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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

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.
 

TryingToLearn

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

TryingToLearn

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

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

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39

ADVERTISEMENT

: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)?
 

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
 

BernieRS

New Member
Joined
Jan 30, 2004
Messages
39

ADVERTISEMENT

(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
 

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.
 

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,452
Members
425,548
Latest member
macjagger17

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