Select Case - 2 causes problems
 

Hi,

I'm having a problem with a Select Case statement. I have an annual report
with 12 macros to show or hide the relevant data for each month. I'm trying
to use another macro with a message box to run these macros by typing the
number, but if I type 2 it exits without running the macro.

Is the number 2 somehow equal to cancel?

Or can anyone find problems in this code:

Sub hide_month_select()

Dim month, message, title
message = "Which month would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"
title = "Month selector"
month = InputBox(message, title)
If month = vbCancel Then Exit Sub

Select Case month
Case 1
hide_month1
Case 2
hide_month2
Case 3
hide_month3
Case 4
hide_month4
Case 5
hide_month5
Case 6
hide_month6
Case 7
hide_month7
Case 8
hide_month8
Case 9
hide_month9
Case 10
hide_month10
Case 11
hide_month11
Case 12
hide_month12
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select

End Sub

I know the problem must be with this code, because I've tried substituting
other cases to run "hide_month2" and it works fine.

I also tried case is = 2 but it made no difference.
Thanks for your help.

Select Case - 2 causes problems
 

vbcancel returns 2

try this code., i changed to debug print because i don't have your macros.

also, create a breakpoint on your original "If month = vbcancel Then Exit Sub.
then, step through the code with F8. after you enter a 2 and click ok, put your
mouse cursor over vbcancel and you should see a 2.


try this code., i changed to debug print because i don't have your macros.

Sub hide_month_select()

Dim month, message, title
message = "Which month would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"
title = "Month selector"
month = InputBox(message, title)

If month = "" Then Exit Sub

Select Case month
Case 1
Debug.Print "hide_month1"
Case 2
Debug.Print " hide_month2"
Case 3
Debug.Print " hide_month3"
Case 4
Debug.Print " hide_month4"
Case 5
Debug.Print " hide_month5"
Case 6
Debug.Print " hide_month6"
Case 7
Debug.Print " hide_month7"
Case 8
Debug.Print " hide_month8"
Case 9
Debug.Print " hide_month9"
Case 10
Debug.Print " hide_month10"
Case 11
Debug.Print "hide_month11"
Case 12
Debug.Print " hide_month12"
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select
End Sub

--


Gary


"malcomio" wrote in message
...
Hi,

I'm having a problem with a Select Case statement. I have an annual report
with 12 macros to show or hide the relevant data for each month. I'm trying
to use another macro with a message box to run these macros by typing the
number, but if I type 2 it exits without running the macro.

Is the number 2 somehow equal to cancel?

Or can anyone find problems in this code:

Sub hide_month_select()

Dim month, message, title
message = "Which month would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"
title = "Month selector"
month = InputBox(message, title)
If month = vbCancel Then Exit Sub

Select Case month
Case 1
hide_month1
Case 2
hide_month2
Case 3
hide_month3
Case 4
hide_month4
Case 5
hide_month5
Case 6
hide_month6
Case 7
hide_month7
Case 8
hide_month8
Case 9
hide_month9
Case 10
hide_month10
Case 11
hide_month11
Case 12
hide_month12
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select

End Sub

I know the problem must be with this code, because I've tried substituting
other cases to run "hide_month2" and it works fine.

I also tried case is = 2 but it made no difference.
Thanks for your help.

Select Case - 2 causes problems
 

Man, you got hit by a coincidence.

vbCancel is a constant for the number 2.

I'd use:
If trim(month) = "" Then Exit Sub

In fact, I might use:

Option Explicit
Sub hide_myMonth_select()

Dim myMonth As Variant
Dim message As String
Dim title As String

message = "Which myMonth would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"

title = "myMonth selector"
myMonth = InputBox(message, title)
If Trim(myMonth) = "" Then Exit Sub

If IsNumeric(myMonth) Then
myMonth = CLng(myMonth)
End If

Select Case myMonth
Case 1 To 12
Application.Run "hide_Month" & Trim(myMonth)
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select

End Sub

If the Hide_MonthX subroutines were pretty close, maybe just pass the month to a
single procedure.

Option Explicit
Sub hide_myMonth_select()

Dim myMonth As Variant
Dim message As String
Dim title As String

message = "Which myMonth would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"

title = "myMonth selector"
myMonth = InputBox(message, title)
If Trim(myMonth) = "" Then Exit Sub

If IsNumeric(myMonth) Then
myMonth = CLng(myMonth)
End If

Select Case myMonth
Case 1 To 12
Call hide_Month(CLng(myMonth))
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select

End Sub
Sub hide_Month(myMonth As Long)
'do all the real work here.
MsgBox myMonth
End Sub

malcomio wrote:

Hi,

I'm having a problem with a Select Case statement. I have an annual report
with 12 macros to show or hide the relevant data for each month. I'm trying
to use another macro with a message box to run these macros by typing the
number, but if I type 2 it exits without running the macro.

Is the number 2 somehow equal to cancel?

Or can anyone find problems in this code:

Sub hide_month_select()

Dim month, message, title
message = "Which month would you like to show or hide?" & Chr(13) & _
"1 - January / April" & Chr(13) & "2 - February / May" & Chr(13) & _
" etc" & Chr(13) & "(for calendar year / financial year spreadsheets)"
title = "Month selector"
month = InputBox(message, title)
If month = vbCancel Then Exit Sub

Select Case month
Case 1
hide_month1
Case 2
hide_month2
Case 3
hide_month3
Case 4
hide_month4
Case 5
hide_month5
Case 6
hide_month6
Case 7
hide_month7
Case 8
hide_month8
Case 9
hide_month9
Case 10
hide_month10
Case 11
hide_month11
Case 12
hide_month12
Case Else
MsgBox "Please enter a number between 1 and 12"
End Select

End Sub

I know the problem must be with this code, because I've tried substituting
other cases to run "hide_month2" and it works fine.

I also tried case is = 2 but it made no difference.
Thanks for your help.

--

Dave Peterson

Select Case - 2 causes problems
 

Thanks to both of you for your help.

I ended up working around it with a user form with 12 buttons that probably
is more appropriate for the users' needs, but thanks anyway.