MsgBox vbOKCancel leaves 1 or 2 in cell

Question

I am using an inputbox with type 8 (cell reference / range). Part of the validation of the selected range is with the MsgBox below.

The problem is that when you select a wrong range and the MsgBox prompts you, if you click Ok it leaves the number 2 in the cell, and if you click Cancel it leaves the number 1 in the cell.

ElseIf myRange.Value = "" Or myRange.Value = 0 Then   

  '... 

   myRange = MsgBox("Please Select a Valid Code. Try again?", vbOKCancel + vbQuestion)
   If myRange = vbCancel Then 'No retry
       Exit Sub
   Else 'retry
       Run "MyCellToCorrect"
   End If

Else

Ken White Ken White · Accepted Answer · 2013-08-13T23:53:15

You're assigning the return value of MsgBox, which will be either vbOK (1) or vbCancel (2), to the range. You should be assigning it to a numeric variable instead, and then testing that variable:

Dim Res As Integer
....

Res = MsgBox("Please Select a Valid Code. Try again?", vbOKCancel + vbQuestion)
If Res = vbCancel Then 'No retry
    Exit Sub
Else 'retry
    Run "MyCellToCorrect"
End If

MsgBox vbOKCancel leaves 1 or 2 in cell

1 Answers