Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Procedures
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Procedures
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 -- HTH. Best wishes Harald Followup to newsgroup only please "Rockee052 " skrev i melding ... Hello, Today Bob has already helped me with this code (Bob if you are reading this THANK YOU). I am having a little problem, if the user unhides the worksheets then closes the user form. Then the user reopens the userform and tries to hide the worksheets it does not work. Can anyone 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 2003 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Sequential Sub Procedures | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming | |||
what's wrong with this sub procedures? | Excel Programming |