Need Error Message Box In VBA Code - Excel 2000 & 2003

[jfcby]

Hello,

In the vba code below i'm tring to insert a Error Message Box but it
will not allow my drop down menu go to the worksheet.

This is what I've got so far but the Error Message Box is not working:

Sub Select_Cal_Options_Macros_Preview()

Dim myMonthName As String
Dim myVal As Long
Dim myYear As Variant
Dim myYear1 As Variant
Dim myYear2 As Variant


myVal = Sheet4.Range("B1").Value
myYear = "2007"
myZone1 = "NORTH"
myZone2 = "SOUTH"


On Error GoTo ErrHandler1:
' your code here

If myVal > 1 _
And myVal < 14 Then
myMonthName = Format(DateSerial(myYear, (myVal Mod 12) - 1, 1),
"MMMM")
Sheets(myMonthName & " " & myYear & " " & myZone1).Select
Call SetSelect_CalOptions
End If

Exit Sub
ErrHandler1:
MsgBox "Didn't find " & myMonthName & " " & myYear & " " & myZone1
& " " & _
"worksheet select again!"

On Error GoTo ErrHandler2:

If myVal > 13 _
And myVal < 26 Then
myMonthName = Format(DateSerial(myYear, (myVal Mod 12) - 1, 1),
"MMMM")
'MsgBox "Found them.. " & Rng.Address
Sheets(myMonthName & " " & myYear & " " & myZone1).Select
Call SetSelect_CalOptions
'End If
End If

Exit Sub
ErrHandler2:
MsgBox "Didn't find " & myMonthName & " " & myYear & " " & myZone2
& " " & _
"worksheet select again!"

End Sub

Thanks for your help in advance,
jfc14

 

[NickHK]

You should not be starting an error handler (On Error GoTo ErrHandler2)
whilst you are already in an error handler.

Seems like you could change you routine to the 2 test first, with a single
error handler:

If myVal > 1 And myVal < 14 Then
'.... etc
Elseif myVal > 13 And myVal < 26 then
'.... etc
Else
'Do something
end if

Exit Sub
ErrHandler1:
MsgBox "Didn't find " & myMonthName & " " & myYear & " " & myZone1 & "
worksheet select again!"

NickHK

 

[jfcby]

Hello NickHK,

Thank you for your reply but I would like the code to be able to have
two error messages.

I figured out how to get the two error messages!

Below is the working code:

Sub Select_Cal_Options_Macros_Preview()
'_Errors Fixed
Dim myMonthName As String
Dim myVal As Long
Dim myYear As Variant
Dim myYear1 As Variant
Dim myYear2 As Variant

myVal = Sheet4.Range("B1").Value
myYear = "2007"
myZone1 = "NORTH"
myZone2 = "SOUTH"

On Error GoTo ErrHandler:
' your code here

If myVal >= 1 _
And myVal <= 13 Then
myMonthName = Format(DateSerial(myYear, (myVal Mod 12) - 1, 1),
"MMMM")
Sheets(myMonthName & " " & myYear & " " & myZone1).Select
Call SetSelect_CalOptions
End If

If myVal >= 14 _
And myVal <= 25 Then
myMonthName = Format(DateSerial(myYear, (myVal Mod 12) - 1, 1),
"MMMM")
'MsgBox "Found them.. " & Rng.Address
Sheets(myMonthName & " " & myYear & " " & myZone2).Select
Call SetSelect_CalOptions
'End If
End If

Exit Sub
ErrHandler:
If myVal >= 1 _
And myVal <= 13 Then
MsgBox "Didn't find " & myMonthName & " " & myYear & " " &
myZone1 & " " & _
"worksheet select again!"
Else
MsgBox "Didn't find " & myMonthName & " " & myYear & " " &
myZone2 & " " & _
"worksheet select again!"
End If

End Sub

Thank you for your help,
jfcby

 

[NickHK]

OK, you version works, but it easier to set a MsgStr in code, so it can be
expanded more readily, if you need to add more checks:
Dim MsgText as string
.....
If myVal >= 1 And myVal <= 13 Then
MsgText=myMonthName & " " & myYear & " " & myZone1
......

If myVal >= 14 And myVal <= 25 Then
MsgText=myMonthName & " " & myYear & " " & myZone2
......

ErrHandler:
MsgBox "Didn't find " & MsgText & " worksheet select again!"

NickHK

 

[jfcby]

Hello NickHK,

Thank you for the modification it works great!

jfcby