Data from a Userform is added to the top if the worksheet

**n_malley** · 01-11-2013, 07:32 AM

Hi All,

i'm new to the forum so apologies if i have added this incorrectly

i'm creating a form which added information to the select worksheet- when the worksheet is selected the last blank row is highlighted.
so far the combo box highlights the correct worksheet and goes to the last blank row but the command button add the data to the top of the worksheet. i need it to be added to the last blank row.

below is my code (sorry if its a mess i'm brushing up on code i have used for years

)

Option Explicit
Dim WkSht As Worksheet
Dim lngLastRow As Long
Dim rngInput As Range



Private Sub ComboBox1_Click()
Application.ScreenUpdating = 0
Sheets(ComboBox1.Value).Visible = True
Application.Goto Sheets(ComboBox1.Value).[a1], True
Application.ScreenUpdating = 1


End Sub

Private Sub CommandButton1_Click()
lngLastRow = ActiveSheet.UsedRange.Rows.Count + 1
Set rngInput = Cells(lngLastRow, 2).End(xlUp).Offset(1, 0)
rngInput.Select
Range("b1") = TextBox1.Text
Range("c1") = TextBox2.Text
Range("d1") = TextBox3.Text
Range("e1") = TextBox4.Text

End Sub


Private Sub UserForm_Initialize()
    For Each WkSht In ThisWorkbook.Worksheets
        ComboBox1.AddItem (WkSht.Name)
    Next WkSht
End Sub

any help would be fantastic.
thanks!

**Norie** · 01-11-2013, 07:44 AM

Try this.

Private Sub CommandButton1_Click()

  Set rngInput = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

  With rngInput
    .Value = TextBox1.Text
    .Offset(,1) = Textbox2.Text
    .Offset(,2) = Textbox3.Text
    .Offset(,3) = Textbox4.Text
   End With

End Sub