VB error, "Compile error: value not defined"

[StargateFanFromWork]

I've used this macro many times before but in this new workbook, I get the
VB compile error. Here is the code:

************************************************************
Sub DeleteRow()
ActiveSheet.Unprotect 'place at the beginning of the code
MyMsgBox = MsgBox("Are you sure you really want to delete this row??
D", vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = 1 Then

Selection.EntireRow.Delete

End If

ActiveSheet.Protect ' place at end of code
End Sub
************************************************************

In another similar case, it was just a question of adding "Option Explicit"
at the top of the code window. The thing is that this already has that text
there, so I'm stumped. I searched for this error code, but couldn't figure
out the resolution to this particular problem.

Thanks in advance for any help re this. Once this is fixed, I can share the
workbook. D

 

[kev_06]

Try adding this at the start of the code:

Dim MyMsgBox as Strin

 

[Tom Ogilvy]

Sub DeleteRow()
Dim MyMsgBox as Long
ActiveSheet.Unprotect 'place at the beginning of the code
MyMsgBox = MsgBox( _
"Are you sure you really want to delete this row?? D", _
vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = 1 Then

Selection.EntireRow.Delete

End If

ActiveSheet.Protect ' place at end of code
End Sub

 

[StargateFanFromWork]

Try adding this at the start of the code:

Dim MyMsgBox as String

Thank you! That seems to have done the job perfectly. D

 

[StargateFanFromWork]

Sub DeleteRow()
Dim MyMsgBox as Long
ActiveSheet.Unprotect 'place at the beginning of the code
MyMsgBox = MsgBox( _
"Are you sure you really want to delete this row?? D", _
vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = 1 Then

Selection.EntireRow.Delete

End If

ActiveSheet.Protect ' place at end of code
End Sub

Thanks, Tom!

Is there a preference to using Dim MyMsgBox as Long over Dim MyMsgBox as
String? I saw the message with the "String" one first, and used that and it
seems to work just fine. But wondering if one is better or more appropriate
than the other??

Cheers! D

 

[Dave Peterson]

The msgbox function returns numbers, so Long is better.

This was taken from xl2003's help:

Constant Value Description
vbOK 1 OK
vbCancel 2 Cancel
vbAbort 3 Abort
vbRetry 4 Retry
vbIgnore 5 Ignore
vbYes 6 Yes
vbNo 7 No

You can use:
if mymsgbox = 1 then
But I bet your code will be easier to understand if you use:
if mymsgbox = vbOk then

It won't matter to the computer if you use 1 or vbOk, only to a human reading
the code.

 

[StargateFanFromWork]

Thank you! I changed the code and it seems to do the same job but, you're
right, easier for user. I never knew re the "1" vs. "vbOK" so it's much
better to use the latter.
************************************************************
Sub DeleteRow()
ActiveSheet.Unprotect 'place at the beginning of the code
Dim MyMsgBox As Long
MyMsgBox = MsgBox("Are you sure you really want to delete this/these
row(s)?? D", vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = vbOK Then

Selection.EntireRow.Delete

End If

ActiveSheet.Protect ' place at end of code
End Sub

************************************************************
This works now, thanks!