[RESOLVED] Coding for textboxes within Visual Basic

[DannyCMA]

Hi all,

I am new to the forums (as you will see from my post count obviously :P)

First, happy New Year to you all.

I am currently writing a userform for work in which we require people to fill in events that they are interested in. This will then be uploaded to our database, meaning that I am required to stick to certain fields.

This problem has meant that I have roughly 15 checkboxes inputting information into the same cell.

When more than 1 checkbox is selected it only enters the value of the last checkbox in the selection.

My code is shown below.

If RunningCheckBox1.Value = True Then Cells(iRow, 26).Value = "Tunbridge Wells Half Marathon,"

If RunningCheckBox2.Value = True Then Cells(iRow, 26).Value = "Silverstone Half Marathon,"

If RunningCheckBox3.Value = True Then Cells(iRow, 26).Value = "Paris Marathon,"

If RunningCheckBox4.Value = True Then Cells(iRow, 26).Value = "London Marathon 2013,"

If RunningCheckBox6.Value = True Then Cells(iRow, 26).Value = "Great Manchester Run,"

If RunningCheckBox7.Value = True Then Cells(iRow, 26).Value = "Super Hero Run,"

If RunningCheckBox8.Value = True Then Cells(iRow, 26).Value = "Edinburgh Marathon,"

If RunningCheckBox9.Value = True Then Cells(iRow, 26).Value = "Bupa London 10k,"

If RunningCheckBox10.Value = True Then Cells(iRow, 26).Value = "British London 10k,"

If RunningCheckBox11.Value = True Then Cells(iRow, 26).Value = "London Triathlon,"

If RunningCheckBox12.Value = True Then Cells(iRow, 26).Value = Me.txtOtherRun.Value

If RunningCheckBox13.Value = True Then Cells(iRow, 26).Value = "Adidas Women's 5k,"

If RunningCheckBox14.Value = True Then Cells(iRow, 26).Value = "Great North Run,"

If RunningCheckBox15.Value = True Then Cells(iRow, 26).Value = "Royal Parks Half Marathon,"

If RunningCheckBox16.Value = True Then Cells(iRow, 26).Value = "Great Birmingham Run,"

If RunningCheckBox18.Value = True Then Cells(iRow, 26).Value = "Great South Run,"

If RunningCheckBox19.Value = True Then Cells(iRow, 26).Value = "Santa Run,"

If RunningCheckBox20.Value = True Then Cells(iRow, 26).Value = "Big Fun Run,"

So for example if RunningCheckBox19 and RunningCheckBox20 were selected, the cell would simply read, Big Fun Run,

Is there anyway to make the value show for all of the checkboxes selected?

If you need more of the code then please let me know and I will copy that into the thread.

Thanks in advance

Best wishes

Daniel

[LaVolpe]

I'm not familiar with the exact syntax for Excel automation

1) Before your IF statements above, clear the cell
2) Each of your IF statements needs to concatenate the current cell value and the new value. Something like the following, but again, use appropriate syntax

If RunningCheckBox20.Value = True Then Cells(iRow, 26).Value = Cells(iRow, 26).Value & "Big Fun Run,"

[Spoo]

Daniel

Do you get this gist of LaVolpe's suggestions?

His "concatenate" suggestion would go like this...

1. click RunningCheckBox19, then Cells(iRow, 26).Value becomes
   "Santa Run,"
2. click RunningCheckBox20, then Cells(iRow, 26).Value "remembers"
   the value of "Santa Run," and adds the value "Big Fun Run," to become
   "Santa Run,Big Fun Run,"

A nit-picky suggestion ..

to add a space during the concatenation process, change LaVolpe's code to this

If RunningCheckBox20.Value = True Then Cells(iRow, 26).Value = Cells(iRow, 26).Value & " " & "Big Fun Run,"

... to get this

"Santa Run, Big Fun Run,"

EDIT:

BTW, welcome to the Forums ..

Spoo

[DannyCMA]

Thanks to both of you. Exactly what I needed!

Only just really getting into VBA so a bit slow on the uptake!

Thanks again to you both and thank you Spoo for the welcome. I am hoping to advance my skills in VBA so should be around a bit, although reading the questions, there aren't many I can help answer!

Best wishes

Daniel