create a second combo box from a first combo box

[Guest]

How do I create a second combo box, from a first one? I am trying to make a
hierarchical sheet listing, so the first box has file names, and the second
should show sheet names. I am passing the top, left, width and height, to
offset the position of the second box, and the name of the first so I can
name the second one, modifying the name to reflect that it is a 'sub' box.
For example, if the first is named,"Simpsons", I would like the second to be
named "SimpsonsSub". I do not want the second box present unless a choice is
made from the first box. Is this possible?

Wazooli

 

[Tom Ogilvy]

Recording a macro while adding a combobox provides this code

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=201, Top:=229.5, Width:=71.25, Height:=
_
16.5).Select

so you could do

Private Sub Simpsons_Click()
Dim cb As MsForms.ComboBox
Dim obj1 As OLEObject
Dim obj As OLEObject
On Error Resume Next
ActiveSheet.OLEObjects("SimpsonsSub").Delete
On Error GoTo 0
Set obj1 = ActiveSheet.OLEObjects("Simpsons")
Set obj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=obj1.Left + obj1.Width + 5, _
Top:=obj1.Top, _
Width:=obj1.Width, _
Height:=obj1.Height)
Set cb = obj.Object
cb.Name = "SimpsonsSub"
End Sub

You would then need to fill it based on the selection in Simpsons

Not sure how you want to trigger a deletion. But use code as shown since I
delete any existing box to avoid duplicates.

If you want to create event code for it, you can look at Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm

 

[Guest]

perfect -thanks tom

Recording a macro while adding a combobox provides this code

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=201, Top:=229.5, Width:=71.25, Height:=
_
16.5).Select

so you could do

Private Sub Simpsons_Click()
Dim cb As MsForms.ComboBox
Dim obj1 As OLEObject
Dim obj As OLEObject
On Error Resume Next
ActiveSheet.OLEObjects("SimpsonsSub").Delete
On Error GoTo 0
Set obj1 = ActiveSheet.OLEObjects("Simpsons")
Set obj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=obj1.Left + obj1.Width + 5, _
Top:=obj1.Top, _
Width:=obj1.Width, _
Height:=obj1.Height)
Set cb = obj.Object
cb.Name = "SimpsonsSub"
End Sub

You would then need to fill it based on the selection in Simpsons

Not sure how you want to trigger a deletion. But use code as shown since I
delete any existing box to avoid duplicates.

If you want to create event code for it, you can look at Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm