-
Populating a Userform Textbox on userform open!
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!
-
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:
[VBA]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[/VBA]
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
-
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?
-
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.
[VBA]ws.Cells.range("I3").Value = wellnumber.Text[/VBA] >> sends data to the worksheet.
when you initialize the userform, the code will be:
[VBA]wellnumber.text = ws.cells.range("I3").value[/VBA]
HTH
Lincoln
-
I do appreciate the help, that worked great. The problem I was having is i was using:
[VBA]
Private Sub wellinfo_initialize()
Dim ws As Worksheet
Set ws = Worksheets("Well Information")
wellnumber.Text = ws.Cells.Range("I3").Value
End Sub
[/VBA]
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!
-
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules