PDA

View Full Version : Populating a Userform Textbox on userform open!



mdmorgan
04-30-2010, 10:21 PM
Hi,

I am in need of assistance. I have a Userform named "Wellinfo". On this form I have 10 Textboxes that a user can type information into, then whent hey click okay it populates the corisponding cells in the worksheet.

The problem I am having is that I need the textboxes to pull the information back from the cells when the userform is opened a second time. The userform opens up just fine but the textboxes do not grab the information from cells. However the information goes to them just fine by writing a simple code like this:

Private Sub cmdOK_Click()
Dim ws As Worksheet
Set ws = Worksheets("Well Information")
ws.Cells.range("I3").Value = wellnumber.Text
ws.Cells.range("I4").Value = billingnumber.Text
ws.Cells.range("I5").Value = fieldname.Text
ws.Cells.range("I6").Value = county.Text
ws.Cells.range("I7").Value = state.Text
ws.Cells.range("I9").Value = basemeridian.Text
ws.Cells.range("I11").Value = spuddate.Text
ws.Cells.range("C3").Value = operator.Text
ws.Cells.range("c4").Value = operatorrep1.Text
ws.Cells.range("c5").Value = operatorrep2.Text
ws.Cells.range("c13").Value = rignumber.Text
ws.Cells.range("C14").Value = rigrep1.Text
ws.Cells.range("c15").Value = rigrep2.Text
ws.Cells.range("I8").Value = section & "/" & township & "/" & range
Unload Me

End Sub

What code do I need to use to have the userform pull the information back out of the cells and populate their textboxes in the userform. Any help would be greatly appreciated! Having trouble finding the answer elsewhere!

lynnnow
04-30-2010, 10:54 PM
Morgan,

You will need to initialize the userform with the data from your worksheet.

The following should be your code considering you are in the first cell of your row with data:

Private Sub userform_initialize()
textbox1.value = Activecell.value
textbox2.value = activecell.offset(0,1).value
'and so on to populate all your textboxes with the values from the worksheet.
End Sub

Also, please explain "wellnumber.Text". are these the names you have given for the textboxes? If these are names for the textboxes, make sure you use the precise name of the textbox to enter the value from the worksheet. Otherwise it will not work.

HTH

Lincoln

mdmorgan
05-01-2010, 04:08 AM
Thank you for the quick response.

I guess I am just to new at VBA. Just a little confused on the whole Initialize procedure.

On one of my worksheets I have a button that opens the userform.

Private Sub CommandButton1_Click()
wellinfo.MultiPage1.Value = 0
wellinfo.Show
End Sub

On the userform named "wellinfo" I have the textboxes that are named as follows: ("wellnumber", "billingnumber", "fieldname", "county", "state", "section", "township", "range", "basemeridian")

So to get the information to the worksheet named "Well Information" I use the following procedure, also listed in first post. This is embeded on the OKAY button on Userform itself.

Private Sub cmdOK_Click()
Dim ws As Worksheet
Set ws = Worksheets("Well Information")
ws.Cells.range("I3").Value = wellnumber.Text
ws.Cells.range("I4").Value = billingnumber.Text
ws.Cells.range("I5").Value = fieldname.Text
ws.Cells.range("I6").Value = county.Text
ws.Cells.range("I7").Value = state.Text
ws.Cells.range("I9").Value = basemeridian.Text
ws.Cells.range("I11").Value = spuddate.Text
ws.Cells.range("C3").Value = operator.Text
ws.Cells.range("c4").Value = operatorrep1.Text
ws.Cells.range("c5").Value = operatorrep2.Text
ws.Cells.range("c13").Value = rignumber.Text
ws.Cells.range("C14").Value = rigrep1.Text
ws.Cells.range("c15").Value = rigrep2.Text
ws.Cells.range("I8").Value = section & "/" & township & "/" & range
Unload Me

End Sub

So with that said, do I want to add the initialize command in the same module as the commandbutton1_click, or do I need a new one. Or do I put this in the userform code?

lynnnow
05-01-2010, 06:02 AM
You need to put it in the userform module and the process to insert data into the userform will be the reverse of putting it on the worksheet.

For e.g.
ws.Cells.range("I3").Value = wellnumber.Text >> sends data to the worksheet.

when you initialize the userform, the code will be:

wellnumber.text = ws.cells.range("I3").value

HTH

Lincoln

mdmorgan
05-01-2010, 08:24 AM
I do appreciate the help, that worked great. The problem I was having is i was using:



Private Sub wellinfo_initialize()
Dim ws As Worksheet
Set ws = Worksheets("Well Information")
wellnumber.Text = ws.Cells.Range("I3").Value
End Sub


I thought that Private Sub Userform_Initialize() was just a common name for a person's userform. Thought I needed to insert my userform name their instead. Man, I have been trying to figure this out on my own for about a week now, and you do it in 5 minutes. Thanks a lot!

mdmackillop
05-01-2010, 12:12 PM
Another MD!
Welcome to VBAX
"Figuring out" is where you really learn, and it's always best to post your own code attempts to find where things go wrong.
BTW, when you post code, select it and click the green VBA button to format it as shown.
Regards
MD