user forms and vlookup issue

grapevine

Board Regular
Joined
May 23, 2007
Messages
208
I have a user form which has a combo box looking at data on another sheet. This is working correctly.
I have a text box alongside which I want to auto populate from data in column U based on the selection in column A selected in the combo box
I have given assigned the named range of tblTakeons to the data on the sheet but I cannot get my vlookup to work. I get runtime error 1004 Application-defined or object-defined error which I knows means it does not recognised the commands I am giving it, but I have looked at lots of previous posts and I cannot see what I am doing wrong. when I hover over the cboProperty.value I see the value selected in the combo box, but the problem appears to be finding the table of data to lookup the price from.
Any help would be greatly appreciated.

[code
Private Sub cboProperty_change()
If cboProperty <> "" Then
txtCurrentPrice.Value = WorksheetFunction.VLookup(cboProperty.Value, Worksheets("Team Vals - Take ons").Range(tblTakeons), 21, False)

End If
End Sub

[/code]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Does this work?
Code:
Private Sub cboProperty_change()
   If cboProperty.ListIndex <> -1 Then
       txtCurrentPrice.Value = Worksheets("Team Vals - Take ons").Range(tblTakeons).Cells(cboProperty.Index+2, 21).Value
   End If
End Sub
PS What is tblTakeons? If it's a named range you'll need to use Range("tblTakeons").
 
Upvote 0
Many thanks for your quick reply, almost there I hope,
The error now comes up with Index highlighted and the message Method or Data Member not found. tblTakeons is a named range and I tried the code with the quotes and without and still received the same error message.
 
Upvote 0
Oops, it should be ListIndex.
Rich (BB code):
 txtCurrentPrice.Value = Worksheets("Team Vals - Take ons").Range(tblTakeons).Cells(cboProperty.ListIndex+2, 21).Value
 
Upvote 0
Many many thanks, it is now working correctly
However, I don't understand why the -1 and then +2 used in the index, can you explain so I can hopefully use this again in other examples.
 
Upvote 0
ListIndex tells you the position of the item selected in the combobox, if it's -1 then nothing has been selected.

So this is checking that something has actually been selected.
Code:
If cboProperty.ListIndex <> -1 Then

The +2 was kind of a guess, ListIndex is 0-indexed which means the ListIndex of the first item is 0, for the second item it's 1, the third 2 and so on

So to find the actual row in the data we need to add 1, and if there's a header we need to take account of we need to add 2.

Hope that makes some kind of sense.
 
Upvote 0
Thank you for that explanation. I will double check the lookup results as I have a few duplicates in the columns so will double check to make sure it is picking up the data in the correct row and not the one above or below.
Once again many thanks, you have been a great help.
Enjoy the rest of the day - lovely and sunny in Suffolk, hope it is in Stirling!
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top