hey I have some code for a set of conditional dropdown boxes. Everything works but I have to manually run the macro to get the values in the drop down box to change. So what I need is for everything to automatically refresh whenever a box is changed, any suggestions?
Private Sub Combo_Change()
ComboBox1.ListFillRange = "Location"
If ComboBox1 = "Cable in Conduit" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "cable"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cableone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cablethree"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "750 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cableseven"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "1000 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cablethousand"
ComboBox3.ListIndex = 0
End If
End If
If ComboBox1 = "Direct Bury" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "direct"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directthree"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "750 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directseven"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "1000 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directthousand"
ComboBox3.ListIndex = 0
End If
End If
If ComboBox1 = "Duct Bank" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "duct"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "ductone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "ductthousand"
ComboBox3.ListIndex = 0
End If
End Sub
Private Sub Combo_Change()
ComboBox1.ListFillRange = "Location"
If ComboBox1 = "Cable in Conduit" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "cable"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cableone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cablethree"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "750 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cableseven"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "1000 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "cablethousand"
ComboBox3.ListIndex = 0
End If
End If
If ComboBox1 = "Direct Bury" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "direct"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directthree"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "750 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directseven"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "1000 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "directthousand"
ComboBox3.ListIndex = 0
End If
End If
If ComboBox1 = "Duct Bank" Then
Worksheets(1).OLEObjects("ComboBox2").ListFillRange = "duct"
ComboBox2.ListIndex = 0
If ComboBox2 = "1/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "ductone"
ComboBox3.ListIndex = 0
End If
If ComboBox2 = "3/0 AL" Then
Worksheets(1).OLEObjects("ComboBox3").ListFillRange = "ductthousand"
ComboBox3.ListIndex = 0
End If
End Sub