How to Add Text to end of cell if multiple check boxes are true?

Question

I've got a form that when I hit the submit button enters the Notes into a cell. As a part of the notes I have checkboxes for various options. What I'd like to do is add the checkbox values to the end of same cell as the Notes. This is what I have:

    If Not IsEmpty(Notes.Text) Then
    ws.Range("N" & LastRow).Value = Notes.Text
    End If

    If TubeAdd.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", Tubing"
    End If

    If Snowshoe.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", Snowshoeing"
    End If

    If TubeExclusive.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", Tubing"
    End If

    If Legacy.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", Legacy Room"
    End If

    If NorthAxe.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", North Axe Room"
    End If

    If MidMountainLodge.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", MidMountain Lodge"
    End If

    If Catering.Value = True Then
    ws.Range("N" & LastRow).Value = ActiveCell & ", Catering"
    End If

The problem I'm running into is that if multiple checkboxes are selected it only shows the ", *****" of the last selected option. I.E. If there are Notes and Tubing & Catering are selected I only end up getting the result for catering (", Catering"). I need all the data to show up in the same cell.

Help?

In general you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. it would be useful to see sample data and expected outcome — cybernetic.nomad, Dec 18 '19 at 20:48
`ws.Range("N" & LastRow).Value = ws.Range("N" & LastRow).Value &...` — Scott Craner, Dec 18 '19 at 20:49

score 1 · Accepted Answer · answered Dec 18 '19 at 20:49

1

Create a Variable to store the string in and at the end of all the if statements stick that string in the cell.

dim notesval as string
If Not IsEmpty(Notes.Text) Then
    notesval = Notes.Text
End If
if TubeAdd.Value = True Then
    notesval = notesval & ", Tubing"
end if
'Finish the rest of the IFs
ws.Range("N" & LastRow).Value = notesval

answered Dec 18 '19 at 20:49

Warcupine

4,460
3
15
24

YAAAAAAAAAAS Thanks so much!! Took a second but I got it! – junepeace Dec 18 '19 at 20:58

How to Add Text to end of cell if multiple check boxes are true?

1 Answers1