Cascading Combo Boxes

[AFSSkier]

I have 4 FormControl CboBoxes on a sheet (not UserForm). These are
dynamically populated by reading unique ListFillRange values from 4 different
sheets that are connected to 4 separate Access Queries from a category
hierarchy (Dept, Cat, SubCat, ProdModule). I have some code that basically
does what it is suppose to do, by cascading through each of the CboBoxes. By
doing a Requery through the 4 separate Access Query sheets (Dept, Cat,
SubCat, ProdModule).

The problem I can’t overcome is when the user does a “RefreshAll†the 2nd,
3rd & 4th CboBoxes default to 0. The user would like to have the CboBoxes to
remain selected to their choices for next week’s “RefreshAllâ€.

Here’s basically how I have it working:
When the LinkCell to “DeptSheet†changes in the 1st CboBox “cboDEPTâ€, it
fires off a requery of the Access Query to repopulate CatSheet for the 2nd
CboBox “cboCatâ€. This gives cboCat a cascading effect. As the user chooses
one of the records from the list of categories that correspond to the
“GROCERY†dept, the LinkCell “CatSheet†changes in the 2nd CboBox “cboCatâ€,
it fires off a requery of the Access Query to repopulate SubCatSheet for the
3rd CboBox “cboSubCatâ€, etc, etc cascading through to the final CboBox
cboProdMod.

VBA Code:
Private Sub cboDept_Change()
'For example, when the Department "GROCERY" is chosen in CboDept
' CatSheet Requeries to repopulate the list for cboCat
'providing a list of "GROCERY" Categories.
cboCat.ListIndex = 0
End Sub

Private Sub cboCat_Change()
'For example, when the Category for "BABY FOOD" is chosen in CboCat
' SubCatSheet Requeries to repopulate the list for cboSubCat
'providing a list of "BABY FOOD" SubCategories.
cboSubCat.ListIndex = 0
End Sub

Private Sub cboSubCat_Change()
'For example, when the SubCategory for "INFANT FORMULAS" is chosen in
CboSubCat
' ProdModSheet Requeries to repopulate the list for cboProdMod
'providing a list of "INFANT FORMULAS" Product Modules.
cboProdMod.ListIndex = 0
End Sub

Private Sub cboDept_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'This resets cboDept to the top of the list "DEPT".
cboDept.ListIndex = 0
End Sub

I was also wondering, is there a better approach to this in Excel, like on a
VBAS User Form? I've read several other posts & even looked at the Data
Validations at http://www.contextures.com. But that does not work for my
application.

I have done this many times on an Access form with the following code:

Private Sub cboDEPT_AfterUpdate()
Me.cboCAT.Requery
Me.cboSUBCAT.Requery
Me.cboPRODMOD.Requery
End Sub


Private Sub CAT_AfterUpdate()
Me.SUBCAT.Requery
Me.PRODMOD.Requery
End Sub
Private Sub SUBCAT_AfterUpdate()
Me.PRODMOD.Requery
End Sub

 

[ker_01]

AFSSkier-

Consider adding a global variable (boolean) at the top of your module,
something like
Global UpdateAll as Boolean
(more knowledgable folks may give feedback on variable scope, maybe "Public"
is better than "Global"?)

Then change each of your subs (or at least the first one, which triggers
your cascade of zeros) to something more like:

Private Sub cboDept_Change()
'For example, when the Department "GROCERY" is chosen in CboDept
' CatSheet Requeries to repopulate the list for cboCat
'providing a list of "GROCERY" Categories.
If UpdateAll = True then cboCat.ListIndex = 0
End Sub

Then set UpdateAll to false at the beginning of your refresh code, and back
to true at the end of that code, so when your first combobox is updated, it
would cascade, but any changes after your refresh code will cause the cascade.

HTH,
Keith

 

[AFSSkier]

Keith,

I think I need something that will save the values in each of the CboBoxes
before the RefreshAll fires. Then replace the values back into each CboBox
at the end of the sub. Something like a DimVar.

Save Then Replace values in 4 CboBoxes on Worksheet "Filtering Form"
(C3) CboBox1 = cboDept
(C5) CboBox2 = cboCat
(C7) CboBox3 = cboSubCat
(C9) CboBox4 = cboProdMod

Also, your suggestion for "If UpdateAll = True Then cboProdMod.ListIndex =
0", actually cancels out the CboBox2 Requery to cell 0 when CboBox1 is
changed.

I couldn't find anything that would help on "global variable (boolean)".