vba help...picking values from list

[hov]

I have a list of part numbers on a worksheet, with coresponding columns that say how the part is made...i.e. use steel A with steel B.

how would i create a drop down list that when a user selects a part, in the corespoinding cells next to it it displays how the part is made.

 

[Marcelo Branco]

Hi and welcome

Maybe this can help

http://www.contextures.com/xlfunctions02.html

M.

 

[hov]

Awesome...this helped me alot. Another question, how would i make a button that when clicked it would open a file based upon the number picked from the drop down list?

 

[gmazza76]

Afternoon,

Possibly something like if you are running the Macro from a Module

dim partno as string
Dim ws As Worksheet
Dim wbook As Workbook
 
partno = Sheets("name of sheet").Cells(cell ref).Value
 
If partno = ("cell ref") Then
 
 
 
Set wbook = Workbooks.Open(Filename:="place of file to open")
 
If wbook.ReadOnly Then
        MsgBox "This file is being used by someone else please try again in a minute"
        wbook.Close False
    Else 'make sure destination file is shared.

The above should open a file of your chose depending on the cell reference. that is entered. this can be a bit pain depending on how many different parts you may need to search.

I do have a file you can look at that will allow you to automatically write the information to a sheet in a workbook as long as it is named the same way as the cell that holds the part name.

 

[gmazza76]

I can send you a file that may help you if you want.

It in essence works on the cell reference ( box name on a userform ) that you want to search by. You would need to have your destination file saved as a shared file so more than 1 person can save to it at a time.

Cheers
Gavin

 

[hov]

thanks for the help.

on my listbox, my macro assigned to it doesnt show all the part numbers i have. i cant figure it out.

i wanted to have the button linked to the value selected in the list box. would that be possible or should i have that listbox value spit out to another cell?