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.

 

[DanteAmor]

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.

I just wanted you to do a test. You have a detail if there are more controls in the userform.
If you only have checkbox you have no problems. But if you add another control like a textbox or a combo or a listbox, even a label, your code generates an error.

 

[My Aswer Is This]

You keep saying:

If you only have checkbox you have no problems​

. But if you add another control like a textbox or a combo or a listbox, even a label, your code generates an error.


I have tested my script 10 times and what your saying does not happen when I run the script.

I have several other Textboxes and other controls and I receive no errors.
​

I just wanted you to do a test. You have a detail if there are more controls in the userform.
If you only have checkbox you have no problems. But if you add another control like a textbox or a combo or a listbox, even a label, your code generates an error.

 

[DanteAmor]

You keep saying:

If you only have checkbox you have no problems​

. But if you add another control like a textbox or a combo or a listbox, even a label, your code generates an error.


I have tested my script 10 times and what your saying does not happen when I run the script.

I have several other Textboxes and other controls and I receive no errors.
​

Obviously it does not generate an error because you have On Error Goto M, but in cell A1 it does not put anything.

 

[My Aswer Is This]

I f a error occurs I should get a message Box popup. And the values do get entered in Range("A1") if there is no error.
The error code is put there in case no CheckBox is checked.

Show me the code your using that causes a error.

Obviously it does not generate an error because you have On Error Goto M, but in cell A1 it does not put anything.

 

[DanteAmor]

I f a error occurs I should get a message Box popup. And the values do get entered in Range("A1") if there is no error.
The error code is put there in case no CheckBox is checked.

Show me the code your using that causes a error.

As I already mentioned, it does not generate an error because you have "On Error Goto M", but it does not show anything in 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

 

[My Aswer Is This]

Well like I have said several times this script works for me. If you would like you can supply the user with the code you think he needs.

And my script does enter the values into A1 and only generates a error if no checkboxes are selected. And I do have other controls on my UserForm.


I'm using Excel 2013 and have never had any problem using this code.

 

[DanteAmor]

Well like I have said several times this script works for me. If you would like you can supply the user with the code you think he needs.

And my script does enter the values into A1 and only generates a error if no checkboxes are selected. And I do have other controls on my UserForm.


I'm using Excel 2013 and have never had any problem using this code.

Maybe it's my 2007 version. Sorry if it caused you any inconvenience.
Well the user already took my scrip, I just wanted to comment on the problem I have with your code, as I showed you, I do not put data in cell A1.
Have an excellent afternoon!

 

[kmclaugh]

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

Thank you so much for this! I have been searching for days trying to find a code to combine the checkboxes into a cell! You're Awesome