VBA Userform - Fill Data within a specific range

[quant1313]

Hello,

I have built a userform and I want the data from the userform to be entered into a specific range of cells. Right now the code finds the last available row but I want the userform to enter data into specific cells. Paritularly, I want the first data from my textboxes to be entered into cells B7:E7 of a particular active sheet (chosen from a combobox), and then the next userform will enter data in cells B:8:E7, all the way down to B12:E12. Below is the code I am currently using:

Private Sub CommandButton1_Click()
TargetSheet = ComboBox1.Value
If TargetSheet = "" Then
Exit Sub
End If
Worksheets(TargetSheet).Activate
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Cells(lastrow + 1, 2).Value = TextBox1.Value
ActiveSheet.Cells(lastrow + 1, 3).Value = TextBox2.Value
ActiveSheet.Cells(lastrow + 1, 4).Value = TextBox3.Value
ActiveSheet.Cells(lastrow + 1, 5).Value = TextBox4.Value
MsgBox ("Data upload to Summary Tab Complete")

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""


Thank you for the help!

 

[Trebor76]

Hi quant1313,

Try this:

Option Explicit
Private Sub CommandButton1_Click()

    Dim wsOutput As Worksheet
    Dim ctrl As Control
    Dim i As Long
    
    On Error Resume Next
        Set wsOutput = ThisWorkbook.Sheets(CStr(ComboBox1.Value))
    On Error GoTo 0
    If wsOutput Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For i = 1 To 4
        wsOutput.Range(Choose(i, "B7", "C7", "D7", "E7")).Value = Controls("TextBox" & i)
        Controls("TextBox" & i) = ""
    Next i
    
    Me.TextBox1.SetFocus
    
    Application.ScreenUpdating = True
    
    MsgBox "Data upload to Summary Tab Complete", vbInformation

End Sub

You could also load the sheet tabs to the combo box upon activating the form so its selection should not error out.

Regards,

Robert