Stepping through a series of check boxes

[Westbury]

I've inserted a Form control check box in sheet "Start sheet". This check box is made up of 9 rows & 2 columns ( cells K26:L34) with the names of continents [column K] so that a user can select one or more [tick boxes in column L]. I would like to use vba to step through these check boxes and for each one to perform a task if the value is true. The task is to copy data from another sheet for processing--the easy bit. With my limited knowledge of VBA I don't know how to interrogate the check boxes using vba; can anyone offer some advice please?

Thanks
Geoff

 

[Yongle]

Here is a simple way to loop through the checkboxes on a sheet to identify which ones are ticked
- run the code and the message box lists all ticked form control checkboxes on the active sheet

Sub CheckboxLoop()
    Dim box As Shape, msg As String
    For Each box In ActiveSheet.Shapes
        If box.Type = msoFormControl And box.FormControlType = xlCheckBox And box.ControlFormat.Value = xlOn Then
            msg = msg & vbCr & box.Name & vbTab & " ticked"
        End If
    Next box
    MsgBox msg
End Sub

What we now need to do is to tell VBA what to do when a particular checkbox is ticked

Consider using Select Case to tell VBA what to do next. Consider renaming the checkboxes to make the code read easier and then your code will look something like this

Sub CheckboxLoop()
    Dim box As Shape 
    For Each box In ActiveSheet.Shapes
        If box.Type = msoFormControl And box.FormControlType = xlCheckBox And box.ControlFormat.Value = xlOn Then
            Select Case box.Name
                Case "continent1"
                    'do something
                Case "continent2"
                    'do something
                'etc
            End Select
        End If
    Next box
End Sub

 

[Westbury]

Yongle,

Thank you; this is just what I need.

Geoff

 

[Westbury]

I've tried using the first sub that you replied with but I'm getting a warning

"Object doesn't support this property or method" What's causing this?

 

[Yongle]

I am flumoxed because it works for me

What are the names of your checkboxes?
Which version of Excel are you using?

 

[Westbury]

Excel as part of Home Office 2013.

the checkboxes are in a tabular format with 2 columns, continent name on the left tick box on the right. There are no properties with the check boxes if I right click on them; should there be?

 

[Yongle]

If you right-click on a checkbox it's name appears in the name box (above cell A1)

I would expect "Checkbox 1" etc
(ie with a space between the word Checkbox and the number)

 

[Westbury]

Thanks, found them but then decided to change to ActiveX check boxes.

I now have this code for checking if at least one box is ticked :-

Dim iNum As Long
Dim iCheck As Long


For iNum = 1 To 9


If ActiveSheet.OLEObjects("CheckBox" & iNum).Object.value = "True" Then

iCheck = iCheck + 1
End If

Next iNum


If iCheck = 0 Then

MsgBox ("At least one continent must be selected" & vbCrLf & "from the list on the Start sheet")

Exit Sub

End If



and to copy the selected continent data from a ws with named ranges

Dim LastRow As Long
Sheets("Start sheet").Select


Dim xNum As Long


xNum = 1


If ActiveSheet.OLEObjects("CheckBox" & xNum).Object.value = "True" Then
Range("tbl_Africa").Copy
Worksheets("Destination options").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If

'find first empty row in col A
xNum = xNum + 1


If ActiveSheet.OLEObjects("CheckBox" & xNum).Object.value = "True" Then
Range("tbl_Asia").Copy
Worksheets("Destination options").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

End If

There are 9 steps in my code but have just posted 2 steps here for anyone to get ideas from. I'm sure this isn't very elegant but it works for a VBA novice!