Your code indicates you are using dropdown boxes from the forms toolbar.
Your first piece of code puts in a dropdown box with 3 choices, so it must
be for creating what you call Combobox1. When you select a choice, it
should then call the other procedure to create your Combobox2. the other
procedure is assigned to Combobox1 as its OnAction property. I will go
with the generic Combobox1 and Combobox2 since I could misinterpret the
names you have used and confuse you further.
Sub CreateCombobox1()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
.Name = "Combobox1"
.OnAction = "Combobox1_Change"
End With
End Sub
the above sub has to be run either manually or by some event to create the
first combobox. After created, if a value is selected by the user from
Combobox1, then the Combobox1_Change macro runs because we have assigned it
to the onaction property.
Sub Combobox1_Change()
Dim idex as Long
' delete any existing dropdown box named Combobox2
On Error Resume Next
Worksheets(1).DropDowns("Combobox2").Delete
On Error goto 0
idex = Worksheets(1).DropDowns("Combobox1").ListIndex
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.Name = "Combobox2"
Select Case Idex
Case 1
.ControlFormat.AddItem "A1", 1
.ControlFormat.AddItem "A2", 2
.ControlFormat.AddItem "A3", 3
.ControlFormat.AddItem "A4", 4
.ControlFormat.AddItem "A5", 5
.ControlFormat.AddItem "A6", 6
.ControlFormat.AddItem "A7", 7
.OnAction = "Combobox2A_Click"
Case 2
.ControlFormat.AddItem "B1", 1
.ControlFormat.AddItem "B2", 2
.ControlFormat.AddItem "B3", 3
.ControlFormat.AddItem "B4", 4
.ControlFormat.AddItem "B5", 5
.ControlFormat.AddItem "B6", 6
.ControlFormat.AddItem "B7", 7
.OnAction = "Combobox2B_Click"
Case 3
.ControlFormat.AddItem "C1", 1
.ControlFormat.AddItem "C2", 2
.ControlFormat.AddItem "C3", 3
.ControlFormat.AddItem "C4", 4
.ControlFormat.AddItem "C5", 5
.ControlFormat.AddItem "C6", 6
.ControlFormat.AddItem "C7", 7
.OnAction = "Combobox2C_Click"
End Select
End With
End Sub
So you assign different click event macros to the Combobox2 based on the
selection from Combobox1.
I also give any box I create, a specific name, so I can refer to it later.
I have combobox1 with choice "A","B" and "C". If the user chooses "A" then I
want another combobox to appear to show the seven categories of "A". And the
[quoted text clipped - 74 lines]