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??
:oD", 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. :oD

[kev_06]

Try adding this at the start of the code:

Dim MyMsgBox as String

[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?? :oD", _
vbOKCancel + vbExclamation, "Delete ... ?")

If MyMsgBox = 1 Then

Selection.EntireRow.Delete

End If

ActiveSheet.Protect ' place at end of code
End Sub

--
Regards,
Tom Ogilvy


"StargateFanFromWork" wrote:

> 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??
> :oD", 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. :oD
>
>
>

[StargateFanFromWork]

"kev_06" <[email protected]> wrote in
message news:[email protected]...
>
> Try adding this at the start of the code:
>
> Dim MyMsgBox as String

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

> --
> kev_06
> ------------------------------------------------------------------------
> kev_06's Profile:
http://www.excelforum.com/member.php...o&userid=35046
> View this thread: http://www.excelforum.com/showthread...hreadid=549469
>

[StargateFanFromWork]

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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?? :oD", _
> 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! :oD

> --
> Regards,
> Tom Ogilvy
>
>
> "StargateFanFromWork" wrote:
>
> > 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??
> > :oD", 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. :oD
> >
> >
> >

[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 wrote:
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > 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?? :oD", _
> > 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! :oD
>
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "StargateFanFromWork" wrote:
> >
> > > 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??
> > > :oD", 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. :oD
> > >
> > >
> > >

--

Dave Peterson

[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)?? :oD", 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!

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?? :oD", _
> > > 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! :oD
> >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "StargateFanFromWork" wrote:
> > >
> > > > 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??
> > > > :oD", 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. :oD
> > > >
> > > >
> > > >
>
> --
>
> Dave Peterson