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:

  1. Put some data in rows 4 & 5 on the Excel worksheet.
  2. Select row 5. (click the number 5 on the left side of the screen)
  3. Right click somewhere in the selected range and choose "Hide"
  4. If the Control Toolbox toolbar is not already showing, go to View|Toolbars|Control Toolbox to display it.
  5. Click Toggle Button. (It looks like two square buttons on top of each other.)
  6. 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.)
  7. Copy above code.
  8. In Excel press Alt + F11 to enter the VBE.
  9. Press Ctrl + R to show the Project Explorer.
  10. Navigate through the explorer to locate the Worksheet that you placed the toggle button on.
  11. Double click the worksheet.
  12. Paste code into the right pane.
  13. Press Alt + Q to close the VBE.
  14. Save workbook before any other changes.
 

Test the code:

  1. Press the toggle button.
  2. Using the code above, row 4 should be hidden, and row 5 visible.
  3. Press the toggle button again.
  4. 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 295 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express