Modify code for vlookup

[Mikal]

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

 

[Fluff]

How would the code know which value in the listbox to use?

 

[Mikal]

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.

 

[Fluff]

Listboxes are designed to hold multiple items. How would the code now which of those items you want to use?

 

[Mikal]

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

 

[Fluff]

It sounds as though you are a ComboBox rather than a ListBox.
If that's the case try

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

 

[Mikal]

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?

 

[Fluff]

Is this what you mean

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

 

[Mikal]

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.

 

[Fluff]

How are you reloading the form?