UserForm Data to Worksheet

[maximillan]

Hello,

I've just started using Excel VBA. I've a little background on
programming VBA and I do mean "little". Anyway, I've been looking all over
about how to transfer data from UserForm in Excel VBA to a worksheet. Problem
is, I've still not grasped the idea nor understand the codes posted here. So
if anyone can help me with this and explain as simple as possible on how to
do this, please do so.

What I did so far:
Created a command button in the worksheet with the code:
Reg_Frm.Show < Which opens the UserForm

Then designed the UserForm with the following items:
3 Text Boxes
1 Combo Box (Not bound. Used ComboBox1.additem)
1 Quit button (Functional)
1 Reset button (Functional)
1 submit button (Problem)

I intend to fill a row with the data taken from UserForm but don't know where
to start. Also, if possible, someone explain to me how it'll check if the
cell is vacant or not.

I'm a complete noob when it comes to macros just so you know. Thank you in
advance for the help. ^__^

 

[Jim Cone]

You can use most worksheet functions in code by using 'Application.' in front
of them... Application.CountA(Range("A5:G5")
The above will tell you if there is anything entered in A5:G5 on the ActiveSheet.

To get text from a Text Box onto a worksheet use the value property of both...
Range("B5").Value = TextBox1.Value
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"maximillan" <u25565@uwe>
wrote in message
Hello,
I've just started using Excel VBA. I've a little background on
programming VBA and I do mean "little". Anyway, I've been looking all over
about how to transfer data from UserForm in Excel VBA to a worksheet. Problem
is, I've still not grasped the idea nor understand the codes posted here. So
if anyone can help me with this and explain as simple as possible on how to
do this, please do so.

What I did so far:
Created a command button in the worksheet with the code:
Reg_Frm.Show < Which opens the UserForm

Then designed the UserForm with the following items:
3 Text Boxes
1 Combo Box (Not bound. Used ComboBox1.additem)
1 Quit button (Functional)
1 Reset button (Functional)
1 submit button (Problem)

I intend to fill a row with the data taken from UserForm but don't know where
to start. Also, if possible, someone explain to me how it'll check if the
cell is vacant or not.

I'm a complete noob when it comes to macros just so you know. Thank you in
advance for the help. ^__^

 

[Guest]

XL97: How to Use a UserForm for Entering Data (Q161514)
http://support.microsoft.com/?id=161514

XL2000: How to Use a UserForm for Entering Data (Q213749)
http://support.microsoft.com/?id=213749

http://j-walk.com/ss/excel/tips/tip84.htm


http://www.dicks-blog.com/excel/2004/09/linking_userfor.html

 

[moon]

More about properties...
Start thinking in objects when you build something like this.
So your worksheet is an object, in the workbook object. The button is an
object on your worksheet, etc.
When programming, it is the best to use objects instead of full
workbooknames or sheetnames.
After you clicked the button, the form shows up, so far so good.

Remember, on the form object, there are textbox objects, combobox objects,
etc.
To transfer data from your form to your worksheet, you should transfer data
from one object to the other.
Let's do this after a button on the form called 'buttonPopulate' is clicked.

Private Sub buttonPopulate_Clicked()

'Declare object variabeles
Dim wb as WorkBook
Dim ws As WorkSheet

Set wb = ThisWorkBook
Set ws = wb.Sheets("Sheet1")

'Now I can say ws.Cells(1,1) for A1, instead
'of 'ThisWorkbook.Sheets("Sheet1").Cells(1,1)
'so writing in objects is way shorter

'Activate the worksheet to enter form data
ws.Activate

'Transfer form data...
ws.Cells(1,1).Value = Me.TextBox1.Text

'...will put the text in TextBox1 in cell A1
'Me is the form object, Text is a so called property of the TextBox object

'This way you can walk through all your controls
'and move data to any cell address you want

'If you're done, always clean up your objects
Set ws = Nothing
Set wb = Nothing

End Sub

 

[maximillan via OfficeKB.com]

Cool tips. So far I've been able to transfer data from UserForm to the
worksheet. What I haven't figured out is how it would move to the next line.
Somehow, it just replaces the existing data on the same row. Can anyone shed
light on this? Thanks again! You guys are great!

 

[moon]

For your rows and columns, you can declare integer variables:

Dim r, c As Integer
r = 1
c = 1
Cells( r, c ).Select 'Row 1, Column 1 --> A1
<Enter your value now>

r = r + 1 'Next Row
Cells( r, c ).Select
<Enter your next value now>

And if you want to move a column to the right:
c = c + 1
Cells( r, c ).Select

It's the best to code this way of moving to the next column or row in a
Loop.

For c = 1 To 5
Cells( r, c ).Select
<Enter data>
Next c

 

[maximillan via OfficeKB.com]

Sorry for the late reply. Thank you to all who helped me with this little
problem. I had trouble with the next row thing and followed all your advice.
I've also experimented with this. I found out that the code will check if the
cell above it is blank or not. So I found a solution involving the count
formula. Again, thanks to all of your advice. More power to OfficeKB and all
the members.