ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Need Error Message Box In VBA Code - Excel 2000 & 2003 (https://www.excelbanter.com/excel-programming/378505-need-error-message-box-vba-code-excel-2000-2003-a.html)

jfcby[_2_]
Need Error Message Box In VBA Code - Excel 2000 & 2003
 
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
Need Error Message Box In VBA Code - Excel 2000 & 2003
 
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" wrote in message
ups.com...
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

jfcby[_2_]
Need Error Message Box In VBA Code - Excel 2000 & 2003
 
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 wrote:
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" wrote in message
ups.com...
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
Need Error Message Box In VBA Code - Excel 2000 & 2003
 
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
ups.com...
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

jfcby[_2_]
Need Error Message Box In VBA Code - Excel 2000 & 2003
 
Hello NickHK,

Thank you for the modification it works great!

jfcby

NickHK wrote:
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" wrote in message
ups.com...
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 wrote:
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" wrote in message
ups.com...
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


All times are GMT +1. The time now is 12:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com