Search code examples
excelvbauserform

Userform opens when selecting command button. How do I make data input into the textboxes in the form insert onto row in separate sheet?

I'm trying to create a UserForm which will open when I select a command button. The purpose of it is to capture data from a selected row concerning a "tour" - i.e. the tour code, start date and end date, and then for me to "split" the tour, for which I need to enter new tour codes, start dates and end dates.

I need the data from the form to go into a separate sheet (called "splits"), so that I have a record of the original tour details, and the new tour details in one sheet. But I get a Run-Time error '1004' (Application-defined or object-defined error) when I try to run the macro. I'm new to VBA and I don't know what I've done wrong!

This is my code so far:

Private Sub UserForm_Initialize()
    With Me
        .OriginalTourCode.Value = Cells(ActiveCell.Row, "A").Value
        .OriginalStartDate.Value = Cells(ActiveCell.Row, "B").Value
        .OriginalEndDate.Value = Cells(ActiveCell.Row, "C").Value
    End With
End Sub

Private Sub SplitTourCommand_Click()

Dim ctrl As Control
    Dim ws As Worksheet
    Set ws = Sheets("Splits")

erow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0)
    Cells(erow, 1) = OriginalTourCode.Text
    Cells(erow, 2) = OriginalStartDate.Text
    Cells(erow, 3) = OriginalEndDate.Text
    Cells(erow, 4) = NewTourCode1.Text
    Cells(erow, 5) = NewStartDate1.Text
    Cells(erow, 6) = NewEndDate1.Text
    Cells(erow, 7) = NewTourCode2.Text
    Cells(erow, 8) = NewStartDate2.Text
    Cells(erow, 9) = NewEndDate2.Text
    Cells(erow, 10) = ReasonForSplit.Text


End Sub

Private Sub CloseCommand_Click()

Unload Me

End Sub

The Userform Intitialise section automatically fills in the first three cells of the UserForm, and then I'll use the form to enter the new data.

The Close command section is just a separate button on the form to exit out.

How to I get the form, when I click the "split tour" command button, to enter the data into the next empty row of the "splits" sheet?

Thanks so much in advance for helping.

Solution 
  • erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    There were 2 errors in your code. First, the command is xlUp, not x1Up - "xl" is short for "Excel", not "X One" - this is a perfect example of why you should almost always use Option Explicit

    Second: your code as-is will try to put the .Value from the cell into untyped variable erow - since the cell is blank (as the cell below the last cell with data), this means that erow will always be 0. And Row 0 does not exist to put data into.

    Instead, by using Range.Row, we get the next row number to insert data on