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.Te xt,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"


End Sub

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



Steve Jones wrote:

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.Te xt,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"

End Sub

--

Dave Peterson

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
--
Pranav Vaidya
VBA Developer
PN, MH-India


"Steve Jones" wrote:

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.Te xt,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"


End Sub

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.



Pranav Vaidya wrote:

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
--
Pranav Vaidya
VBA Developer
PN, MH-India

"Steve Jones" wrote:

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.Te xt,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"


End Sub




--

Dave Peterson

Thanks very much for your help.

"Steve Jones" wrote in message
...
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.Te xt,
ClientNames!Client_Name, 2, False)

Exit Sub

Error:
txtaddr1.Text = "Error"


End Sub