Populate cells from Macro Textbox

[Loin75]

Hi, I have searched everywhere for what I think is very simple, but cannot find what I need.

Stage 1.

I have created a userform that simply has 4 textboxes on it. I would like the contents of the textboxes to populate a table in the worksheet.

Stage 2.

If the macro (userform) is used again, the data would appear on the next line of the table (rather than overwriting the first entry).

I am using Excel 2007. Is this possible?

Many Thanks for any help...

 

[Loin75]

Thanks, but that code is doing exactly the same thing.

Does this mean I have something else going on that causing this code to be ignored?

This is my code at present: (taken from the tutorial you gave me)

I wanted to learn the offset, so moved my table down one row, and right one column. (thought that it would be a straight forward test... )

Private Sub CommandButton1_Click()
Add.Hide

Dim iRow As Long

'find first empty row in database
iRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

'copy the data to the database
Cells(iRow, 1).Value = Me.TextBox1.Value
Cells(iRow, 2).Value = Me.TextBox2.Value
Cells(iRow, 3).Value = Me.TextBox3.Value
Cells(iRow, 4).Value = Me.TextBox4.Value

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox1.SetFocus
End Sub

Private Sub CommandButton2_Click()
Add.Hide
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox1.SetFocus
End Sub

 

[VoG]

That should be putting the data in columns A to D. Is it? Where do you want it to be written?

 

[Loin75]

After moving it one down and one right, the first available cell becomes B3 (including header row). So I would like cells B3-E3 popuating please.

Adjusting the original code by 1 and 1 didn't work as expected..
Thanks

 

[VoG]

Try this

Private Sub CommandButton1_Click()
Add.Hide

Dim iRow As Long

'find first empty row in database
iRow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row

'copy the data to the database
Cells(iRow, 2).Value = Me.TextBox1.Value
Cells(iRow, 3).Value = Me.TextBox2.Value
Cells(iRow, 4).Value = Me.TextBox3.Value
Cells(iRow, 5).Value = Me.TextBox4.Value

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox1.SetFocus
End Sub

 

[Loin75]

Awesome!! Thanks VoG

I think I understand what you did to fix it...

 

[VoG]

You might find it easier to use Range rather than Cells notation:

'find first empty row in database
iRow = Range("B" & Rows.Count).End(xlUp).Offset(1).Row

'copy the data to the database
Range("B" & iRow).Value = Me.TextBox1.Value
Range("C" & iRow).Value = Me.TextBox2.Value
Range("D" & iRow).Value = Me.TextBox3.Value
Range("E" & iRow).Value = Me.TextBox4.Value

 

[Loin75]

That works good aswell.

This might be a dumb question, but say I popluated 10 rows of info using this tool, and then deleted the info for row3 (leaving the row blank)... This tool isn't actually finding "the next empty row" becasue row 3 doesn't get filled back up the next time I add data...

Would this involve complex programming? bearing in mind that this easy task was already complicated for me...

 

[VoG]

If B1 and B2 are populated (with headings for example) try this method to get the first empty row

iRow = Range("B1").End(xlDown).Offset(1).Row

 

[Loin75]

Thanks, but didn't work. It just keeps overwriting the first row every time.

This isn't essential in the grand scheme of things, but more of a "nice to have" if it is at all possible....

 

[VoG]

That's odd. With data in B1 and B2 then some blanks then more data, where does the cursor end up when you run this

Sub test()
Range("B1").End(xlDown).Offset(1).Select
End Sub

This code goes in a regular module: in the code window select Module from the Insert menu and paste in the code. Then press ALT + Q to close the code window, then Tools > Macro > Macros, click on test then click the Run button.