ListBox Procedures

[Rockee052]

Hello,

Today Bob has already helped me with this code (Bob if you are readin
this THANK YOU). I am having a little problem, if the user unhides th
worksheets then closes the user form. Then the user reopens th
userform and tries to hide the worksheets it does not work. Can anyon
see what I am missing...

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

Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub

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

Application.ScreenUpdating = False
If collSheets Is Nothing Then
MsgBox "Error" 'I think something belongs here?
Else
For i = 1 To collSheets.Count
Worksheets(collSheets(i)).Visible = False
Next i
End If
Application.ScreenUpdating = True
End Sub

Thanks again,

Rockee
Excel 200

 

[Harald Staff]

Hi

You problem is perhaps that the collection is unloaded and reloaded with
different content, while your list is not. If so you must address the
collection by key (which is the sheet name) and not the index number. But
you may still have errors if the list has things that's removed from the
collection.

There are many approaches to this. But I assume that your Listbox1 contains
all "legal" sheet names and that it's multiselect. I'd pick the sheet names
directly from the list instead of indexing a collection:

Private Sub UserForm_Initialize()
Dim wks As Worksheet
For Each wks In Worksheets
'Criteria for displaying sheets here, and if met:
List1.AddItem wks.Name
Next
End Sub

Private Sub CmdHide_Click()
Dim L As Long
On Error Resume Next
For L = 0 To List1.ListCount - 1
If List1.Selected(L) Then _
Sheets(List1.List(L)).Visible = False
Next
End Sub

Private Sub cmdUnhide_Click()
Dim L As Long
For L = 0 To List1.ListCount - 1
If List1.Selected(L) Then _
Sheets(List1.List(L)).Visible = True
Next
End Sub