How do I build a dynamic list?

L

LEWhite

I want to make it easier for my salesfoce to build a Bill Of Material. I have
a sheet that the users will not be modifing which contains a full table from
an SQL database on an MSSQL server. This is my master list of items available
to be put into the BOM. I want to take the second sheet and put in a drop
down list describing the product classes. My user will select the type of
product and this will then need to search the other sheet and populate the
list. For example, the user picks a product class of assembly. All assemblies
are listed with a part number starting with 'AS'. So I want the list to
populate with all of the AS part numbers from the other sheet. I would also
like the part number list to show the description of the part chosen. Most
people don't remember the parts by the number but by the name. So after they
select the assembly attribute the list created for them to select the part
from should show them the name of the assembly not the part number.

Could one of you please point me in the correct direction of how to do this?
I was thinking that I should place a VBA button on the sheet. After the
product class has been selected then that button could trigger the creation
of the list. I don't know how to create that list in the VBA and have it
display on the sheet as a drop down. Any and all help would be appreciated.

L.E.White
 
L

LEWhite

Thank you for the link. I am reading the site but have not been able to find
what I am looking for. In all honesty some of this has me more confused than
I thought I was before. Could you please provide a little more information on
how you would go about this? Is there a better way to do this than the VBA
button I am thinking of?

LEWhite
 
G

Guest

All that the SQL really does is build my database for me.
It is already imported as a seperate sheet. When it
imports it has not been sorted by the part number so I
can always put that in there if it will make things
easier. I understand building a named list through data
validation. The problem I am having is that I don't seem
to be able to alter the list automatically. Once I select
a product class on my quote I want the contents of the
list to change. I need a formual to say something like...

IF (product class=1) THEN list is all in range
A1:A10,000 with part number starting with 'AS', ELSE list
is all part numbers starting with 'AA'

So I need to find all of the enteries that fit the
product class and build a list out of them. That list
will then be what I want to link to the descriptions. If
I need to be using named lists I run into the problem
that the items in the list change regularly. This list
will need to be able to update based on finding all of
the matching items. I may need to search the imported
database each time but I don't know how to write that
formula. So far I have not been able to write a formula
into a list at all.

LEWhite
 
D

Dave Peterson

I put two comboboxes from the Control Toolbox toolbar (not the Forms toolbar) on
a Sheet1. I put a bunch of test data in sheet2 (A1:A500).

Then rightclick on the worksheet tab for Sheet1 and selected View Code. Paste
this into the code window:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range
Dim myPfx As String

With Worksheets("sheet2")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

Me.ComboBox2.Clear

If Me.ComboBox1.ListIndex < 0 Then
'do nothing
Else
Select Case Me.ComboBox1.ListIndex
Case Is = 0 'All Parts
myPfx = "*"
Case Is = 1 'Assembly
myPfx = "as*"
Case Is = 2 'pcb
myPfx = "fb*"
Case Is = 3 'component
myPfx = "d*"
Case Is = 4 'documentation
myPfx = "sp*"
Case Else
myPfx = "*" 'just in case
End Select
End If

For Each myCell In myRng.Cells
If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox2.AddItem myCell.Value
End If
Next myCell

End Sub

Then copy this to a General module (Insert|Module from the menubar):

Option Explicit
Public BlkProc As Boolean
Sub Auto_open()
BlkProc = True
With Worksheets("sheet1").ComboBox1
.Clear
.Style = fmStyleDropDownList
.AddItem "All Parts"
.AddItem "Assembly"
.AddItem "PCB"
.AddItem "Component"
.AddItem "Documentation"
End With
Worksheets("sheet1").ComboBox2.Clear
BlkProc = False
End Sub

When you open the workbook, the auto_open routine will run and populate
combobox1 with options.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
L

LEWhite

I wanted to say thank you. That code does solve my problem and with a little
work it will finish my spread sheet.

I really appreciate the time that both of you took in responding to my
request. The links have also provided me with a lot of information that I can
use to improve what I am already doing.

With Best Regards
LEWhite
 
Top