ListBox with hidden and visible sheet names?

[Rockee052]

Hi,

Me again. I have created a userform with a listbox. I can view the
sheets with the following code:

Private Sub UserForm_Initialize()
Dim sh As Variant
For Each sh In ActiveWorkbook.Sheets
If sh.Visible = True Then
Me.ListBox1.AddItem sh.Name
End If
Next sh
With ListBox1
..Value = ActiveSheet.Name
End With
End Sub

As you can tell I can only veiw the visible sheets. What would I need
to change to view all sheets? This is my goal, create 2 cmdbuttons,
View & Print. Each one does exactly what it says view: views the sheet
and if the sheet is hidden it will unhide the sheet, print: prints the
selected sheet only if it is visible. Also, would there be a way to
exclude some of my worksheets from showing up in the listbox. I have 28
sheets and would only like the user to be able to select 23 of the
sheets. 5 worksheets are for coding only. If anyone has some ideas or
suggestions, could you please show me the light... Right now VBA and
I are in a fight and VBA is winning.

Rockee
Excel 2003

 

[Bob Phillips]

Rockee,

To see them all, just take out the test for sh = Visible.

To exclude some, just test for them, like this

For Each sh In Worksheets
Select Case sh.Name
Case "Sheet1", "Sheet2"
Case Else: MsgBox sh.Name
End Select
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rockee052]

Bob,

Thanks for your time and help. You reply helps out a lot.

Rockee

 

[Rockee052]

Hi,

Okay, I have one more question. Would it be possible to use th
listbox_Click() and have each sheet that is hidden temporary unhide
then hide? So, in other words, when clicking in the listbox, sheet
that are set to hidden would unhide for viewing. I'm trying to clean u
some clutter in a workbook by hidding all but one sheet. Some occasion
I might need to view the hidden sheets.

Thanks,

Rocke

 

[Bob Phillips]

Why not have a commandbutton to unhide them all. You could store each that
you unhide in an array, and have another commandbutton to re-hide them.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rockee052]

Bob,

That sound good but I have not worked with an array before, I don'
have a clue on how to set it up.

Rocke

 

[Bob Phillips]

Rockee,

Here is some code for the two commandbuttons. I decided to use a collection
not an array in the end.

Dim collSheets As Collection

Sub Unhide_Click()
Dim sh As Worksheet
Dim i As Long
Dim c

If collSheets Is Nothing Then
Set collSheets = New Collection
Else
For i = 1 To collSheets.Count
collSheets.Remove 1
Next
End If
For Each sh In ActiveWorkbook.Sheets
If sh.Visible <> xlSheetVisible Then
collSheets.Add sh.Index, sh.Name
sh.Visible = True
End If
Next sh

End Sub

Sub Hide_Click()
Dim sh As Worksheet
Dim i As Long

If collSheets Is Nothing Then
MsgBox "Error"
Else
For i = 1 To collSheets.Count
Worksheets(collSheets(i)).Visible = False
Next i
End If
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rockee052]

Bob,

That worked GREAT!!!! Thank you

Rockee

 

[Bob Phillips]

Rockee,

The one thing I didn't do was to cater for very hidden sheets. If you need
that, you will need to amend slightly.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)