jeudi 15 février 2018

Displaying Sheets in Userform - Make sure at least one sheet checkbox is always checked

I have some code to display a list of sheets in a listbox within a userform. They have checkboxes to hide and unhide sheets as needed. However, if the user accidentally tries to hide all the sheets, VBA throws an error. I would like to account for this by not allowing them to unselect the last sheet, and displaying a message. The code below is not working. Any help is appreciated!!

Public Sub ListBox_Sheet_List_Change()

        Application.ScreenUpdating = False
        Application.EnableEvents = False

        Dim i As Integer
        For i = 0 To Me.ListBox_Sheet_List.ListCount - 1
            If Me.ListBox_Sheet_List.Selected(i) Then
                Worksheets(Me.ListBox_Sheet_List.List(i)).Visible = True
            Else
                If Worksheets(Me.ListBox_Sheet_List(i) >= 1) Then
                    Worksheets(Me.ListBox_Sheet_List.List(i)).Visible = False
                Else
                    MsgBox ("ERROR: You must have at least one sheet displayed.")
                    Exit Sub
                End If
            End If
        Next i

        Application.ScreenUpdating = True
        Application.EnableEvents = True

    End Sub




Aucun commentaire:

Enregistrer un commentaire