How do I use VLOOKUP within a User Form in my VBA code.

Hi All,

I have a simple User form which comprises the following form controls:
a) Customer name (Combo box)
b) Customer address1 (Text box)
c) Customer address2  (Text box)
d) Customer address3 (Text box)
e) Town/City (Text box)
f) Postcode (Text box)

I want an event whereby selecting a customer from the Combo box dropdown list automatically populates the text box controls beneath.

I've tried using Vlookup against my Customer sheet but keep getting compilation errors. Is there a standard code that would fit this requirement ?

Thanks
Toco
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
1. what error do you get ?
2. can u post sample of your code ?
3. you are using
result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)

Open in new window

?  vlookup is not a vba function.
0
TocogroupAuthor Commented:
I was using the following example ......

Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim col As Integer
Dim found As Variant

' teCustomerName is the name of a text box control on the User form. It is the following line that I get a compilation error.......

Set lookFor = teCustomerName

Set rng = Sheets("Customers").Columns("A:F")
col = 6

On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, col, 0)
If IsError(found) Then
MsgBox lookFor & " not found"
Else: MsgBox "The look-up value of " & lookFor & " is " & found & " in column " & col
End If
On Error GoTo 0
End Sub
0
Robberbaron (robr)Commented:
the textbox is just that , a textbox object and you re trying to assign it to a range.

you are looking for the Text property of the text box.

set lookFor = activesheet.range( teCustomerName.Text)
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

TocogroupAuthor Commented:
Sorry I misled you and didn't proofread my own post.

The CustomerName control is a Combo box. I have now renamed it to reflect the type of object it is - coClientName.

Attached is my code as it currently stands with worksheet names and definitions, and the latest error message (also attached).
Code-in-error-2.jpg
Error-message-2.jpg
0
Robberbaron (robr)Commented:
1. put a breakpoint on the line with error and find out what the value of coClientName.Text is.

2. depending on how you setup the combo box, you may need to read the .Value property, or use .ListIndex to get the selected item.
http://www.get-digital-help.com/2011/12/21/working-with-combo-boxes-form-control-using-vba/#read
0
TocogroupAuthor Commented:
The value of coClientName.Text at the breakpoint is the name of the client, as expected.

Why is 'lookfor' defined as a Range ? I would have thought that it should have been the lookup value.

It's falling over at the line...

Set lookFor = wsSessions.Range(coClientName.Text)

so it's not even getting to the point of doing the lookup.
0
Robberbaron (robr)Commented:
1/ the code line in question is trying to create a reference to the range defined by coclientName.Text   if this is xyzcorp  then you need a 'named range' that matches.

i thought originally you were entering a range address such as B17

2/ so yes, the use of a range doesnt match how you are using it as the lookup is indeed a value.

found = Application.Vlookup(coClientName.Text, rng, col, 0)

Open in new window

0
TocogroupAuthor Commented:
Yes, that's it ! It returned the value determined by the col variable.

Is it possible to incorporate this in a loop to return the other fields, or do I have to repeat the  block of code for each one  :

Customer address1 (Text box)
Customer address2  (Text box)
Customer address3 (Text box)
Town/City (Text box)
Postcode (Text box)

The values appear in consecutive columns on the Clients sheet.
0
Robberbaron (robr)Commented:
without seeing your process, i think that you will need the code block.

that is, each 'Find' is separate.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Many thanks for your patience. It works fine now.
Kind regards
Toco
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.