Array of Checkboxes

[magnamundian]

Hi

I'm looking to put together a VBA sub/module which, using the following variables:

Dim iOptions as Integer
iOptions = {spreadsheet refererence}
ReDim sNames(1 to iOptions) as string array
ReDim bSelected(0 to iOptions) as boolean array

Would achieve the following:

Populate a UserForm with {iOptions + 1} Checkboxes and an OK button.
Label the first CheckBox as "Select All" and all subsequent CheckBoxes according to the labels found in the {sNames} array.

When user ticks whatever number of checkboxes they want, the variable {bSelected} would be populated with either True or False based on whether the Checkbox was ticked (True) or not (False) with bSelected(0) being the "Select All" checkbox and all subsequent checkboxes being number 1, 2, 3... etc

This would need to be dynamic, i.e. the number of boxes required would go up or down.

Any thoughts?

 

[Norie]

Have you considerd a listbox?

They can show a checkbox for each item and might be a little easier to work with than multple dynamic checkboxes.

 

[magnamundian]

Sounds good, but how does that work?

 

[Norie]

Well you would just populate the listbox with the options and set a couple of properties to show the list with a checkbox next to each item.

How to populate really depends on where the data for the options is located.

For example if it's on a worksheet you could simply set the List property of the listbox

Listbox1.List = Range("A1:A10").Value

Though in your case, with the Select All option, you might want to use AddItem.

Set rng =Range("A1:A10")
 
Listbox1.Additem "Select All"
 
For Each cl In rng
      Listbox1.AddItem = cl.Value
Next cl

When it comes to checking what's been checked you can just loop through the items in the listbox and check there Selected property.

Similarly if Select All is checked then you can loop through and set the Selected property.

Oh, forgot to ask - where would you want to do this?

On a worksheet? A userform?

 

[magnamundian]

Userform, and I think that helps me a lot, sometimes with a new thing it's just finding the basic starting point of how to do something in vba.

 

[Rasm]

If you want to use a Listview then you can have checkBoxes on the list - it is more coding but very flexible - The code below give you an idea of how this works - Just insert a ListView on your userform

Private Sub UserForm_Activate()
    Dim list_item As ListItem
    With ListView1
        .MultiSelect = True
        .CheckBoxes = True
        .BackColor = &H80FFFF
        .ListItems.Clear
        .ColumnHeaders.Add , , "File Names", 120
        .ColumnHeaders.Add , , "What/ever", 85
        Set list_item = .ListItems.Add(1, , "File_1")
        list_item.SubItems(1) = "Entry one"
        Set list_item = .ListItems.Add(1, , "File_2")
        list_item.SubItems(1) = "Entry Two"
 
        .View = 3 ' you can mess with this - changes layout
 
    End With
End Sub

 

[Rasm]

BTW - Norie - I luv my ListViews - thanks for pointing me to ListViews - lol