How would I get VBA to include the result of multiple checkboxes into one cell

[DreyFox]

For example, I have multiple checkboxes shown below:

When I select the options I want, I would like the "Additional Options" cell to be filled with associated values of the checkboxes. For example, If I select the Arctic/Salinity Package Option, the Drill Depth Sensor Option, and the Feed Rail Lubrication Option, then the cell Additional Options would show ADL. I tried to set this up using the following below:

Range("M109").Value = IIf(Me.CheckBox33, "A", "")

But it only does it for each individual option, not for all of it.

Any help would be greatly appreciated!

 

[JLGWhiz]

This is based on the check boxes being Active-x controls. The checkbox from the Form Controls tool box requires a different syntax. The text in the checkbox is a caption, so we first have to establish that the checkbox is checked by using a loop to go through all controls on the sheet and pick out the checkboxes, see if they are 'True' , then use the Left function to clip off the letter designation and put in in M109.

Dim ctl As Object
    For Each ctl In ActiveSheet.OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            If ctl.Object.Value = True Then
                If Range("M109") = "" Then
                    Range("M109") = Left(ctl.Object.Caption, 1)
                Else
                    Range("M109") = Range("M109").Value & vbLf & Left(ctl.Object.Caption, 1)
                End If
            End If
        End If
    Next

 

[DreyFox]

This is based on the check boxes being Active-x controls. The checkbox from the Form Controls tool box requires a different syntax. The text in the checkbox is a caption, so we first have to establish that the checkbox is checked by using a loop to go through all controls on the sheet and pick out the checkboxes, see if they are 'True' , then use the Left function to clip off the letter designation and put in in M109.

Dim ctl As Object
    For Each ctl In ActiveSheet.OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            If ctl.Object.Value = True Then
                If Range("M109") = "" Then
                    Range("M109") = Left(ctl.Object.Caption, 1)
                Else
                    Range("M109") = Range("M109").Value & vbLf & Left(ctl.Object.Caption, 1)
                End If
            End If
        End If
    Next

Thank you for your response! Where would I put this in? Would they go in each of the checkboxes?

 

[JLGWhiz]

The code would go into a standard code module, like module1. It can then be called from other code where needed.

Sub t()
Dim ctl As Object
    For Each ctl In ActiveSheet.OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            If ctl.Object.Value = True Then
                If Range("M109") = "" Then
                    Range("M109") = Left(ctl.Object.Caption, 1)
                Else
                    Range("M109") = Range("M109").Value & vbLf & Left(ctl.Object.Caption, 1)
                End If
            End If
        End If
    Next
End Sub