Filling one cell with Multiple checkbox data

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
Hi, I have a userform that pops up when you enter a specific cell. The userform has 6 Checkboxs on it named checkbox1, checkbox2 etc and 1 textbox. The captions for the checkboxes are the names in cells B1-B5 and and a 6th named "Other". These checkboxes are only visible when there is a name in the corresponding cell. What I need to do is fill Cell F5 with the names that are selected from the checkboxes seperated by a comma, but I can't get the code right if there is only one selection made.

Here is the data:
Book1
ABCD
1ProjectManagerBob
2ProjectTeamTest
3Test2
4
5
Sheet1


This is the code I have so far, It works but puts a comma in front of the text if only 1 option has been selected:

Code:
Private Sub CommandButton1_Click()
    If UserForm10.CheckBox1.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.CheckBox1.Caption
    End If
    If UserForm10.CheckBox2.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.CheckBox2.Caption
    End If
    If UserForm10.CheckBox3.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.CheckBox3.Caption
    End If
    If UserForm10.CheckBox4.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.CheckBox4.Caption
    End If
    If UserForm10.CheckBox5.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.CheckBox5.Caption
    End If
    If UserForm10.CheckBox6.Value = True Then
        ActiveCell.Value = ActiveCell.Value + ", " + UserForm10.TextBox1.Value
        End If
 UserForm10.Hide
End Sub

Here is a copy of the userform also:

untitled.bmp
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

try adding the line of code before the end sub

Code:
If Not IsEmpty(ActiveCell) Then ActiveCell.Value = Right(ActiveCell.Value, Len(ActiveCell.Value) - 2)

HTH

Tony
 

ashok_theagarajan

Board Regular
Joined
Oct 4, 2005
Messages
68
You could try some thing like this before the code

Private Sub CommandButton1_Click()

If ActiveCell.Value = "" Then
If userform10.checkbox1.Value = True Then
ActiveCell.Value = userform10.checkbox1.Caption
End If
< Paste the remaining checkbox options like this too >

Else
If userform10.checkbox1.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.checkbox1.Caption
End If
If userform10.CheckBox2.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.CheckBox2.Caption
End If
If userform10.CheckBox3.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.CheckBox3.Caption
End If
If userform10.CheckBox4.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.CheckBox4.Caption
End If
If userform10.CheckBox5.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.CheckBox5.Caption
End If
If userform10.CheckBox6.Value = True Then
ActiveCell.Value = ActiveCell.Value + ", " + userform10.TextBox1.Value
End If
End If
userform10.Hide
end sub
 

rmtimmah

Board Regular
Joined
May 14, 2007
Messages
153
Thanks. ACW option works better as checkbox1 would not always be selected first.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,807
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top