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...

 

[Andrew Poulsom]

Welcome to the Board.

Here's an example:

http://www.contextures.com/xlUserForm01.html

 

[VoG]

Hello and welcome to MrExcel.

There is some sample code here http://www.contextures.com/xlUserForm01.html

 

[Loin75]

Thank you very much, and thanks for the great link. This should do the trick.

I would like to ask though, to help me understand what it is I am typing...

What do these settings mean? What are they doing exactly?

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

Thanks Again...

 

[VoG]

This may help

Dim iRow As Long ' define iRow as a long integer
Dim ws As Worksheet ' define ws as a worksheet
Set ws = Worksheets("PartsData") 'assign ws - we can now use ws to refer to the sheet

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

 

[Loin75]

I see. So if I wasn't using a seperate worksheet like in the example, do I still need to define the sheet?

 

[VoG]

If you don't define the sheet and you use for example

Cells(iRow, 1).Value = Me.txtPart.Value

the data will be written to whichever sheet is selected when the code runs. This is fine if you are sure that the selected sheet will always be the correct one.

 

[Loin75]

There will only ever be one sheet. Thats perfect.

Thanks very much for your help...

 

[Loin75]

Hi VoG,

Sorry to go on, but i've been playing with this for the last hour, but can't get the offset to start in cell B5. No matter what I do, it always wants to populate column A.

iRow = Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 5).Row

Sorry for being lame..

 

[VoG]

This will give you the first free (empty) row in column B

iRow = Cells(Rows.Count, 2).End(xlUp).Offset(1).Row