VBA that returns value of checkbox captions into a cell

[jasonfish11]

I've searched everywhere and can't seem to find an answer.

I am trying to build out a macro off a userform where someone can select multiple items and when they hit OK all of those items (captions for the check boxes) will be entered into a specific location, preferably separated by a comma.

Example:
Choose equipment needs
[x] laptop
[ ] desktop
[x] phone
[ ] 2 monitors
[x] 3 monitors

If this was the outcome and the user hit "OK" the result would show the following in a specific cell.

"laptop,phone,3 monitors"

I have the userform built out, I just don't know where to begin to "capture" the caption of the checked boxes and list them in a specific location. I do not know if this is even possible given I can't find anything close online. If I'm going down the wrong path trying to use check boxes in the userform, and there is a better way to do this I would be happy to hear. Any help is appreciated.

Thanks in advance.

 

[My Aswer Is This]

Try this:
Will Put results in Activesheet Range("A1")

Private Sub CommandButton2_Click()
'Modified  4/24/2019  2:24:33 PM  EDT
On Error GoTo M
Dim ans As String
Dim Anss As String
For Each xcontrol In Me.Controls
    If TypeName(xcontrol) = "CheckBox" And xcontrol.Value = True Then ans = ans & xcontrol.Caption & ","
Next xcontrol
Anss = Left(ans, Len(ans) - 1)
ActiveSheet.Cells(1, 1).Value = Anss
Exit Sub
M:
MsgBox "You selected no CheckBoxes"
End Sub

 

[CalcSux78]

Another option would be to replace the checkboxes with one ListBox housing all of the options. The ListBox MultiSelect should be set to 1-fmMultiSelectMulti and (Optional) set the ListStyle to 1-fmListStyleOption.

From there, use the MouseUp event of your ListBox to create your string.

Userform code:

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim i&, Arr As Variant
Dim str$        'variable holding string value of choices
Dim coll As New Collection
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then coll.Add ListBox1.List(i)
Next i
str = ""
If coll.Count = 0 Then Exit Sub
ReDim Arr(1 To coll.Count)
For i = 1 To coll.Count
    Arr(i) = coll.Item(i)
Next i
str = Join(Arr, ",")
MsgBox (str)    'change this to assign str value to your worksheet
End Sub

 

[DanteAmor]

Try this:
Will Put results in Activesheet Range("A1")

Private Sub CommandButton2_Click()
'Modified  4/24/2019  2:24:33 PM  EDT
On Error GoTo M
Dim ans As String
Dim Anss As String
For Each xcontrol In Me.Controls
    If TypeName(xcontrol) = "CheckBox" [COLOR=#0000ff]And xcontrol.Value = True[/COLOR] Then ans = ans & xcontrol.Caption & ","
Next xcontrol
Anss = Left(ans, Len(ans) - 1)
ActiveSheet.Cells(1, 1).Value = Anss
Exit Sub
M:
MsgBox "You selected no CheckBoxes"
End Sub

If you have other controls, like a textbox, it can not have a value = True, then it sends error and it goes m:

Then:

Private Sub CommandButton2_Click()
'Modified  4/24/2019  2:24:33 PM  EDT
    Dim ans As String
    For Each xcontrol In Me.Controls
        If TypeName(xcontrol) = "CheckBox" Then
            If xcontrol.Value = True Then ans = ans & xcontrol.Caption & ","
        End If
    Next xcontrol
    If ans = "" Then
        MsgBox "You selected no CheckBoxes"
    Else
        ActiveSheet.Cells(1, 1).Value = Left(ans, Len(ans) - 1)
    End If
End Sub

 

[jasonfish11]

If you have other controls, like a textbox, it can not have a value = True, then it sends error and it goes m:

Then:

Private Sub CommandButton2_Click()
'Modified  4/24/2019  2:24:33 PM  EDT
    Dim ans As String
    For Each xcontrol In Me.Controls
        If TypeName(xcontrol) = "CheckBox" Then
            If xcontrol.Value = True Then ans = ans & xcontrol.Caption & ","
        End If
    Next xcontrol
    If ans = "" Then
        MsgBox "You selected no CheckBoxes"
    Else
        ActiveSheet.Cells(1, 1).Value = Left(ans, Len(ans) - 1)
    End If
End Sub

This worked, thank you both so much.

 

[My Aswer Is This]

My script only looks at CheckBoxes. It does not look at TextBoxes.

If you have other controls, like a textbox, it can not have a value = True, then it sends error and it goes m:

Then:

Private Sub CommandButton2_Click()
'Modified  4/24/2019  2:24:33 PM  EDT
    Dim ans As String
    For Each xcontrol In Me.Controls
        If TypeName(xcontrol) = "CheckBox" Then
            If xcontrol.Value = True Then ans = ans & xcontrol.Caption & ","
        End If
    Next xcontrol
    If ans = "" Then
        MsgBox "You selected no CheckBoxes"
    Else
        ActiveSheet.Cells(1, 1).Value = Left(ans, Len(ans) - 1)
    End If
End Sub

 

[My Aswer Is This]

The script I provided works for me and only looks at CheckBoxes

It does not look at textboxes:

If TypeName(xcontrol) = "CheckBox" Then
​

 

[DanteAmor]

The script I provided works for me and only looks at CheckBoxes

It does not look at textboxes:

If TypeName(xcontrol) = "CheckBox" Then
​

In agreement. Just put on your test form, a textbox. Execute the form, check several checkboxes and press your commandbutton.

 

[DanteAmor]

This worked, thank you both so much.

I'm glad to help you. Thanks for the feedback.

 

[My Aswer Is This]

I'm not sure what your getting at. If I have a Textbox with a value or not a value my script still works. The script does not look at textboxes. I have used this type script many times.

In agreement. Just put on your test form, a textbox. Execute the form, check several checkboxes and press your commandbutton.