Modify code for vlookup

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
73
I have a code that loads a value into 2 text boxes when you choose a part # in t lit box. I would like to modify the code so that it sees the part #in the list box and loads the text box, instead of upon selection of the part #. Can someone shoe me how to make the modification?

Private Sub In_Material_Click()
Dim f As Range
Set f = Sheets("Materials").Range("a:a").Find(In_Material.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
In_CostPerUnit.Value = Sheets("Materials").Cells(f.Row, "d").Value
In_Units.Value = Sheets("Materials").Cells(f.Row, "e").Value
End If
End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How would the code know which value in the listbox to use?
 
Upvote 0
once the value has been selected and it is in the list box it would recognize it? Currently it does it upon the click to choose it, I am hoping there is a way it can just see what is there. Right now I can change the value in the list box and the text box does not change because I did not select it.
 
Upvote 0
Listboxes are designed to hold multiple items. How would the code now which of those items you want to use?
 
Upvote 0
When I open the user form the list box is blank, I then use the drop down arrow to choose from the list. One it is chosen, I want the code to look at what has been chosen. Right now if I type in the list box it pulls up the values in order. If it happens to be on the one I am looking for the value in the text box does not change (even if I click on it in the list). The value in the text box only changes when I click on it in the list "and it is new" if it has already populated the list box during the search it does not change the value in the text box. What I need to accomplish is the textbox value being correct no matter how the list box as populated.If there is a corresponding value it matches the list box or returns "0".
 
Upvote 0
It sounds as though you are a ComboBox rather than a ListBox.
If that's the case try
Code:
Private Sub In_Material_Change()
Dim f As Range
Set f = Sheets("Materials").Range("a:a").Find(In_Material.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
In_CostPerUnit.Value = Sheets("Materials").Cells(f.Row, "d").Value
In_Units.Value = Sheets("Materials").Cells(f.Row, "e").Value
End If
End Sub
 
Upvote 0
Thank you so much for your help. You are correct it is a combo box not a list box. This code works great! However once applying it I realize that I need it to do what it is doing now, but also to double check it wen the for is opened. If for some reason someone has typed over the value I need it to correct itself. Is there something that an be added t the code?
 
Upvote 0
Is this what you mean
Code:
Private Sub In_Material_Change()
Dim f As Range
Set f = Sheets("Materials").Range("a:a").Find(In_Material.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
   In_CostPerUnit.Value = Sheets("Materials").Cells(f.Row, "d").Value
   In_Units.Value = Sheets("Materials").Cells(f.Row, "e").Value
Else
   In_CostPerUnit.Value = ""
   In_Units.Value = ""
End If
End Sub
 
Upvote 0
No that doesn't seem to be it. Right now the user form loads the info to the spread sheet. When you open the user form it pulls all of the info from the spreadsheet back into the user form so that it can be modified etc.When I create a new part the code you have provided pulls in the info perfectly. However when I am doing an update, if I have changed the values that it is pulling in, it does not update to the new value when the form reloads. So I need it to do what it is currently doing, plus to update the values if they have changed when the form reloads. Sorry if I was unclear, I'm pretty new at this.
 
Upvote 0
How are you reloading the form?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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