Excel VBA - Writing multiple userform checkbox values to a single cell

Question

I am trying to take the values passed from a userform that has 4 checkbox options and write them to a single concatenated cell.

When I select my userform like this:

enter image description here

I would like to save it to a single cell like this:

enter image description here

I tried accomplishing this with the following code (see below), but it doesn't work quite right with the commas and such if only the 2nd, 3rd, or 4th item is chosen without the first. I am convinced there is a better way but I can't figure it out or find an answer online.

Private Sub cmdSave_Click()
  Dim colors As String

   If chkRed = True Then
      colors = "Red"
   Else
      colors = colors
   End If

   If chkBlue = True Then
      colors = colors & ", Blue"
   Else
      colors = colors
   End If

   If chkGreen = True Then
      colors = colors & ", Green"
   Else
      colors = colors
   End If

   If chkYellow = True Then
      colors = colors & ", Yellow"
   Else
      colors = colors
   End If

   With colorsSheet
      .Cells(ActiveCell.Row, 1).Value = colors
   End With

   Unload Me

End Sub

Alex K. · Accepted Answer · 2013-03-11 16:33:04Z

4

Rename the frame control to frameColours then you can loop its checkboxes & build your string;

Dim chk as Control
Dim colors as string, delimiter as string

for Each chk In Me.frameColours.Controls
    if typeOf chk Is msforms.CheckBox then
        if (chk.Value) then
            colors = colors & delimiter & chk.Caption
            delimiter = ","
        end if
    end if
next

With colorsSheet
    .Cells(ActiveCell.Row, 1).Value = colors
End With

answered Mar 11, 2013 at 16:33

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

2 Comments

ReidC Over a year ago

Alex, that looks great. Thank you so much. I will try it out here and let you know how it goes. I haven't learned how to do it this way before and knew there must be a better way out there.

ReidC Over a year ago

Worked great. Only change I had to make was to add a space to the delimiter to change it from "," to ", "

findwindow · Accepted Answer · 2016-06-01 16:35:18Z

1

Private Sub Submit_Click()

Dim lngIndex As Long, strTemp As String


For lngIndex = 1 To 16

'There are 16 check boxex, hence 1 to 16 is given

If Me.Controls("CheckBox" & lngIndex) Then
    strTemp = strTemp & Me.Controls("TextBox" & lngIndex).Value & vbLf 

'Returns you the output in a new line within the same cell.

End If

Next lngIndex

Sheets("Sheet1").Range("A1").Value = Left$(strTemp, Len(strTemp) - 1)

End Sub

edited Jun 1, 2016 at 16:35

findwindow

3,2081 gold badge15 silver badges32 bronze badges

answered Mar 13, 2013 at 17:44

mac

111 bronze badge

Comments

Tim · Accepted Answer · 2013-03-11 16:39:42Z

0

If you want to eliminate the initial comma from output like ", Blue, Green, Yellow", you're going to have to change the code that adds those strings to check whether colors is empty. Something like:

If chkBlue = true Then
    If colors = "" Then
        colors = "Blue"
    Else
        colors = colors & ", Blue"
    End If
End If

Since "Red" is the first color you add, you can assume that colors is empty:

If chkRed = True Then
      colors = "Red"
End If

You dont' need Else ... colors = colors

answered Mar 11, 2013 at 16:39

Tim

1,8262 gold badges23 silver badges35 bronze badges

2 Comments

Tim Over a year ago

I like (and upvoted) @Alex K.'s solution. It uses less code, and won't require code changes if you add colors.

ReidC Over a year ago

Tim, you are right. Alex has a great solution. I like yours as well because it fixes my code as it stands. Glad to have 2 ways of accomplishing what I set out to do. Thank you.

Collectives™ on Stack Overflow

Excel VBA - Writing multiple userform checkbox values to a single cell

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related