Loop Through CheckBoxes in Userform in Powerpoint with VBA

Question

When a user runs a GUI on a PPT slide it brings up a userform, pictured below.

They can select up to 3 hazards in the check boxes. I am trying to find a way to loop through all the check boxes to see which ones are selected (if none, then it will just exit the sub). Then, based on which one(s) is(are) selected, it will enter a corresponding image into a shape within the PPT slide. The shapes are lined up from left to right. The "highest ranking" selection would go in the left shape, with the "lowest ranking" of the three going in the right box. Only the first and second shapes would be filled if there are only two options selected. I am not sure the easiest way to assign values (i.e. 1, 2, 3, etc) to the order of importance for each option. I would prefer not to have to cover every combination with If -> Then statements as that would be tedious and quite time consuming. I assume there is a better way to do this?

I am able to loop through the combobox lists easily by using the following code:

Private Sub MainImage()
Call Dictionary.MainImageDict

'References the Dictionary for the Main Image options.

ComboBoxList = Array(CStr(ComboBox2)) 'ComboBox2 is the Main Image dropdown.

    For Each Ky In ComboBoxList
    On Error Resume Next
    'If nothing is selected in the Main Image dropdown, do nothing and exit this sub.
    If Ky = "" Then
    Exit Sub
    'Otherwise, if anything is selected in the Main Image dropdown, insert image and remove placeholder text.
    ElseIf Ky <> "" Then
    ActiveWindow.Selection.SlideRange.Shapes("MainImage").Fill.UserPicture (dict3.Item(Ky)(0))

    End If

    Next

Set dict3 = Nothing

End Sub

The dictionary referenced above is as follows:

Public dict, dict2, dict3, dict4, dict5 As Object, Key, val 'Makes the dictionaries public so they can be accessed by other Modules.

Sub MainImageDict()

'This is the dictionary for the Main Image portion of the slides.

Set dict3 = CreateObject("Scripting.Dictionary")

Key = "Day 1 High Temperatures": val = Array("URL_to_Image")
dict3.Add Key, val
Key = "Day 2 High Temperatures": val = Array("URL_to_Image")
dict3.Add Key, val

End Sub

I would like to do something similar, if possible, with the check boxes. Since they are all separate, and not combined into one (like the combobox), I am not sure how to go about doing this. Any help would be greatly appreciated! Please let me know if anything I said does not make sense. Thank you!

UPDATED CODE

Using the suggested code works wonderfully. Now I have one final question. I have modified the code to the following:

    Private Sub Hazards()
    Call Dictionary.HazardsDict

   'References the Dictionary for the Hazard Image options.

    Dim chkboxes As Variant
    Dim iCtrl As Long

    Select Case CountSelectedCheckBoxes(chkboxes)
        Case Is > 3
            MsgBox "Too many selected checkboxes!" & vbCrLf & vbCrLf & "please select three checkboxes only!", vbCritical
        Case Is = 1 'If only one checkbox is selected
            For iCtrl = LBound(chkboxes) To UBound(chkboxes)
              HazardList = Array(chkboxes(iCtrl).Caption)
              Debug.Print chkboxes(iCtrl).Caption
              Next
                'MsgBox chkboxes(iCtrl).Tag '<--| here you output each selected checkbox Tag. you can use this "number"
            For Each Ky In HazardList
                ActiveWindow.Selection.SlideRange.Shapes("Hazard1").Fill.UserPicture (dict5.Item(Ky)(0))
                ActiveWindow.Selection.SlideRange.Shapes("Hazard1Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1)
            Next
        Case Is = 2 'If exactly 2 checkboxes are selected
            For iCtrl = LBound(chkboxes) To UBound(chkboxes)
              HazardList = Array(chkboxes(iCtrl).Caption)
              Debug.Print chkboxes(iCtrl).Caption
            Next
            For Each Ky In HazardList
                ActiveWindow.Selection.SlideRange.Shapes("Hazard1").Fill.UserPicture (dict5.Item(Ky)(0)) 'The checkbox with the lowest number in its Tag would go here.
                ActiveWindow.Selection.SlideRange.Shapes("Hazard1Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1)
                ActiveWindow.Selection.SlideRange.Shapes("Hazard2").Fill.UserPicture (dict5.Item(Ky)(0)) 'The checkbox with the second lowest tag number would go here
                ActiveWindow.Selection.SlideRange.Shapes("Hazard2Text").TextFrame.TextRange.Text = dict5.Item(Ky)(1)
            Next
    End Select

    Set dict5 = Nothing

End Sub

I am trying to figure out how I would write the code so that the checkbox with the lowest number in its Tag goes into "Hazard1", the Tag with the second lowest number (of the up to 3 that can be selected) goes into Hazard2, and the Tag with the third lowest number goes into Hazard3. Any ideas? Thank you!

user3598756 · Accepted Answer · 2017-03-05 21:51:37Z

1

you can iterate through UserForm Controls collection and check for those with "CheckBox" TypeName

while you could store some "importance rating" in Tag property of your checkboxes controls (when in VBA IDE press F4 to pop Property window, select each checkbox in your userform and type proper "importance rating" in the Tag entry)

all what above you can exploit as follows:

Private Sub InsertBtn_Click()
    Dim chkboxes As Variant
    Dim iCtrl As Long

    Select Case CountSelectedCheckBoxes(chkboxes)
        Case Is > 3
            MsgBox "Too many selected checkboxes!" & vbCrLf & vbCrLf & "please select three checkboxes only!", vbCritical
        Case Is < 3
            MsgBox "Too few selected checkboxes!" & vbCrLf & vbCrLf & "please select three checkboxes!", vbCritical            
        Case Else
            For iCtrl = LBound(chkboxes) To UBound(chkboxes)
                MsgBox chkboxes(iCtrl).Tag '<--| here you output each selected checkbox Tag. you can use this "number"
            Next
    End Select
End Sub

Function CountSelectedCheckBoxes(chkboxes As Variant) As Long
    Dim ctrl As Control
    ReDim chkboxes(1 To Me.Controls.count)

    For Each ctrl In Me.Controls '<--| loop through userform controls
        If TypeName(ctrl) = "CheckBox" Then '<--| check if current control is a "checkbox" one
            If ctrl Then '<--| check if it's "checked"
                CountSelectedCheckBoxes = CountSelectedCheckBoxes + 1 '<--| update checked checkboxes counter
                Set chkboxes(CountSelectedCheckBoxes) = ctrl '<--| store it in the array
            End If
        End If
    Next
    If CountSelectedCheckBoxes > 0 Then ReDim Preserve chkboxes(1 To CountSelectedCheckBoxes) '<--|size checkboxes array to actual checked checkboxes found
End Function

answered Mar 5, 2017 at 21:51

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hunter21188 Over a year ago

That works perfectly. Thank you! I have one last question. I have modified my original question. You can see the modified code and my question towards the bottom. Thanks again!

user3598756 Over a year ago

You are welcome. Please be informed that changing a question after receiving solution to the original one leads to what's been called "chamaleon" questions, which are not allowed here. So, if my answer solved your original question then mark it as accepted. While you may want to make a new post for your new issue. Thank you

Collectives™ on Stack Overflow

Loop Through CheckBoxes in Userform in Powerpoint with VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related