UserForm outputs to cells in a series of rows

[aaronlive]

I wonder if anyone can help me. I've created a userform with cascading comboboxes and other fields and when completed the userform populates cells B3:H3. The problem that I'm running into is that I need the form to populate the next row with the next entry of information. For example, the first time the user enters information, the userform populates B3:H3. The second time information is entered it populates B4:H4 and so on. Additionally, if the user clicks the command button "Cancel" the row of cells it currently populates should be cleared and the sub should not proceed to the next row.

Any help would be greatly appreciated! I've copied the userform code below as a reference.



Private Sub Calendar1_Click()
Range("B3").Value = Calendar1.Value

End Sub

Private Sub EntryBox1_Change()
If EntryBox1.Value = "" Then
EntryBox2.Clear
ElseIf EntryBox1.Value = "Income" Then
EntryBox2.RowSource = "Categories!F2:F6"
ElseIf EntryBox1.Value = "Costs" Then
EntryBox2.RowSource = "Categories!G2:G16"
Range("H3").Font.ColorIndex = 3
ElseIf EntryBox1.Value = "Transfer Out" Then
EntryBox2.RowSource = "Categories!A103"
Range("H3").Font.ColorIndex = 3
ElseIf EntryBox1.Value = "Transfer In" Then
EntryBox2.RowSource = "Categories!A104"
End If
End Sub

Private Sub CommandButton1_Click()
EntryJan001.Hide

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub Label2_Click()

End Sub

Private Sub UserForm_Click()

End Sub

 

[crimson_b1ade]

you need to use a variable for your row. example:

Private Sub Calendar1_Click()

Dim LastRow As Long
 
LastRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
 
Sheets("Sheet1").Range("B" & LastRow).Value = Calendar1.Value

End Sub

I'm not clear on the mechanics of your "cancel" button but it prolly would also involve looking up the lastrow with data as shown above then clearing it.

 

[Norie]

If you want to find the next blank row to enter data into try something like this.

NextRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row+1

Which can then be used like this.

Sheets("Sheet1").Range("B" & NextRow) = EntryBox1.Value

Mind you I think perhaps it's time you explained in words what you are trying to do.

Why do you appear to be populating a combobox with a single value?

I would also recommend not using If ElseIf...

Perhaps something like this.

Private Sub Calendar1_Click()
Dim NextRow As Long
 
    NextRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & NextRow).Value = Calendar1.Value
End Sub
 
Private Sub EntryBox1_Change()
 
    Select Case EntryBox1.Value
        Case ""
            EntryBox2.Clear
        Case "Income"
            EntryBox2.RowSource = "Categories!F2:F6"
        Case "Costs"
            EntryBox2.RowSource = "Categories!G2:G16"
            Range("H3").Font.ColorIndex = 3
        Case "Transfer Out"
            EntryBox2.RowSource = "Categories!A103"
            Range("H3").Font.ColorIndex = 3
        Case "Transfer In"
            EntryBox2.RowSource = "Categories!A104"
    End Select
 
End Sub
 
Private Sub CommandButton1_Click()
    Unload Me
End Sub

As for the Cancel button, I'm as confused as Crimson Blade.

One thing I can think of is don't use something like the Click event to place data on the worksheet.

As well as a Cancel button you could have an Enter/Commit button.

Using it's click event you could validate what the user has entered and if it's ok transfer the data to the worksheet.

If it isn't ok you can notify the user and allow them to change/add things as needed.

This is one way to validate data but there are others, eg using the exit event for the controls.

Using that method you could prevent the user even moving from one control to another until they've entered the required/appropriate data.

 

[aaronlive]

Thanks for all the suggestions! I guess I should be a little more clear. I have a user form with 3 cascading comboboxes, 1 independent combobox, 1 calendar, and 2 textboxes. Each of these fields is populated by the user and the data are recorded in a row of cells B to H. Is there an elegant way to move all the data one row down after input or do I need to do the next row for each field?

I like the idea of collecting user input and then sending the data to the cells when the "ok" command button is clicked. I've been using the control source property to send information to the associated cells, so I'll probably have to change that.

 

[Norie]

Don't use ControlSource, have you tried any of the suggestions?

In particular the way to find the next blank row for data entry.

I'm also a little confused by 'move all the data one row down', what do you mean by that?

Can you explain in words what you are trying to do?

It's good to know what controls you have but it would also be good to know exactly what you are trying to achieve.

Why do you have 4 comboboxes on the userform?

Are you sure comboboxes are the right things to use?

What about listboxes, checkboxes, option buttons etc?