|
|
|
|
|
|
Excel
|
Use Toggle Button To Hide And Unhide Rows
|
|
Ease of Use
|
Intermediate
|
Version tested with
|
2000, 2003
|
Submitted by:
|
Ken Puls
|
Description:
|
There may be instances where you want to hide or unhide rows using a toggle button.
|
Discussion:
|
The great feature of a toggle button is that an event can be evaluate to see if it is on/off, so only one button is needed to make it work, unlike using command buttons or option buttons where two individual buttons would usually be used.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
Rows(4).EntireRow.Hidden = True
Rows(5).EntireRow.Hidden = False
Else
Rows(4).EntireRow.Hidden = False
Rows(5).EntireRow.Hidden = True
End If
End Sub
|
How to use:
|
- Put some data in rows 4 & 5 on the Excel worksheet.
- Select row 5. (click the number 5 on the left side of the screen)
- Right click somewhere in the selected range and choose "Hide"
- If the Control Toolbox toolbar is not already showing, go to View|Toolbars|Control Toolbox to display it.
- Click Toggle Button. (It looks like two square buttons on top of each other.)
- Place a Toggle Button on the sheet. (Using your left mouse button, click and drag to size the button, then let go to place it.)
- Copy above code.
- In Excel press Alt + F11 to enter the VBE.
- Press Ctrl + R to show the Project Explorer.
- Navigate through the explorer to locate the Worksheet that you placed the toggle button on.
- Double click the worksheet.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
Test the code:
|
- Press the toggle button.
- Using the code above, row 4 should be hidden, and row 5 visible.
- Press the toggle button again.
- Using the code above, row 4 should now be visible, and row 5 hidden.
|
Sample File:
|
Peek-a-boo.zip 8.42KB
|
Approved by mdmackillop
|
This entry has been viewed 322 times.
|
|