Microsoft Office Tutorials and References
In Depth Information
Try It
11.
In your new module, type Sub ToggleViews and press the Enter key. VBA will produce the
following two lines of code, with an empty row between them:
Sub ToggleViews()
End Sub
12.
Because the macro will hide and unhide rows and columns, turn off ScreenUpdating to keep
the screen from flickering:
Application.ScreenUpdating = False
13.
Open a With structure that uses Application.Caller to identify the Forms button that was
clicked:
With ActiveSheet.Buttons(Application.Caller)
14.
Toggle between views based on the button’s captions to determine which view is next in the
cycle:
If .Caption = “SHOW ALL” Then
With Range(“A5:G18”)
.EntireColumn.Hidden = False
.EntireRow.Hidden = False
End With
.Caption = “MONTH TOTALS”
ElseIf .Caption = “MONTH TOTALS” Then
Range(“B:F”).EntireColumn.Hidden = True
.Caption = “ITEM TOTALS”
ElseIf .Caption = “ITEM TOTALS” Then
Range(“B:F”).EntireColumn.Hidden = False
Rows(“6:17”).Hidden = True
.Caption = “SHOW ALL”
End If ‘for evaluating the button caption.
15.
Close the With structure for Application.Caller :
End With
16.
Turn ScreenUpdating on again:
Application.ScreenUpdating = True
17.
Your entire macro will look like this:
Sub ToggleViews()
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False
‘Open a With structure that uses Application.Caller
‘to identify the Forms button that was clicked.
With ActiveSheet.Buttons(Application.Caller)
Search JabSto ::




Custom Search