VBA Express : Excel - Use Toggle Button To Hide And Unhide Rows

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 
         'This area contains the things you want to happen
         'when the toggle button is not depressed
        Rows(4).EntireRow.Hidden = True 
        Rows(5).EntireRow.Hidden = False 
         
    Else 
         'This area contains the things you want to happen
         'when the toggle button is depressed
        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.