Having trouble with combo boxes.

[Brian]

Is it possible to to use Multiple Combo Boxes to narrow down choices for
specfic data in a worksheet?

Example: User Selects from choices in Combo Box 1, the choice from that
gives the choices for Combo Box 2, which gives you the choices for Combo Box
3?

All the Data to fill Combo Box 3 is in an excel worksheet in the same
workbook as the User Form. I have the worksheet set up as named ranges
"CLLI_GA", etc...

Combo box 1 = Customer (Approx 8 customers)
Combo box 2 = State (Approx 9 States)
Combo box 3 = Office (400 Offices per State)

I got the first combo box to work with the add item method, but I am not
sure how to continue on.

Private Sub UserForm_Initialize()

'Customer Information
With Me.Customer_11
.AddItem ""
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
End With
End Sub

Any help or sugestions would be greatly apprieciated.

 

[OssieMac]

Hi Brian,

Called Cascading Combo boxes. Check out the following link.

http://www.excel-vba.com/vba-forms-3-7-combo-boxes.htm

 

[OssieMac]

Hi again Brian,

I didn't realize that you have to pay for info at that site. I'll see if I
can put an answer together for you.

 

[Brian]

I didn't mean to possibly offend you on my other post about resizing the User
form. I am sorry if came off as kind of rude. I have been working on the
program for a month now and I am getting really frustrated at it. Everytime I
make 3 steps forward i get something like that which really sets me back.

The truth is because of that issue I may not be able to use this program and
thats really disapoiting if you know what I mean.

Again I am sorry if i came acroos as rude.

 

[OssieMac]

Don't worry about it Brian. I interpreted it as as simple frustration and I
fully understand that; I often get like it. Matter of fact I am approaching
it now because I am having trouble with the cascading combo boxes; nearly
there and can't get the last bit correct and not sure that I can.

Did you check out my latest option on your other thread for the userform size?

Unfortunately the userform thing is an inaccurate science because when you
adjust the screen resolution the height and width do not remain proportional.
You can adjust the form size quite well but you need to Zoom to adjust the
size of the controls in the form and the zoom affects both height and width
and if not proportional it does not work well.

It would be a lot of work but it should be possibe to identify the screen
resolution and then set the form size and then separately set the size and
position of all the controls in the form.

 

[Brian]

After your last post I went on a "Where's Waldo' search for anything on
Cascading Combo Boxes. I found some code that looks like it would work,
except for the fact that i don't under stand it or how it works. Plus there
table is layed out odd. My Worksheet is just a standard table with names,
address, zip, etc... The problem is that if I have to retype them it's going
to take 2 months.

Whats so bad is I only have 2 major hurdles to over come to basically
complete this program.

1: User Form Resize
2: Cascading Combo Boxes

I didn't understand this code, but you probaly will look at it and
understand it. I know that you will have more of a clue than I will.

http://www.xldynamic.com/source/xld.Dropdowns.html

It might come down to paying someone to write the code for resizing the User
Form and thats Ok as long as it gets done. "SOON" LOL

Thanks for all your help

 

[Brian]

I like the way the resize works that you posted.


I set it up the test program as a Macro seperate from the program. The user
can run that macro first to get there settings. Can we add a text box for the
user to adjust there settings without having to go into the code? Something
like?

A text box for dblMultWdth = vidWidth / 1446
A text box for dblMultHt = vidHeight / 816

A spin Button for .Zoom = 75 * dblZoom
A spin Button for .Top = 2
A spin Button for .Left = 35

THanks so much for your help on this, I was starting to worry about if it
could be done.

 

[OssieMac]

Hi again Brian,

Userform.
There is no need for each user to run the code to find their own setting.
That code was for you to run on the computer on which you developed the
Userform. Just need a base for the code to start with. When you know what the
parameters are, just edit the code using the returned width and height and
the proportional calculation should adjust the size for the other users.
Unfortunately it is not foolproof coding and you still might find it
unsatisfactory depending on the resolutions being used.

I can’t download the workbook from the page you gave me so really not much
chop. Says the page is unavailable.

Anyway I have come up with a solution to cascading ComboBoxes. (Pity it is
not Access because cascading ComboBoxes are basically built in; just need to
know how to manipulate them.)

However, you have now said that you don’t want to re-type your raw data so
can you post a sample of your raw data for the combo boxes. Just a few lines
will do. Don’t post customer names; just replace them with a bunch of A’s,
B’s and C’s etc. I want to see if my code will work with your data layout.

 

[Brian]

The info is not top secrect, so it's no big deal.

Here are the column headings:

GEORGIA OFFICES
CLLI NAME ADDRESS 1 ADDRESS 2 CITY STATE ZIP GLC Code T-Base

