ListBox with hidden and visible sheet names?
 

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


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

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 " wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

Bob,

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

Rockee


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

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

--
Message posted from http://www.ExcelForum.com

ListBox with hidden and visible sheet names?
 

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 " wrote in message
...
Hi,

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

Thanks,

Rockee


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

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

--
Message posted from http://www.ExcelForum.com

ListBox with hidden and visible sheet names?
 

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 " wrote in message
...
Bob,

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

Rockee


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

Bob,

That worked GREAT!!!! Thank you :)

Rockee


---
Message posted from http://www.ExcelForum.com/

ListBox with hidden and visible sheet names?
 

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)

"Rockee052 " wrote in message
...
Bob,

That worked GREAT!!!! Thank you :)

Rockee


---
Message posted from http://www.ExcelForum.com/