CODEWITHSUNDEEP
exceltextboxuserformvba
Awill
Awill

Reputation: 117

combining multiple userform textbox values into one cell

I have a userform that is filled out and enters the data on a spreadsheet. One feature I've added is frame of 10 textboxes which remain hidden unless data is entered in the previous box, allowing up to 10 entries. Because the user is entering participants' names in these text boxes, I want them all to fill the same cell of the spreadsheet, separated by commas. At first I entered the code without thinking and would receive 9 commas in the cell even when no names were added. I've since managed to get the second text box value preceded by a comma added to the cell but cannot get the value of any additional textboxes (3-10) to do the same.

ws.Range("I" & LastRow).Value = tbPart1.Value
If Not IsEmpty(tbPart2.Value) Then
ws.Range("I" & LastRow).Value = tbPart1.Value & "," & tbPart2.Value
ElseIf Not IsEmpty(tbPart3.Value) Then
ws.Range("I" & LastRow).Value = tbPart1.Value & "," & tbPart2.Value & "," & tbPart3.Value

End If

Upvotes: 0

Views: 4143

Answers (2)

Tyeler
Tyeler

Reputation: 1118

Awill, try something like this. It should fit your needs and it's pretty flexible code. Instead of making an If Then statement for every combination, you could just loop through all the textboxes and populate your worksheet more elegantly.

Dim ctrl As Control
Dim ctrlName As String, ctrlNum As Integer

For Each ctrl In Me.Controls 'Loops through all controls on the Userform
    If TypeName(ctrl) = "TextBox" Then 'Only acknowledges TextBox Controls
        ctrlName = ctrl.Name
        ctrlNum = CInt(Replace(ctrlName, "tbPart", "")) 'Names each textbox by number
        If ctrlNum > 0 And ctrlNum < 11 And ctrl.Value <> "" Then 'cycles through textboxes 1-10
            If ws.Range("I" & NextRow).Value <> "" Then 'If cell isn't blank, preclude with comma
                ws.Range("I" & NextRow).Value = ws.Range("I" & NextRow).Value & ", " & ctrl.Text
            Else: ws.Range("I" & NextRow).Value = ctrl.Text 'otherwise value only
            End If
        End If
    End If
Next ctrl

Upvotes: 0

Chrowno
Chrowno

Reputation: 198

Looping through all Textboxes and checking for its value:

Dim i As Integer

For i = 1 To 10

    'Loop through all 10 Textboxes and add its value to the cell
    If Not Controls("tbPart" & i).Value = "" Then

        'Check if cell is empty
        If ws.Range("I" & LastRow).Value = "" Then
            ws.Range("I" & LastRow).Value = Controls("tbPart" & i).Value
        Else
            ws.Range("I" & LastRow).Value = _
            ws.Range("I" & LastRow).Value & ", " & Controls("tbPart" & i).Value
        End if
    End If
Next i

Code is not tested.

Upvotes: 2

Related Questions