Avatar of Tocogroup
TocogroupFlag for United Kingdom of Great Britain and Northern Ireland asked on

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 ?

Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Robberbaron (robr)

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.

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
Robberbaron (robr)

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)
Your help has saved me hundreds of hours of internet surfing.

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).
Robberbaron (robr)

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.

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robberbaron (robr)

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


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.
Robberbaron (robr)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Many thanks for your patience. It works fine now.
Kind regards
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes