In Reverse...

S

stck2mlon

Ok, continuing on with my VBA Tutorial...

I have a row with 5 cells that have something in each one. I want t
take those 5 cels and populate 5 text boxes. I have created a userform
where do I begin to pull the information from the current worksheet
 
T

Tom Ogilvy

Private Sub Userform_Initialize()
With Worksheets("Sheet2")
Textbox1.Text = .Range("A21").Value
Textbox2.Text = .Range("B9").Value
Textbox3.Text = .Range("M32").Value
Textbox4.Text = .Range("Z1").Value
Textbox5.Text = .Range("F10").Value
End With
End Sub

Goes in the Userform code module
 
K

kkknie

Something like:

Textbox1.Text = Sheets("Sheet1").Range("A1").Value
Textbox2.Text = Sheets("Sheet1").Range("A2").Value
Textbox3.Text = Sheets("Sheet1").Range("A3").Value
Textbox4.Text = Sheets("Sheet1").Range("A4").Value
Textbox5.Text = Sheets("Sheet1").Range("A5").Value

probably placed in your UserForm_Initialize() event procedure.
 
S

stck2mlon

What if I want to pick a range of items from a combobox, example selec
company name and get their telephone number? Thanks for everything s
far
 
K

kkknie

Could you elaborate? Remember, I'm not looking at your spreadsheet...
What you ask sounds simple, but I have no idea where to start.
 
S

stck2mlon

Sorry about that...

I want to have a small userform with a dropdown that has a list of th
clients I work with. I use...

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

to get the list.

I would like it to call another userform that has five textboxe
populated with the remaining information that is in the rows calle
above...does this help
 
K

kkknie

Try something like this:

Code
-------------------
Private Sub ComboBox1_Click
UserForm2.Show
End Sub

'Second userform (UserForm2)
Private Sub UserForm_Initialize()
Dim i as Long
i = UserForm1.ComboBox1.ListIndex
TextBox1.Text = Sheets("Active Collection").Cells(i+3,5).Value
TextBox2.Text = Sheets("Active Collection").Cells(i+3,6).Value
'etc.
Next
End Su
 
S

stck2mlon

I get absolutely no response...I have the following

Private Sub cmdEnter_Click()
frmEditCollect.Show
frmLookUp.Hide
End Sub

Private Sub UserForm_Initialize()
Dim i&
With ThisWorkbook.Sheets("Active Collection")
For i& = 3 To 300
cmbClient.AddItem .Cells(i&, 4).Value
Next i&
End With
cmbClient.ListIndex = 0
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClient_DropButtonClick()

End Sub



then the next form...

Private Sub UserForm_Initialize()
Dim i As Long
i = frmLookUp.cmbClient.ListIndex
txtCompany.Text = Sheets("Active Collection").Cells(i + 3
5).Value
'Next
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub


any suggestions??
 
S

stck2mlon

Sorry again...For some reason I think you are reading my mind...

The next form shows but all textboxes are blank and the focus isn'
even on the correct text box. It's as if I am not calling the righ
form...but the correct form shows.

Does it matter if it's in a frame
 
K

kkknie

I'm gone for the day, but will check back tomorrow. Some suggestions:

Change to

txtCompany.Text = i

to see what value of i comes up and then make sure you have somethin
in that cell.

Use debug.print in your code to see what's going on.

I may try to check back in 5-6 hours, but hopefully, you will hav
solved the problem by then. I did try a quick mock-up on my machin
and got it to work, so I know we are on the right path.

K

P.S. Just read your last post. A frame does sometime complicate thing
(which is why I don't use them). Try to qualify the text box (or th
combobox if that's what you mean) with the frame name.

i.e. UserForm1.Frame1.TextBox

or try removing the frames to see if it is the issue.

P.P.S. As for focus, the focus will be on the control with the lowes
tab order unless you use TextBox1.SetFocus.
 
S

stck2mlon

Thanks for everything...I am going to plug at this for a whil
longer...again thanks for everything
 
Top