Solved: Adding text to a cell with CheckBoxes

[AA_20069]

hi all,

Wondering if someone could help me out please.

I have about 14 CheckBoxes on a form with a command button; what i am trying to acheive, is that when the button is pressed, depending what checkboxes are ticked, numbers will appear in a cell

So for example if CheckBox 1,3 and 8 are checked - then the value in cell M5 will be A,B & H

Any help will be wonderfull...

Thanks

[GTO]

Probably just the blonde guy, but how are the checkboxes tied to a given value. Also, does that last bit mean you want to concatenate the vals or add them?

[Bob Phillips]

Dim i As Long
Dim iNext As Long
Dim tmp As String

For i = 1 To 14

If Me.Controls("CheckBox" & i).Value Then

iNext = iNext + 1
tmp = tmp & Chr(64 + iNext) & ", "
End If
Next i
Range("M5").Value2 = Left$(tmp, Len(tmp) - 2)

[AA_20069]

wow, thnks XLD - one question - as GTO said .... how do i define a value to each checkbox?

[mdmackillop]

Use Tags


Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 to 14
With Me.Controls("Checkbox" & i)
.Tag = i
End With
Next
End Sub

Private Sub CommandButton1_Click()
Dim t As Long, i As Long
For i = 1 To 14
With Me.Controls("Checkbox" & i)
t = t - .Value * .Tag
End With
Next
MsgBox t
End Sub

[Bob Phillips]

I didn't understand the question when Mark asked it, and I still don't.

[AA_20069]

I didn't understand the question when Mark asked it, and I still don't.

Oh ok, lemme try another way...

Lets say we have CheckBox1:14, now if CheckBox1:3 are selected, then in cell M5 - after clicking the button - the following should appear Mustang, Alfa, VW

Now, how do we define each CheckBox to have a make of car assigned to it (instead of A,B,C as mentioned before)??

[mdmackillop]

Still use tags

Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
Dim arr
arr = Array("Mustang", "Alfa", "VW")
For i = 0 To 2
With Me.Controls("Checkbox" & i + 1)
.Tag = arr(i)
End With
Next
End Sub

Private Sub CommandButton1_Click()
Dim t As String, i As Long
For i = 1 To 3
With Me.Controls("Checkbox" & i)
If .Value Then t = t & .Tag & ", "
End With
Next
Cells(1, 1) = Left(t, Len(t) - 2)
End Sub

[AA_20069]

Still use tags

Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
Dim arr
arr = Array("Mustang", "Alfa", "VW")
For i = 0 To 2
With Me.Controls("Checkbox" & i + 1)
.Tag = arr(i)
End With
Next
End Sub

Private Sub CommandButton1_Click()
Dim t As String, i As Long
For i = 1 To 3
With Me.Controls("Checkbox" & i)
If .Value Then t = t & .Tag & ", "
End With
Next
Cells(1, 1) = Left(t, Len(t) - 2)
End Sub


BRILLIANT :rotlaugh: thanks so much mdmackillop and XLD