User form update the last row number on click-CodePudding

I'm trying to make a UserForm with approximately 50 or so TextBoxes. All textboxes except the first one take numbers or vbNullString. The UserForm inputs the same text in a column based on the number in the textbox. For example, if the user inputs 4 in RC924TB TextBox the form will fill 4 cells in a column with UL924 text then move to the next Textbox. I'm trying to find out how can I update the inputRange after filling a number of cells based on the textbox value. So far, here is what I have, however using the sum of values in each text box to offset the inputRange will get very messy. Could someone help with a more eloquent solution?

Private Sub SubmitBtn_Click()

Dim inputRange As Range
     
            
    'Checks if room name has been inputted

If roomNameTB.Value = vbNullString Then
    MsgBox "Please add Room Name"
    roomNameTB.SetFocus
Else
End If


    ' find last row and add 2
Set inputRange = ActiveSheet().Cells(Rows.Count, 2).End(xlUp).Offset(2)
    
    
    
    'inputs room name
inputRange.Offset(0, 1).Value = roomNameTB.Value



    'inputs bridge 1
inputRange.Offset(0, -1).Value = "Bridge"


    ' inputs UL924 devices
If RC924TB.Value = vbNullString Then 'do nothing

    Else
        inputRange.Offset(1, -1).Resize(RC924TB.Value).Value = "UL924"
End If


' inputs RC1RTB devices
If RC1RTB.Value = vbNullString Then 'do nothing

    Else
        inputRange.Offset(RC924TB.Value   1, -1).Resize(RC1RTB.Value).Value = "1R"
End If


End Sub

CodePudding user response：

Put the value UL924, 1R etc in the ControlTipText for the textbox concerned.

Private Sub SubmitBtn_Click()

    Dim c As Control, s As String
    Dim n As Long, rowIn As Long
    
    'Checks if room name has been inputted
    If RoomNameTB.Value = vbNullString Then
        MsgBox "Please add Room Name"
        RoomNameTB.SetFocus
        Exit Sub
    End If

    ' find last row and add 2
    rowIn = Cells(Rows.Count, "A").End(xlUp).Row   2
    
    'inputs bridge1 and room name and bridge 1
    Cells(rowIn, "A") = "Bridge"
    Cells(rowIn, "C") = RoomNameTB.Value
    rowIn = rowIn   1
   
    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            If c.Name Like "RC*" And IsNumeric(c.Value) Then
                
                n = c.Value
                If n > 0 Then
                    s = c.ControlTipText '"UL" & Mid(c.Name, 3, 3)
                    Cells(rowIn, "A").Resize(n).Value = s
                    rowIn = rowIn   n
                End If
                
            End If
        End If
    Next

End Sub