VBA userform multiple TextBoxes in one cell

[Pisus]

Hello everyone,
I have a code which helps to edit/add new data into a table.
I'd like that one of the cells values would arrive from multiple TextBoxes and the code would be available for the whole column.
The code i have :

Sub EditAdd()
Dim id As Integer, i As Integer, j As Integer, flag As Boolean
Dim emptyRow As Long
If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 10
Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
i = i + 1
Loop
If flag = False Then
For j = 1 To 10
Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If
End If
End Sub

The code i need to enter (something like):

If j = 9 Then Cells(i, j).Value =
UserForm1.Controls(TextBox12).Value & vbCrLf &
UserForm1.Controls(TextBox13).Value & vbCrLf &
UserForm1.Controls(TextBox14).Value & vbCrLf &
UserForm1.Controls(TextBox15).Value & vbCrLf &
UserForm1.Controls(TextBox16).Value

or if Cells.column = 9 then

or if cells(i, j).column = 9 then.
End If

I tried, but couldn't figure it out.
Any help or tips are really appreciated

Cross-posted here

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Sub EditAdd()
    Dim id As Integer, j As Integer
    Dim RecordRow As Long
    Dim str As String
    
    id = Val(Me.TextBox1.Value)
    If Len(id) = 0 Then Exit Sub
    
    For i = 12 To 16
        str = str & Me.Controls("TextBox" & i).Value & IIf(i < 16, Chr(10), "")
    Next i
    
    RecordRow = WorksheetFunction.CountA(Range("A:A")) + 1
    m = Application.Match(id, Columns(1), 0)
    If Not IsError(m) Then RecordRow = CLng(m)
    
    For j = 1 To 10
        Cells(RecordRow, j).Value = IIf(j = 9, str, Me.Controls("TextBox" & j).Value)
    Next
    
End Sub

BTW thanks for letting us know about cross post

Dave

 

[Pisus]

Just tested. Works great. Thank you for your time