Submit User Form Contents to next available row
 

I have a user form designed for input of specific data, at the moment the
users use the Workbook "as a workbook, but I want the form to be used and
the data input to be submitted to the next free row on the sheet, how do I
do this?

I know there is a "form" in the Data menu - but I need to be able to have
the form open as soon as the workbook is opened and I can only do this with
a VBA form.

Does this make sense?


tia

Jonathan

Submit User Form Contents to next available row
 

Jonathan,

You can show the form when the workbook opens by using the
Workbook_Open event procedure. In the ThisWorkbook code module,
use the code

Private Sub Workbook_Open()
Userform1.Show
End Sub

To find the next available row, based on data in column A, use
code like

Dim Rng As Range
Set Rng = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2, 1)

Insert your data on the row referenced by the Rng variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jonathan" wrote in message
...
I have a user form designed for input of specific data, at the
moment the
users use the Workbook "as a workbook, but I want the form to
be used and
the data input to be submitted to the next free row on the
sheet, how do I
do this?

I know there is a "form" in the Data menu - but I need to be
able to have
the form open as soon as the workbook is opened and I can only
do this with
a VBA form.

Does this make sense?


tia

Jonathan

Submit User Form Contents to next available row
 

Thanks Chip,

I hate to sound dim - but I still can't work out how to write the data from
the form to the end of my data list, like I would in access.

Whats am I missing?

But this code has helpd me

tfm

Jonathan
"Chip Pearson" wrote in message
...
Jonathan,

You can show the form when the workbook opens by using the
Workbook_Open event procedure. In the ThisWorkbook code module,
use the code

Private Sub Workbook_Open()
Userform1.Show
End Sub

To find the next available row, based on data in column A, use
code like

Dim Rng As Range
Set Rng = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2, 1)

Insert your data on the row referenced by the Rng variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jonathan" wrote in message
...
I have a user form designed for input of specific data, at the
moment the
users use the Workbook "as a workbook, but I want the form to
be used and
the data input to be submitted to the next free row on the
sheet, how do I
do this?

I know there is a "form" in the Data menu - but I need to be
able to have
the form open as soon as the workbook is opened and I can only
do this with
a VBA form.

Does this make sense?


tia

Jonathan

Submit User Form Contents to next available row
 

Chip's code gives you a range reference to the cell in column A for the row
where you will write your data. Now you must write your data a cell at a
time, possibly using offset from the referenced cell.

rng.Value = Userform1.Textbox1.Text
rng.offset(0,1).Value = Userform.Textbox2.Text
rng.offset(0,2).Value = Userform.Combobox1.Value

as an example.

--
Regards,
Tom Ogilvy

jonathan wrote in message
...
Thanks Chip,

I hate to sound dim - but I still can't work out how to write the data
from
the form to the end of my data list, like I would in access.

Whats am I missing?

But this code has helpd me

tfm

Jonathan
"Chip Pearson" wrote in message
...
Jonathan,

You can show the form when the workbook opens by using the
Workbook_Open event procedure. In the ThisWorkbook code module,
use the code

Private Sub Workbook_Open()
Userform1.Show
End Sub

To find the next available row, based on data in column A, use
code like

Dim Rng As Range
Set Rng = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2, 1)

Insert your data on the row referenced by the Rng variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"jonathan" wrote in message
...
I have a user form designed for input of specific data, at the
moment the
users use the Workbook "as a workbook, but I want the form to
be used and
the data input to be submitted to the next free row on the
sheet, how do I
do this?

I know there is a "form" in the Data menu - but I need to be
able to have
the form open as soon as the workbook is opened and I can only
do this with
a VBA form.

Does this make sense?


tia

Jonathan