VBA to capture data from User Form

[Mattyads2011]

I have creatoed a userform which I am intending for users to input data and I need this data to the be captured onto a Excel Spreadsheet in vasrious coloumns, populating new rows with each new entry.

Haven't got the first idea where to start, can anyone please help me?


I am using Excel 2003.

Thank you.

 

[Norie]

How do you want this to happen?

For the user to click a button once they've finished entering data?

Perhaps something like this?

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Next As Range
 
      Set ws = Worksheets("Data") ' change for the worksheet you want data to go to
 
      ' the following will find the next empty row
      ' it uses column A, change that if needed
 
      Set rngNext ws.Range("A" & Rows.Count).End(xlup).Offset(1)
 
      ' now put the data from the textboxes in columns A-D
 
       rngNext.Value = TextBox1.Value ' column A
 
       rngNext.Offset(,1).Value = TextBox2.Value ' column B
 
       rngNext.Offset(,2).Value = TextBox3.Value ' column C
 
       rngNext.Offset(,3).Value = TextBox4.Value ' column D
 
       ' unload and reshow form for further input - change UserForm1 if
       ' your form name is different
 
       Unload Me 
 
       UserForm1.Show
 
End Sub

That's a very simple example which should give you a start.

Post back if you need more help.

 

[Mattyads2011]

Sorry never came back onto the site to check for a reply!

Thank you very much though, this helped a lot !

 

[trixam]

How do you want this to happen?

For the user to click a button once they've finished entering data?

Perhaps something like this?

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Next As Range
 
      Set ws = Worksheets("Data") ' change for the worksheet you want data to go to
 
      ' the following will find the next empty row
      ' it uses column A, change that if needed
 
      Set rngNext ws.Range("A" & Rows.Count).End(xlup).Offset(1)
 
      ' now put the data from the textboxes in columns A-D
 
       rngNext.Value = TextBox1.Value ' column A
 
       rngNext.Offset(,1).Value = TextBox2.Value ' column B
 
       rngNext.Offset(,2).Value = TextBox3.Value ' column C
 
       rngNext.Offset(,3).Value = TextBox4.Value ' column D
 
       ' unload and reshow form for further input - change UserForm1 if
       ' your form name is different
 
       Unload Me 
 
       UserForm1.Show
 
End Sub

That's a very simple example which should give you a start.

Post back if you need more help.

I think 'Dim Next As Range' should be 'Dim rngNext As Range'.

 

[Norie]

It doesn't actually matter - there's no Option Explicit.

What does matter is the missing = in the line that sets the range reference.