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

1
votes

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.

3
excelvbauserform
What value does erow contain? (And what data type has it been defined as?) You can use Debug.Print to output it to the Immediate Window - Chronocidal
As I say, I'm new to VBA so I don't know what you mean by what value is erow? I thought that string of code just meant that it would find the next empty row on the "Splits" sheet? Where do I put Debug.print ? - Liz H
If you put, for example Debug.Print erow after the erow = .. row, then it will output the value for you to look at - but, you would actually get an error before that (see my answer below) - Chronocidal

3 Answers

0
votes

You were so close

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

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


End Sub
0
votes
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

0
votes

No Object Variable Necessary

The End Parameter is xlUp not x1Up.

Array Version

Option Explicit

Private Sub SplitTourCommand_Click()

    Const cSheet As String = "Splits"

    Dim erow As Long
    Dim vnt As Variant

    ReDim vnt(1 To 1, 1 To 10) As String

    vnt(1, 1) = OriginalTourCode.Text
    vnt(1, 2) = OriginalStartDate.Text
    vnt(1, 3) = OriginalEndDate.Text
    vnt(1, 4) = NewTourCode1.Text
    vnt(1, 5) = NewStartDate1.Text
    vnt(1, 6) = NewEndDate1.Text
    vnt(1, 7) = NewTourCode2.Text
    vnt(1, 8) = NewStartDate2.Text
    vnt(1, 9) = NewEndDate2.Text
    vnt(1, 10) = ReasonForSplit.Text

    With Worksheets(cSheet)
        erow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(erow, 1).Resize(, 10) = vnt
    End With

End Sub

Range Version

Option Explicit

Private Sub SplitTourCommand_Click()

    Const cSheet As String = "Splits"

    Dim erow As Long

    With Worksheets(cSheet)
        erow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .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 With

End Sub