Cascading Combo Box’s on worksheet (Excel-2003)

[AikonEnohp]

My apologies for the long message. I have noticed that people seeking assistance via message boards often fail to provide enough information.

Summary: Need to dynamically resolve ‘ListFillRanges’ for 5 ComboBox controls located on a worksheet based on varying combinations of the ComboBox values. All information and logic needed to resolve the ListFillRanges is self-contain within the workbook utilizing matrix tables and formula logic. VBA code is only needed for initializing the control values, fetching resolved ListFillRanges and updating 5 named-range values.

More information: The worksheet logic works as expected but various VBA coding methods fail. Problems encountered include run-away code, corrupted ComboBox controls (the value property vanishes) and workbook corruption and fatal Excel errors.

Description:
The workbook contains:
wksUI (User interface a date field and 5 combo-box controls.
wksLookup - lookup tables used by combo boxes and sheet-level validation.
wksMatix3 - used to determine the ListFillRange for cboOpt4 based on Combo-box 1 thru 3.
wksMatix4 - used to determine the ListFillRange for cboOpt5 based on Combo-box 1 thru 4
wksMatix5 - an all items table
wksResults – Stores values form Combo-boxes and formulas for business-layer logic.


wksResults has 11 individually named cells.

rngTransDate
rngCBO1
rngCBO2
rngCBO3
rngCBO4
rngCBO5
RngCalcMatrixID
rngCBO1List
rngCBO2 List
rngCBO3 List
rngCBO4 List
rngCBO5 List

rngCBO1 thru rngCBO5 are intended to store values from 5 Combo-box controls located on wksUI. The values in these fields are combined to form a 5 digit value calculated and stored in rngCalcMatrixID The values in rngCBO1 thru rngCBO5 are limited to the number of items in corresponding “Lookup” ranges. Example: If the lookup ranges associated with cboOpt1 thru cboOpt5 each have 5 elements then max value of rngCalcMatrixID would equal 55,555. If rngCBO1 = 3, rngCBO2 = 2 and the reaming rngCBO values are empty, the value resolved would equal 32000. Dynamic ‘FillListNames’ are resolved from the matrix worksheets based on the calculated value in rngCalcMatrixID.


Help Needed: Code to retrieve the resolved ListFileRanges and to update the ListFillRanges based on selection changes to any one of 5 combo-box controls.

The code I have tried is follows. Note that this code only deals with the date field and the first 2 controls


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ If TargetCloseDate not valid clear FIRST CBO ListFillRange
‘ Otherwise retrieve the resolved CBO ListFillRange

Dim vRangeValue As Variant
Dim iRangeData As Integer
Dim vVarType As Variant

Dim rngTargetClose As Range
Dim cboProj As MSForms.ComboBox
Dim rngProjData As Range
Dim rngProjFillList As Range

Set rngTargetClose = Range("ui_TargetCloseDate")
Set cboProj = wksUI.cboProjTypeID
Set rngProjData = wksResults.Range("RES_ProjTypeID")
Set rngProjFillList = wksLookUps.Range("LOOK_aryProjTypes")

Application.EnableEvents = False

If rngTargetClose <= Date Then
rngTargetClose = ""
With cboProj
.Value = Empty
.ListFillRange = Empty
.Enabled = False
End With
rngProjData = ""
rngTargetClose.Activate

Else
cboProj.ListFillRange = (rngProjFillList.Name)
With cboProj
.Activate
.Enabled = True
End With
End If
Call MEvalSenerioOptions.Test
Application.EnableEvents = True
End Sub


‘*** Called by
‘*** wksUI. Worksheet_SelectionChange ()
‘***wksUI. Worksheet_Activate()
‘*** all CNO Change Events

Sub Test()
Dim vRangeValue As Variant
Dim iRangeData As Integer
Dim vVarType As Variant
Set cboOcc = wksUI.cboOccTypeID
Set rngOccData = wksResults.Range("RES_OccTypeID")
Set rngOccFillList = wksResults.Range("RES_OccCntFillList")
‘-----------------------
' Evaluate cboOCC: If cboOccDoc is empty then:
' Clear value Property
' Clear ListFillRange property
' Set Enabled property to FALSE
' Clear associated range value
'
' Otherwise:
' Set control value to associated range value
' Set ListFillRange property
' Set Enabled property to TRUE
' Set associated range value to control's value

Application.EnableEvents = False
cboOcc.Activate
vVarType = VarType(rngProjData.Value)
If Application.WorksheetFunction.Or(vVarType = 0, vVarType = 1, vVarType = 10, rngProjData <= 0) Then
With cboOcc 'cboOpt2
.Value = 0
.ListFillRange = Empty
.Enabled = False
End With
rngOccData = ""
Else
With cboOcc
.ListFillRange = CStr(rngOccFillList)
.Enabled = True
End With
rngOccData = cboOcc.Value 'cboOpt2
End If
ExitSub:
Application.EnableEvents = True
End Sub

I am a novice and any assistance will be greatly appreciated[/b]