Dependent ComboBoxes as Critiria for VLOOKUP or OFFSET
Hi Minitman,
Try replacing my suggested code with the following:
'=============
Option Explicit
Public blStopEvents As Boolean
'-------------
Private Sub ComboBox1_Change()
Call LoadCB2(Me.ComboBox1.Value)
End Sub
'-------------
Private Sub ComboBox2_Change()
If blStopEvents Then Exit Sub
Call LoadCB3(Me.ComboBox1.Value, Me.ComboBox2.Value)
End Sub
'-------------
Sub LoadCB2(sVendor As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim iRow As Long
Dim CB1 As ComboBox
Dim CB2 As ComboBox
Dim CB3 As ComboBox
Dim blLoaded As Boolean
If blStopEvents Then Exit Sub
Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set CB1 = Me.ComboBox1
Set CB2 = Me.ComboBox2
Set CB3 = Me.ComboBox3
blStopEvents = True
CB2.Clear
For iRow = 2 To Rng1.Rows.Count
If Rng1(iRow).Value = sVendor Then
CB2.AddItem Rng2(iRow).Value
End If
Next iRow
CB2.ListIndex = 0
blStopEvents = False
Call LoadCB3(CB1.Value, CB2.Value)
End Sub
'-------------
Sub LoadCB3(sVendor As String, sdate As String)
Dim Sh As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim iRow As Long
Dim CB3 As ComboBox
Dim blLoaded As Boolean
If blStopEvents Then Exit Sub
Set Sh = ThisWorkbook.Sheets("Data")
Set Rng1 = Sh.Range("dVendor")
Set Rng2 = Sh.Range("dDate")
Set Rng3 = Sh.Range("dNum")
Set CB3 = Me.ComboBox3
CB3.Clear
For iRow = 2 To Rng1.Rows.Count
If Rng2(iRow).Value = sdate _
And Rng1(iRow).Value = sVendor Then
CB3.AddItem Rng3(iRow).Value
End If
Next iRow
blStopEvents = True
CB3.ListIndex = 0
blStopEvents = False
End Sub
'<<=============
--
Regards,
Norman
|