S
Suzanne
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
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