Populating UserForm with checkboxes

[Schadenfreud]

Hello, I've just started using the VBA and am trying to create an Excel UserForm, which lists all visible Sheets in the current file (doesn't list hidden ones) andcreates a checkbox in front of each Sheet name, which will hide/show the Sheet, depending on it being true/false. The Form must be scrollable and there must be a validation, checking that there's no less than one visible Sheet at any given time.

So far I'm stuck at the populating the form with checkboxes part. I need them to appear one below the other with the sheet name beside them.

My code so far looks like this (and doesn't work apparently):

Private Sub UserForm_Initialize()
Private WithEvents Chk As MSForms.CheckBox
Dim myWorksheet As Worksheet


    For Each myWorksheet In Worksheets
        If myWorksheet.Visible = xlSheetVisible Then
        'add the checkboxes for each visible worksheet to the form
        Set Chk = frmShowTabs.Controls.Add("Forms.CheckBox.1", "myWorksheet.Name")
        End If
    Next
End Sub

P.s. I'm sure I have to somehow add coordinates for where the checkboxes should appear too, but I don't know how to do that either.

 

[dmt32]

Hi,</SPAN>
As a suggestion, you might find it easier to achieve what you want by using a listbox with its liststyle set to option.</SPAN>

Place a listbox on your form and add following code - see if this approach is of any help to you:</SPAN>

Dim a As Boolean ' this variable must sit at top of code page
Private Sub UserForm_Initialize()
    Dim myWorksheet As Worksheet
    With ListBox1
        .MultiSelect = fmMultiSelectMulti
        .ListStyle = fmListStyleOption
        .AddItem "Select All"
        For Each myWorksheet In Worksheets
            If myWorksheet.Visible = xlSheetVisible Then
                'add each visible worksheet to the listbox
                .AddItem myWorksheet.Name
            End If
        Next myWorksheet
    End With
End Sub

Private Sub ListBox1_Change()
    If a = True Then Exit Sub
    With Me.ListBox1
        If .ListIndex = 0 Then
            'stop recursion
            a = True
            For i = 1 To .ListCount - 1
                .Selected(i) = .Selected(0)
            Next i
            a = False
        Else
            .Selected(0) = False
        End If
    End With
End Sub

Dave

 

[Schadenfreud]

Hi, Dave! Thanks for the quick replay. I tried your code but when I attempt to Run the code it gives me

Run-time Error '424': Object Required

and I'm not certain how to debug it.

 

[Schadenfreud]

Seems the error occurs here

With ListBox1
        .MultiSelect = fmMultiSelectMulti

 

[Schadenfreud]

I got it, finally... Thanks, Dave, the code worked I just didn't realize that I had to first add the ListBox in design view >.< I thought it would appear magically (automatically) from somewhere...

Can you give me a bit of a clarification on how the ListBox1_Change method works?