rorobear
08-17-2021, 10:17 PM
Hello Everyone,
I’m currently working on a worksheet manager. Although it’s coming along nicely, I ran into a little problem that I’m hoping to get some help with, so thank you everyone in advance. In the userform, I have 2 listbox (ListBox1 and lbxHeaders). In here various things happen, move sheets, delete sheets, rename sheets, hide sheets etc. The problem is when I hide a sheet, even though the status changes to hidden, it still shows in the listbox. Furthermore, if I click on it causes the form to locks up. If anyone can help me, here’s what I’d like:
When I hide a sheet in one of two ways, either with the “Hide Active Sheet” button or the “Hide Selected Sheet” button I want that selection cleared from the listbox and when I unhide it comes back.
On the second listbox (lbxHeaders) whatever corresponds to that selection in (ListBox1) should be cleared as well and return when I unhide.
Below is my code. Hopefully, I did a good job explaining, but I probably didn’t so I’m including the workbook as well. Again, any assistance is greatly appreciated.
Private Sub HideSheet_Click()
'Hide the active worksheet
Application.ScreenUpdating = False
ActiveSheet.Visible = xlSheetHidden
reset
UserForm_Initialize
Application.ScreenUpdating = True
End Sub
Private Sub HideSelectedSheet_Click()
'hide the selected sheet
Dim i As Integer
Dim mySheet As String
Application.DisplayAlerts = False
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
On Error Resume Next
Sheets(Mid(ListBox1.List(i), InStr(ListBox1.List(i), vbTab) + 1)).Visible = xlSheetHidden
' Check to not to delete all sheets as this will cause an error
If Err.Number = 1004 Then
MsgBox "Can't hide all the worksheets", vbOKOnly + vbCritical
End If
'Enable default error handling again
On Error GoTo 0
End If
Next i
Application.DisplayAlerts = True
Me.ListBox1.Clear
For i = 1 To ActiveWorkbook.Sheets.Count
Me.ListBox1.AddItem ActiveWorkbook.Sheets(i).Name
Next i
reset
UserForm_Initialize
End Sub
I’m currently working on a worksheet manager. Although it’s coming along nicely, I ran into a little problem that I’m hoping to get some help with, so thank you everyone in advance. In the userform, I have 2 listbox (ListBox1 and lbxHeaders). In here various things happen, move sheets, delete sheets, rename sheets, hide sheets etc. The problem is when I hide a sheet, even though the status changes to hidden, it still shows in the listbox. Furthermore, if I click on it causes the form to locks up. If anyone can help me, here’s what I’d like:
When I hide a sheet in one of two ways, either with the “Hide Active Sheet” button or the “Hide Selected Sheet” button I want that selection cleared from the listbox and when I unhide it comes back.
On the second listbox (lbxHeaders) whatever corresponds to that selection in (ListBox1) should be cleared as well and return when I unhide.
Below is my code. Hopefully, I did a good job explaining, but I probably didn’t so I’m including the workbook as well. Again, any assistance is greatly appreciated.
Private Sub HideSheet_Click()
'Hide the active worksheet
Application.ScreenUpdating = False
ActiveSheet.Visible = xlSheetHidden
reset
UserForm_Initialize
Application.ScreenUpdating = True
End Sub
Private Sub HideSelectedSheet_Click()
'hide the selected sheet
Dim i As Integer
Dim mySheet As String
Application.DisplayAlerts = False
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
On Error Resume Next
Sheets(Mid(ListBox1.List(i), InStr(ListBox1.List(i), vbTab) + 1)).Visible = xlSheetHidden
' Check to not to delete all sheets as this will cause an error
If Err.Number = 1004 Then
MsgBox "Can't hide all the worksheets", vbOKOnly + vbCritical
End If
'Enable default error handling again
On Error GoTo 0
End If
Next i
Application.DisplayAlerts = True
Me.ListBox1.Clear
For i = 1 To ActiveWorkbook.Sheets.Count
Me.ListBox1.AddItem ActiveWorkbook.Sheets(i).Name
Next i
reset
UserForm_Initialize
End Sub