I have an Excel 2007 macro-enabled workbook with 50 or so worksheets -- 12 of
them are worksheets to collect monthly data totals ("JAN" "FEB" "MAR", etc.).

On each of these 12 worksheets, I would like to add 4 form or Active X
controls (command button, checkbox, or whatever) to allow users to
(1) hide all the month worksheets except the current month;
(2) hide the current month (worksheet) and unhide the previous month;
(3) hide the current month (worksheet) and unhide the next month;
(4) unhide all the months.

I'm thinking a variation of this solution (by Tom Ogilvy in 4 Nov 07,
subject: "Macro-hide sub sheets?") might be a start. Any help on this, or
another formula, is greatly appreciated!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim b1 As Long, b2 As Long
Dim sht As Worksheet, sChr As String
Select Case LCase(Sh.Name)
Case "original"
b1 = xlSheetVisible
b2 = xlSheetHidden
Case "future"
b1 = xlSheetHidden
b2 = xlSheetVisible
Case Else
Exit Sub
End Select
For Each sht In Worksheets
sChr = UCase(sht.Name)
If Len(sChr) = 1 Then
If sChr = "C" And sChr <= "H" Then
sht.Visible = b1
ElseIf sChr = "I" And sChr <= "N" Then
sht.Visible = b2
End If
End If
Next
End Sub

Hi Suzanne,

I think it would be easier write a macro to hide or unhide all 12 month
sheets, copy it to all 12 worksheets for all four button scenarios, then
place a ' in front of the month(s) you want to leave out of that macro. It's
a little bit of work but really not that much to get what you need.

Squeaky

"Suzanne" wrote:

I have an Excel 2007 macro-enabled workbook with 50 or so worksheets -- 12 of
them are worksheets to collect monthly data totals ("JAN" "FEB" "MAR", etc.).

On each of these 12 worksheets, I would like to add 4 form or Active X
controls (command button, checkbox, or whatever) to allow users to
(1) hide all the month worksheets except the current month;
(2) hide the current month (worksheet) and unhide the previous month;
(3) hide the current month (worksheet) and unhide the next month;
(4) unhide all the months.

I'm thinking a variation of this solution (by Tom Ogilvy in 4 Nov 07,
subject: "Macro-hide sub sheets?") might be a start. Any help on this, or
another formula, is greatly appreciated!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim b1 As Long, b2 As Long
Dim sht As Worksheet, sChr As String
Select Case LCase(Sh.Name)
Case "original"
b1 = xlSheetVisible
b2 = xlSheetHidden
Case "future"
b1 = xlSheetHidden
b2 = xlSheetVisible
Case Else
Exit Sub
End Select
For Each sht In Worksheets
sChr = UCase(sht.Name)
If Len(sChr) = 1 Then
If sChr = "C" And sChr <= "H" Then
sht.Visible = b1
ElseIf sChr = "I" And sChr <= "N" Then
sht.Visible = b2
End If
End If
Next
End Sub

H again,

Or better still, write it up to work on one sheet, then copy the sheet 11
times. Then you just need to change the month.

Squeaky

"Suzanne" wrote:

I have an Excel 2007 macro-enabled workbook with 50 or so worksheets -- 12 of
them are worksheets to collect monthly data totals ("JAN" "FEB" "MAR", etc.).

On each of these 12 worksheets, I would like to add 4 form or Active X
controls (command button, checkbox, or whatever) to allow users to
(1) hide all the month worksheets except the current month;
(2) hide the current month (worksheet) and unhide the previous month;
(3) hide the current month (worksheet) and unhide the next month;
(4) unhide all the months.

I'm thinking a variation of this solution (by Tom Ogilvy in 4 Nov 07,
subject: "Macro-hide sub sheets?") might be a start. Any help on this, or
another formula, is greatly appreciated!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim b1 As Long, b2 As Long
Dim sht As Worksheet, sChr As String
Select Case LCase(Sh.Name)
Case "original"
b1 = xlSheetVisible
b2 = xlSheetHidden
Case "future"
b1 = xlSheetHidden
b2 = xlSheetVisible
Case Else
Exit Sub
End Select
For Each sht In Worksheets
sChr = UCase(sht.Name)
If Len(sChr) = 1 Then
If sChr = "C" And sChr <= "H" Then
sht.Visible = b1
ElseIf sChr = "I" And sChr <= "N" Then
sht.Visible = b2
End If
End If
Next
End Sub

That's pretty much what I did: I created a user form with option controls to
select a month that programatically hides the rest of the months.

Thanks

"Squeaky" wrote:

H again,

Or better still, write it up to work on one sheet, then copy the sheet 11
times. Then you just need to change the month.

Squeaky

"Suzanne" wrote:

I have an Excel 2007 macro-enabled workbook with 50 or so worksheets -- 12 of
them are worksheets to collect monthly data totals ("JAN" "FEB" "MAR", etc.).

On each of these 12 worksheets, I would like to add 4 form or Active X
controls (command button, checkbox, or whatever) to allow users to
(1) hide all the month worksheets except the current month;
(2) hide the current month (worksheet) and unhide the previous month;
(3) hide the current month (worksheet) and unhide the next month;
(4) unhide all the months.

I'm thinking a variation of this solution (by Tom Ogilvy in 4 Nov 07,
subject: "Macro-hide sub sheets?") might be a start. Any help on this, or
another formula, is greatly appreciated!

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim b1 As Long, b2 As Long
Dim sht As Worksheet, sChr As String
Select Case LCase(Sh.Name)
Case "original"
b1 = xlSheetVisible
b2 = xlSheetHidden
Case "future"
b1 = xlSheetHidden
b2 = xlSheetVisible
Case Else
Exit Sub
End Select
For Each sht In Worksheets
sChr = UCase(sht.Name)
If Len(sChr) = 1 Then
If sChr = "C" And sChr <= "H" Then
sht.Visible = b1
ElseIf sChr = "I" And sChr <= "N" Then
sht.Visible = b2
End If
End If
Next
End Sub