Toggle button for hiding/unhiding rows

[BernieRS]

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

Thanks -- Bernie

 

[Cbrine]

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]

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

 

[TryingToLearn]

correction:

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

 

[BernieRS]

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]

correction:

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]

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]

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]

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