Toggle Button to Hide/Unhide Rows

[Don.Inc]

Hi. My first question is as above. I've looked all around the forum for codes and have found many but me being such an excel noob I don't know how to adapt them to my situation.
What I need is the code for a toggle Button that:
~When the toggle button is in its enabled unhides rows 15-58 and says "Hide"
~When the toggle button is dis-enabled (unpressed) hide rows 15-58 and says "Unhide"
Thank you guys in advanced, as you can tell I haven't got much excel experience.
P.S This is Excel from MS Office '07
Cheers

 

[repairman615]

Hello,

Welcome to the Board!

This uses a toggle button named togglebutton1.

Right click the activeX toggle button while in design mode. Select veiw code. Then paste in:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ToggleButton1_Click()<br><br><SPAN style="color:#00007F">If</SPAN> ToggleButton1 <SPAN style="color:#00007F">Then</SPAN><br>    MsgBox "Hide."<br>    Rows("15:58").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>    MsgBox "Unhide."<br>    Rows("15:58").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

See if that does the task.

 

[mikerickson]

If you assign this macro to a forms command button or any shape, this will do what you want.

Sub toggleRows()
    Dim toggleRows As Range
    Set toggleRows = Sheet1.Range("15:58").EntireRow
    
    With toggleRows
        .Hidden = Not (.Rows(1).Hidden)
    
        If TypeName(Application.Caller) = "String" Then
            ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = IIf(.Hidden, "Show", "Hide")
        End If
    
    End With
End Sub

 

[Don.Inc]

Hello,

Welcome to the Board!

This uses a toggle button named togglebutton1.

Right click the activeX toggle button while in design mode. Select veiw code. Then paste in:

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ToggleButton1_Click()<br><br><SPAN style="color:#00007F">If</SPAN> ToggleButton1 <SPAN style="color:#00007F">Then</SPAN><br>****MsgBox "Hide."<br>****Rows("15:58").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>****MsgBox "Unhide."<br>****Rows("15:58").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

See if that does the task.

Cheers worked a charm.

 

[bamaisgreat]

For the Forms Command Button that Mike posted the code to, how could that be modified for columns?? Example: I only need to hide and unhide column C

Sub toggleRows()
Dim toggleRows As Range
Set toggleRows = Sheet1.Range("15:58").EntireRow

With toggleRows
.Hidden = Not (.Rows(1).Hidden)

If TypeName(Application.Caller) = "String" Then
ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = IIf(.Hidden, "Show", "Hide")
End If

End With
End Sub