Multiple selection based on checkboxes

Question

I have a UserForm where I have 6 check boxes. The role of check box is to select a unique range in Excel. Each checkbox corresponds to a different range in the Excel sheet.

I want to know what methodology can you use to ensure that when a user select a combination of checkboxes max of 6, Excel selects the corresponding ranges of the selected checkbox.

For example:

Checkbox1 programmed to select range A1
Checkbox2 programmed to select range H3
Checkbox3 programmed to select range F6

If User ticks Checkbox1 and Checkbox2 then how can you tell Excel to select A1 and H3 without using If statements since the combination of 6 check boxes would mean a lot of If statements.

Is there anyway when Checkbox1 is selected it keeps that selection in memory and adds it to the next selection.

Thanks

Tim Williams Tim Williams · Accepted Answer · 2021-03-24T22:32:40

You would loop over the checkboxes, and build a range using Application.Union() (plenty of examples of that here on SO). When you're done looping then select the built-up range.

Or you can build a string like "H3,F6" and use Range(rangeString).Select

For example:

Sub CheckSelection()
    Dim s As String, i As Long, sep
    For i = 1 To 6
        With Me.Controls("Checkbox" & i)
            If .Value = True Then
                s = s & sep & .Tag   'ranges are stored in checkboxes' Tag property
                sep = ","
            End If
        End With
    Next i
    If Len(s) = 0 Then s = "A1" 'default selection if none chosen...
    ActiveSheet.Range(s).Select
    Debug.Print s
End Sub

Private Sub CheckBox1_Click()
    CheckSelection
End Sub
'...
' etc
'...
Private Sub CheckBox6_Click()
    CheckSelection
End Sub

Multiple selection based on checkboxes

1 Answers