L
LWhite
Hello everyone,
I have imported an MSSQL database table into my Excel spreadsheet. I
then placed four combo boxes on the first sheet. One of them allows you
to browse a column from the database sheet and select a cell. I want
the others to follow the first and show the matching cell two columns
over.
Example: My table has ten rows of four columns. The four columns are
part name, part number, price and expiration date. The first combo box
is a drop down list of all part numbers that start with the letter E.
This is only four of the ten rows. When a user pick the part number I
want the next box to show the part name which is one cell left on the
same row.
How do I do this or is there a better way than to use a second combo
box? Can it be done with some other item easier and if so how. Below is
the code running my combo boxes.
### CODE ###
Private Sub ComboBox1_Change()
Dim myRng As Range
Dim myCell As Range
Dim myPfx As String
With Worksheets("SQL")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.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 'Standard Part
myPfx = "aa*"
Case Is = 3 'Adhesives
myPfx = "ca*"
Case Is = 4 'Bagging
myPfx = "cb*"
Case Is = 5 'Core
myPfx = "cc*"
Case Is = 6 'Fabric
myPfx = "cf*"
Case Is = 7 'Mold Release
myPfx = "cm*"
Case Is = 8 'Prepreg
myPfx = "cp*"
Case Is = 9 'Resin
myPfx = "cr*"
Case Is = 10 'Thickner
myPfx = "ct*"
Case Is = 11 'Abrasives
myPfx = "fa*"
Case Is = 12 'Paint
myPfx = "fp*"
Case Is = 13 'Hardware
myPfx = "h0*"
Case Is = 14 'Tooling Material
myPfx = "tm*"
Case Is = 15 'Maint. Equipment
myPfx = "me*"
Case Is = 16 'Maint. Building
myPfx = "mb*"
Case Is = 17 'Supplies Manufacturing
myPfx = "sm*"
Case Is = 18 'Supplies Cleaning
myPfx = "sc*"
Case Is = 19 'Supplied Office
myPfx = "so*"
Case Is = 20 'Supplies Packaging
myPfx = "sp*"
Case Is = 21 'Supplies Safety
myPfx = "ss*"
Case Is = 22 'Mining
myPfx = "10*"
Case Is = 23 'Glass Fixturing
myPfx = "20*"
Case Is = 24 'Auto Racing
myPfx = "60*"
Case Is = 25 'Aircraft
myPfx = "90*"
Case Is = 26 'HANS
myPfx = "hans*"
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.Offset(0, 3)
End If
If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox3.AddItem myCell.Offset(0, 0)
End If
If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox4.AddItem myCell.Offset(0, 5)
End If
Next myCell
'
End Sub
###
The combo box 1 is what sorts out the data by type. Combo box 2 allows
me to then select from a reduced list of parts by the part name. I want
boxes 3 and 4 to display based on the results found in 2 but don't know
how to code it.
Thanks
LWhite
I have imported an MSSQL database table into my Excel spreadsheet. I
then placed four combo boxes on the first sheet. One of them allows you
to browse a column from the database sheet and select a cell. I want
the others to follow the first and show the matching cell two columns
over.
Example: My table has ten rows of four columns. The four columns are
part name, part number, price and expiration date. The first combo box
is a drop down list of all part numbers that start with the letter E.
This is only four of the ten rows. When a user pick the part number I
want the next box to show the part name which is one cell left on the
same row.
How do I do this or is there a better way than to use a second combo
box? Can it be done with some other item easier and if so how. Below is
the code running my combo boxes.
### CODE ###
Private Sub ComboBox1_Change()
Dim myRng As Range
Dim myCell As Range
Dim myPfx As String
With Worksheets("SQL")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
Me.ComboBox2.Clear
Me.ComboBox3.Clear
Me.ComboBox4.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 'Standard Part
myPfx = "aa*"
Case Is = 3 'Adhesives
myPfx = "ca*"
Case Is = 4 'Bagging
myPfx = "cb*"
Case Is = 5 'Core
myPfx = "cc*"
Case Is = 6 'Fabric
myPfx = "cf*"
Case Is = 7 'Mold Release
myPfx = "cm*"
Case Is = 8 'Prepreg
myPfx = "cp*"
Case Is = 9 'Resin
myPfx = "cr*"
Case Is = 10 'Thickner
myPfx = "ct*"
Case Is = 11 'Abrasives
myPfx = "fa*"
Case Is = 12 'Paint
myPfx = "fp*"
Case Is = 13 'Hardware
myPfx = "h0*"
Case Is = 14 'Tooling Material
myPfx = "tm*"
Case Is = 15 'Maint. Equipment
myPfx = "me*"
Case Is = 16 'Maint. Building
myPfx = "mb*"
Case Is = 17 'Supplies Manufacturing
myPfx = "sm*"
Case Is = 18 'Supplies Cleaning
myPfx = "sc*"
Case Is = 19 'Supplied Office
myPfx = "so*"
Case Is = 20 'Supplies Packaging
myPfx = "sp*"
Case Is = 21 'Supplies Safety
myPfx = "ss*"
Case Is = 22 'Mining
myPfx = "10*"
Case Is = 23 'Glass Fixturing
myPfx = "20*"
Case Is = 24 'Auto Racing
myPfx = "60*"
Case Is = 25 'Aircraft
myPfx = "90*"
Case Is = 26 'HANS
myPfx = "hans*"
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.Offset(0, 3)
End If
If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox3.AddItem myCell.Offset(0, 0)
End If
If LCase(myCell.Value) Like LCase(myPfx) Then
Me.ComboBox4.AddItem myCell.Offset(0, 5)
End If
Next myCell
'
End Sub
###
The combo box 1 is what sorts out the data by type. Combo box 2 allows
me to then select from a reduced list of parts by the part name. I want
boxes 3 and 4 to display based on the results found in 2 but don't know
how to code it.
Thanks
LWhite