Toggle button for hiding/unhiding rows

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39
I would appreciate help for implementing a button-activated macro for hiding/unhiding a range of rows at different locations on down the sheet. I would like to have a separate button for each location. Each button (labelled "More Rows") passes the range of target rows (or the button number, from which the range can be computed) to a shared Sub. The Sub would test the status of the toggle and perform the action of "unhide" for "button down" and "hide" for "button up" for the requested rows. The following is nonworking code I have entered so far:

Private Sub ToggleButton1_Click()
Rows("24:28").Select 'Need to make row range an input argument
If Status.ToggleButton1 = True Then 'Invalid statement
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True
End If
End Sub

:rolleyes: Thanks -- Bernie
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Have you thought about possibily using the group and outline option in the data drop down menu. I havn't used it, but I believe it lets you create tree views. You could setup the ranges you want hidden and then use the +/- buttons to hide and unhide information.
 
Upvote 0

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
730
Private Sub ToggleButton1_Click()
With Rows("24:28")
Selection.EntireRow.Hidden = Not Selection.EntireRow.Hidden
End With
End Sub
 
Upvote 0

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
730
:oops:
correction:

Code:
Private Sub ToggleButton1_Click()
    With Rows("24:28")
        .Select
       .EntireRow.Hidden = Not .EntireRow.Hidden
    End With
End Sub
 
Upvote 0

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39
:biggrin: Thanks, Bob! An elegant solution! Button and row status even preserved between file close/opens. If I may pick your brain some more, is there any way to share the code for 26 buttons at different locations (cell ranges)?
 
Upvote 0

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
TryingToLearn said:
:oops:
correction:

Code:
Private Sub ToggleButton1_Click()
    With Rows("24:28")
        .Select
       .EntireRow.Hidden = Not .EntireRow.Hidden
    End With
End Sub

Function HideRows(Rows as string)
With Rows(Rows)
.Select
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub

You can put this in your module level and call it using this syntax

HideRows("24:34")
or
HideRows("42:44")

Just add the code to each of your button click sub's
 
Upvote 0

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39
(y) Thanks to you both! I had to make the following change to make the Function work:

Function HideRows(rr as string)
With Rows(rr)
.Select
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub
 
Upvote 0

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39
The above method enabled me to successfully design a form that toggles between a minimum (12) and maximum (24) number of exposed rows at a number of places on down the worksheet. What I would prefer is a scroll bar to control what rows are in view (say 12 at a time) at a number of places on down the worksheet. An impossible task? Thanks.
 
Upvote 0

GeoffreyHale

New Member
Joined
Apr 13, 2009
Messages
1
I am completely new to excel macros and MVB so I'm not sure how to make this work... Excel complains about the maxrow being used in the Rows() operator. "Type mismatch"... Here is my code:

Sub AmortCollapse()
'
' Amort Macro
' Collapses Amortization Schedule on Primary Residence sheet. Macro recorded 4/13/2009 by Geoffrey Hale.
'
months = Range("D8").Value
Range("H18").Value = months
maxrow = months + 23
Range("H19").Value = maxrow
With Rows("24:maxrow")
.Select
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,187,188
Messages
5,962,103
Members
438,585
Latest member
peterLEE0223

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top