ActiveX Toggle Workbook

[Scafidel]

OK. I am using this command to hide/unhide several rows of a sheet that have
nonessential information. As my workbook has many sheets, I would like to be
able to Hide/Unhide rows on ALL the sheets. I know I can copy the command for
each sheet, but there must be an easier way. If possible, by having a "cover
sheet" with a toggle to hide/unhide rows for the entire workbook. If not, is
there a way to include all sheets in the command?

Private Sub ToggleButton1_Click()
With Worksheets("34-01").Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End Sub

Thanks
Scafidel

 

[Gord Dibben]

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Scafidel]

Thanks again, Gord. How can I omit a sheet (from this command) that I want
to display completely?
Scafidel

 

[Duke Carey]

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
if ws.name <> "name of worksheet to skip"
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
end if
Next ws
End Sub

 

[Gord Dibben]

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "displayall" Then
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End If
Next ws
End Sub

"displayall" will be edited to your sheet name.

Gord

 

[Scafidel]

Thanks for the quick reply.
Scafidel

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "displayall" Then
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End If
Next ws
End Sub

"displayall" will be edited to your sheet name.

Gord

 

[Scafidel]

This probably should be a new thread, but I thought I'd go to the well once
more. I am using an ActiveX Togglebutton to hide/unhide rows of nonessential
information on various sheets in a workbook. The button is on a cover sheet
to the workbook. However, the program doesn't "see" all of the pages I would
like to omit from hiding. Only the first Totals* is ignored, not the others.
??
thanks
Scafidel

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Totals-*" Then
If ws.Name <> "Min*" Then
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End If
End If
Next ws
End Sub

 

[Gord Dibben]

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "Totals-*" And _
Not ws.Name Like "Min*" Then
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End If
Next ws
End Sub


Gord

 

[Scafidel]

Thanks, again. Works well.

Private Sub ToggleButton1_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If Not ws.Name Like "Totals-*" And _
Not ws.Name Like "Min*" Then
With ws.Rows("45:200")
If .Hidden = True Then
.Hidden = False
Else
.Hidden = True
End If
End With
End If
Next ws
End Sub


Gord

 

[Gord Dibben]

We have to quit meeting like this<g>

Gord

Thanks, again. Works well.