Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need Error Message Box In VBA Code - Excel 2000 & 2003
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default 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




Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2000 error message Bill New Users to Excel 3 January 4th 07 06:41 PM
Excel 2000 error message Bill New Users to Excel 0 January 4th 07 02:50 PM
Excel 2003 causes error with Excel 2000 VBA code darthrader Excel Programming 6 October 31st 06 04:11 PM
Excel 2003 error message! jfcby[_2_] Excel Programming 2 September 1st 06 05:08 PM
error message when opening excel 2000 Lem Excel Discussion (Misc queries) 3 August 31st 06 11:06 PM


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement