Modify code for vlookup

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
72
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
How would the code know which value in the listbox to use?
 

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
Listboxes are designed to hold multiple items. How would the code now which of those items you want to use?
 

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
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
 

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
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
 

Mikal

Board Regular
Joined
Aug 31, 2015
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,087
Office Version
365
Platform
Windows
How are you reloading the form?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,788
Messages
5,470,786
Members
406,721
Latest member
Laiceyshae

This Week's Hot Topics

Top