Vlookup in Userform - XL2003

[Steve Jones]

I have a combobox on a userform.

I would like when user selects a Client Name from the combo box the Address
appears in txtAddr1, Addr2,Addr3 .......

I have come up with the code below but clearly I have made a mistake
somewhere.

Thanks very much in advance.

Steve


Private Sub cboClient_Change()

On Error GoTo Error

txtaddr1.Text = Application.WorksheetFunction.VLookup(cboClient.Text,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"


End Sub

 

[Dave Peterson]

txtaddr1.Text = Application.WorksheetFunction.VLookup(cboClient.Text, _
worksheets("ClientNames").range("Client_Name"), 2, False)

 

[Guest]

you have to make 2 changes here.
First of all change the name of label from Error to something else. You are
using a keyword.

Secondly, declare a variable of variant type and store the result of vlookup
in this variable and later set the text box's text with this variable. Thsi
is because if your vlookup fails, it will return FALSE which you can not
store in text box.

hope this helps

 

[Dave Peterson]

I think your first point is very good.

I don't understand about the False stuff, though.

But the way the OP wrote the code (using .worksheetfunction), a mismatch would
cause a runtime error that he avoids with the "on error goto..." line.

 

[Steve Jones]

Thanks very much for your help.