ActiveX Toggle Workbook

S

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
 
G

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
 
S

Scafidel

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

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
 
G

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
 
S

Scafidel

Thanks for the quick reply.
Scafidel

Gord Dibben said:
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
 
S

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
 
G

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
 
S

Scafidel

Thanks, again. Works well.

Gord Dibben said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top