CODEWITHSUNDEEP
excelvba
Abs7778
Abs7778

Reputation: 3

Multiple selection based on checkboxes

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:

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

Upvotes: 0

Views: 497

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 0

Related Questions