Data in Worksheet
--------------------------------
Clli: ACWOGAMA
Add 1: 4745 Logan Road
City: Acworth
St: GA
Zip: 30101
GLC: F5341

Clli: AGSTGAAU
Add 1: 3523 Washington Street
City: Augusta
St: GA
Zip: 30907
GLC: R3547

Clli: AGSTGABM
Add 1: 1490 Ellis Street
City: Augusta
St: GA
Zip: 30902
GLC: R6341

 

[Brian]

I can have the girl at the office cut and paste it if need be. All it is is
just a list of offices in each state.

The coulmns go across on my worksheet. The example on that web page went
down the page. Do you know how big that worksheet would be to have 400
offices in 9 different states done that way.

What is going to be difficult is when the last choice "CLLI" is choose, I am
going to try and get it to auto fill the rest of the Info into the userform.
That should be fun.

 

[OssieMac]

Hi again Brian,

I see you have posted for more help no doubt due to me being slow to get
back to you but as I have written this code I thought I might as well post
it. If you get a better answer from someone else then go for it.

For the following code you need 2 additional worksheets in your workbook.
One called Raw Data and the other called Combo Data.

In the Raw Data sheet you need your list of data in the first 3 columns.
(That could be changed but better to keep it simple if it just means copying
the 3 columns.)

The first column will contain the data for the first combo box
The second column has data for the 2nd combo box
Third column has data for the third combo box.

Now this data requires every row in each column to have data. Therefore the
data should look something like the following.(I am not sure which one is the
customer and which one is the office in you sample data so I have assumed the
first is customer and last is office but I am sure you can sort that out.)

It does not matter if the customer is repeated and appears in multiple
states and/or multiple offices just so long as every cell in each column
contains data like the following. (Some of the states I have used are
ficticious because I am not from Nth America and i don't know the states or
abbreviations for them).

Customer State Office
ACWOGAMA GA F5341
AGSTGAAU GA R3547
AGSTGABM GA R6341
ACWOGAMA NJ A2345
AGSTGAAU SF B123
AGSTGABM AL C6789

Basically the system copies a unique sorted list of the data in column 1 of
Raw Data and places it in Column 1 of Combo Data when the form is loaded and
this becomes the Rowsource for Combo1.

When a selection is made in Combo1 then AutoFilter is applied to the first
column in Raw Data based on the value in Combo1. A unique sorted list of the
visible cells in column 2 is then copied to Column 2 of Combo Data and it
becomes the Rowsource for Combo2.

When a selection is made in Combo2 then AutoFilter is applied to the 2nd
column in Raw Data based on the value in Combo2. A unique sorted list of the
visible cells in column 3 is then copied to Column 3 of Combo Data and it
becomes the Rowsource for Combo3.

'********************************
'This sub between the asteeisk lines
'in module that Shows userform
'Last command is Userform1.Show

Sub ShowTheUserform()
Dim rngFilter As Range

'Next line is optional
Application.ScreenUpdating = False

With Sheets("Combo Data")
'Clear existing data
.Columns("A:C").ClearContents

'Name start cells for each ComboBox RowSource
.Range("A1").Name = "Cbo1List"
.Range("B1").Name = "Cbo2List"
.Range("C1").Name = "Cbo3List"
End With

