Displaying multiple string outputs in a single cell

[mtp9302]

Hi, I have a question regarding the output of multiple strings. I am writing code for a user form with 16 check boxes on it. More than one check box can be selected at a time. Each check box has a certain string attached to it that I want to output to a cell. I have got the entire thing to work except I cannot figure out how to output more than one string to the output cell B5. Right now, for example, if I check "aaaaa" and "fffff", cell B5 reads only the last box checked, or "fffff". I would also like the program to put each item on the next line in the same cell (like hitting the Return key), but I didn't know how to do that either. The code I currently have is shown below. The names Option1-16 are the names of the check boxes.

n = 0

Rows("5:5").Select
Selection.Insert Shift:=xlDown

Range("B5:F5").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With

If Option1 Then
Range("B5").Value = "aaaaa"
n = n + 1
End If

If Option2 Then
Range("B5").Value = "bbbbb"
n = n + 1
End If

If Option3 Then
Range("B5").Value = "ccccc"
n = n + 1
End If

If Option4 Then
Range("B5").Value = "ddddd"
n = n + 1
End If

If Option5 Then
Range("B5").Value = "eeeee"
n = n + 1
End If

If Option6 Then
Range("B5").Value = "fffff"
n = n + 1
End If

If Option7 Then
Range("B5").Value = "ggggg"
n = n + 1
End If

If Option8 Then
Range("B5").Value = "hhhhh"
n = n + 1
End If

If Option9 Then
Range("B5").Value = "iiiii"
n = n + 1
End If

If Option10 Then
Range("B5").Value = "jjjjj"
n = n + 1
End If

If Option11 Then
Range("B5").Value = "kkkkk"
n = n + 1
End If

If Option12 Then
Range("B5").Value = "lllll"
n = n + 1
End If

If Option13 Then
Range("B5").Value = "mmmmm"
n = n + 1
End If

If Option14 Then
Range("B5").Value = "nnnnn"
n = n + 1
End If

If Option15 Then
Range("B5").Value = "ooooo"
n = n + 1
End If

If Option16 Then
Range("B5").Value = Other 'This is a text box where the user can type in anything
n = n + 1
End If

Rows("5:5").Select
Selection.RowHeight = 27 + 13.2 * (n - 1)[/face] 'This increases the row height depending on how many items are checked

Also, I did not know if I could simplify the 16 if statements by using the case command. I want it to consider all cases so that it keeps looking for the next check box after it finds one until it has looked at all 16.

Any help anyone can give me on this matter would be much appreciated. Thanks.

 

[erik.van.geit]

Hi,

this works for me (only the fill in part)

Option Explicit

Private Sub CommandButton1_Click()
Dim myCell As Range
Dim dataArray As Variant
Dim i As Integer
Dim n As Integer

dataArray = Array("aaaaa", "bbbbb", "ccccc", "ddddd", "eeeee", "fffff", "ggggg", _
"hhhhh", "iiiii", "jjjjj", "kkkkk", "lllll", "mmmmm", "nnnnn", "ooooo", Other)


Set myCell = Range("B5")
myCell = ""

Application.ScreenUpdating = False

    For i = 1 To 16
        If Me.Controls("Option" & i) Then
        myCell = myCell & IIf(myCell = "", "", vbLf) & dataArray(i - 1)
        n = n + 1
        End If
    Next i
    
Application.ScreenUpdating = True

End Sub

please use codetags to post code

kind regards,
Erik

 

[mtp9302]

Erik,

Thanks for your help, much appreciated. I apologize about the code, I am now aware of the codetags. Thanks again.

-Matt

 

[erik.van.geit]

You're welcome, Matt

I hope it works for you: my tests were OK