• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6020
  • Last Modified:

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 ?

  • 5
  • 5
1 Solution
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.
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
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)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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).
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.
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.
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

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.
Robberbaron (robr)Commented:
without seeing your process, i think that you will need the code block.

that is, each 'Find' is separate.
TocogroupAuthor Commented:
Many thanks for your patience. It works fine now.
Kind regards
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now