'Create unique list of first column
'of data for 1st ComboBox RowSource
With Sheets("Raw Data")
'Turn off AutoFilter
.AutoFilterMode = False
'Set rng variable to all data in column A
Set rngFilter = .Range(.Cells(1, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

'Copies unique data to Sheets("Combo Data")
rngFilter.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Cbo1List"), _
Unique:=True

'Name the column of data for 1st ComboBox
'Starts row 2; excludes column header
With Sheets("Combo Data")
.Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A") _
.End(xlUp)).Name = "Cbo1Source"

'Clear any existing Sort Parameters
.Sort.SortFields.Clear

'Set the Sort Parameters
.Sort.SortFields.Add _
Key:=Range("Cbo1Source"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

'Sort the data
With .Sort
.SetRange Range("Cbo1Source")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End With

'Assign RowSource for 1st ComboBox property
'Edit UserForm1.ComboBox1 to your
'Userform name and ComboBox name
UserForm1.ComboBox1.RowSource = "Cbo1Source"

'Edit Userform1 to your userform name
UserForm1.Show

End Sub
'********************************

'#########################################
'These 3 subs between the # lines
'in the Userform code module

'Ensure that Sub name matches your
'1st ComboBox name.
Private Sub ComboBox1_AfterUpdate()

'The following line will suppress running
'of the code if the field is simply cleared.
If Me.ComboBox1.Value = "" Then Exit Sub

'Creates Sorted, Unique list for
'2nd ComboBox RowSource
Dim lngLastCol As Long
Dim lngLastRow As Long
Dim rngFilter As Range
Dim i As Long

'Clear values from remaining ComboBoxes
'because after updating 1st ComboBox
'others may be incorrect.
Me.ComboBox2 = ""
Me.ComboBox3 = ""

'Clear 2nd ComboBox RowSource data.
Sheets("Combo Data").Columns("B") _
.ClearContents

With Sheets("Raw Data")
'Finding last column and last row is
'more reliable than UsedRange
lngLastCol = .Cells(1, 1) _
.End(xlToRight).Column

lngLastRow = .Cells(1, 1) _
.End(xlDown).Row

'Remove AutoFilter and reset to ensure
'correct range is AutoFiltered.
.AutoFilterMode = False
.Range(.Cells(1, 1), _
.Cells(lngLastRow, lngLastCol)) _
.AutoFilter

With .AutoFilter.Range
'Sets 1st Filter to match 1st ComboBox.
'Edit ComboBox1 to your
'1st Combo Box Name.
.AutoFilter Field:=1, Criteria1:= _
Me.ComboBox1.Value

'Copy visible cells to Sheets("Combo Data")
.Columns(2) _
.SpecialCells(xlCellTypeVisible) _
.Copy Range("Cbo2List")
End With
End With

With Sheets("Combo Data")
lngLastRow = .Cells(.Rows.Count, "B") _
.End(xlUp).Row

'Sort required before duplicates
'can be removed.
'Name the range ready to sort.
.Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B") _
.End(xlUp)).Name = "Cbo2Source"

'Clear any existing Sort Parameters
.Sort.SortFields.Clear

'Set the Sort Parameters
.Sort.SortFields.Add _
Key:=Range("Cbo2Source"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

'Sort the data
With .Sort
.SetRange Range("Cbo2Source")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

'Remove any duplicates.
'Must work backwards when deleting.
For i = lngLastRow To 2 Step -1
If .Cells(i, "B") = .Cells(i - 1, "B") Then
.Cells(i, "B").Delete Shift:=xlUp
End If
Next i

'Name the range for ComboBox2 RowSource
.Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B") _
.End(xlUp)).Name = "Cbo2Source"
End With

'Assign ComboBox2 RowSource
'Edit ComboBox2 to your 2nd ComboBox name
Me.ComboBox2.RowSource = "Cbo2Source"
End Sub

'Ensure that Sub name matches your 2nd ComboBox name
Private Sub ComboBox2_AfterUpdate()

'The following line will suppress running
'of the code if the field is simply cleared.
If Me.ComboBox2.Value = "" Then Exit Sub

'Creates Sorted, Unique list for
'3rd ComboBox RowSource
Dim lngLastCol As Long
Dim lngLastRow As Long
Dim i As Long

'Clear value from remaining ComboBox
'because after updating 2nd ComboBox
'3rd ComboBox may be incorrect.
Me.ComboBox3 = ""

'Clear 3rd ComboBox RowSource data.
Sheets("Combo Data").Columns("C") _
.ClearContents

With Sheets("Raw Data")
With .AutoFilter.Range
'Sets 2nd filter to match 2nd ComboBox
'Edit ComboBox2 to your
'2nd Combo Box Name.
.AutoFilter Field:=2, Criteria1:= _
Me.ComboBox2.Value

'Copy visible cells to Sheets("Combo Data")
.Columns(3) _
.SpecialCells(xlCellTypeVisible) _
.Copy Range("Cbo3List")
End With
End With

With Sheets("Combo Data")
lngLastRow = .Cells(.Rows.Count, "C") _
.End(xlUp).Row

'Sort required before duplicates
'can be removed.
'Name the range ready to sort.
.Range(.Cells(2, "C"), _
.Cells(.Rows.Count, "C") _
.End(xlUp)).Name = "Cbo3Source"

'Clear any existing Sort Parameters
.Sort.SortFields.Clear

'Set the Sort Parameters
.Sort.SortFields.Add _
Key:=Range("Cbo3Source"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal

'Sort the data
With .Sort
.SetRange Range("Cbo3Source")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

'Remove any duplicates.
'Must work backwards when deleting
For i = lngLastRow To 2 Step -1
If .Cells(i, "C") = .Cells(i - 1, "C") Then
.Cells(i, "C").Delete Shift:=xlUp
End If
Next i

'Name the range for ComboBox3 RowSource
.Range(.Cells(2, "C"), _
.Cells(.Rows.Count, "C") _
.End(xlUp)).Name = "Cbo3Source"
End With

'Assign ComboBox3 RowSource
'Edit ComboBox3 to your 3rd ComboBox name
Me.ComboBox3.RowSource = "Cbo3Source"
End Sub

Private Sub ComboBox3_AfterUpdate()
'If ComboBox3_AfterUpdate is used to run
'other code then the following line will
'suppress running of the code if the
'field is simply cleared.
If Me.ComboBox3.Value = "" Then Exit Sub

End Sub

Private Sub UserForm_Terminate()
'This sub and code is optional
'It just cleans up but not essential

'Remove AutoFilter on the Raw Data Sheet.
With Sheets("Raw Data")
.AutoFilterMode = False
End With

'Clear all data from Combo Data sheet.
With Sheets("Combo Data")
.Cells.Clear
End With

End Sub
'############################################

 

[Christopher Kluth]

OssieMac,
Would it be possible to provide a file of the code you posted here?
I'm using Excel 2003, and even though I'm copying and psating the code, the Combo Boxes end up being empty when I run/submit the form with the 3 Combo Boxes.
Thank you, OssieMac.



OssieMac wrote:

Hi again Brian,I see you have posted for more help no doubt due to me being
18-Feb-10

Hi again Brian

I see you have posted for more help no doubt due to me being slow to ge
back to you but as I have written this code I thought I might as well pos
it. If you get a better answer from someone else then go for it

For the following code you need 2 additional worksheets in your workbook
One called Raw Data and the other called Combo Data

In the Raw Data sheet you need your list of data in the first 3 columns
(That could be changed but better to keep it simple if it just means copyin
the 3 columns.

The first column will contain the data for the first combo bo
The second column has data for the 2nd combo bo
Third column has data for the third combo box

Now this data requires every row in each column to have data. Therefore th
data should look something like the following.(I am not sure which one is th
customer and which one is the office in you sample data so I have assumed th
first is customer and last is office but I am sure you can sort that out.

It does not matter if the customer is repeated and appears in multipl
states and/or multiple offices just so long as every cell in each colum
contains data like the following. (Some of the states I have used ar
ficticious because I am not from Nth America and i do not know the states o
abbreviations for them)

Customer State Offic
ACWOGAMA GA F534
AGSTGAAU GA R354
AGSTGABM GA R634
ACWOGAMA NJ A234
AGSTGAAU SF B12
AGSTGABM AL C678

Basically the system copies a unique sorted list of the data in column 1 o
Raw Data and places it in Column 1 of Combo Data when the form is loaded an
this becomes the Rowsource for Combo1

When a selection is made in Combo1 then AutoFilter is applied to the firs
column in Raw Data based on the value in Combo1. A unique sorted list of th
visible cells in column 2 is then copied to Column 2 of Combo Data and i
becomes the Rowsource for Combo2

When a selection is made in Combo2 then AutoFilter is applied to the 2n
column in Raw Data based on the value in Combo2. A unique sorted list of th
visible cells in column 3 is then copied to Column 3 of Combo Data and i
becomes the Rowsource for Combo3

'*******************************
'This sub between the asteeisk line
'in module that Shows userfor
'Last command is Userform1.Sho

Sub ShowTheUserform(
Dim rngFilter As Rang

'Next line is optiona
Application.ScreenUpdating = Fals

With Sheets("Combo Data"
'Clear existing dat
..Columns("A:C").ClearContent

'Name start cells for each ComboBox RowSourc
..Range("A1").Name = "Cbo1List
..Range("B1").Name = "Cbo2List
..Range("C1").Name = "Cbo3List
End Wit

'Create unique list of first colum
'of data for 1st ComboBox RowSourc
With Sheets("Raw Data"
'Turn off AutoFilte
..AutoFilterMode = Fals
'Set rng variable to all data in column
Set rngFilter = .Range(.Cells(1, "A"),
..Cells(.Rows.Count, "A").End(xlUp)
End Wit

'Copies unique data to Sheets("Combo Data"
rngFilter.AdvancedFilter
Action:=xlFilterCopy,
CopyToRange:=Range("Cbo1List"),
Unique:=Tru

'Name the column of data for 1st ComboBo
'Starts row 2; excludes column heade
With Sheets("Combo Data"
..Range(.Cells(2, "A"),
..Cells(.Rows.Count, "A")
..End(xlUp)).Name = "Cbo1Source

'Clear any existing Sort Parameter
..Sort.SortFields.Clea

'Set the Sort Parameter
..Sort.SortFields.Add
Key:=Range("Cbo1Source"), _

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorial...24-c9960b55b669/putting-twitter-realtime.aspx