Dynamic Combobox

[Dennis]

I have been searching hours for a solution and haven't had any luck finding a
solution to the problem the follows. At first, this seemed a simple task.

Simple story: I have a combobox that is prepopulated and I want a second
combobox to react in drill-down fashion to the change event of the first.

Simple, Right?

The combobox is an in-sheet combobox (i.e., not on a user form and not
created dynamically at run-time) that will be visible 100% of the time. When
the first value changed event occurs - I want to remove all of any previously
loaded data in the second combobox and re-popluate the list from a different
data source (that may be in or outside of the workbook). I can access windows
forms objects if on a userform by name - but, how do I access the object if
it's "in-sheet"?

Anyway, I guess the need here is: how do I access the properties of windows
form control in-sheet and not in a userform.

Any help would be greatly appreciated.

 

[Mike]

Put this into the sheet module that has you
combobox in them
Private Sub ComboBox1_Change()
Dim c As Variant
ComboBox2.Clear
For i = 1 To 10
Set c = Range("A" & i)
ComboBox2.Value = c
ComboBox2.AddItem (c)
Next
End Sub

 

[OssieMac]

Hi Dennis,

"how do I access the properties of windows form control in-sheet "

Firstly there are two types of controls that you can apply to a worksheet.

One set is called Forms controls and I suggest that you don't uses these. I
believe that they are left over from very early versions of xl. However, if
you do use them, right click the control then you get to the properties.

The other type are ActiveX controls. I suggest that you use these. To set
the properties on these, you need to turn on Design Mode. Design Mode turns
on when you initially create the control but you need to turn it on again if
you want to edit the control. It needs to be turned off after editing/setting
properties etc to use the control. The Design Mode control is toggled on and
off by a button that looks like a set square, ruler and pencil.

To access both type of controls:-

Excel 2007: On Developer Ribbon, use the Insert button in the Controls block
and both the Forms and ActiveX controls are displayed together under separate
headings.

Pre Excel 2007: Forms controls are on the Forms toolbar and ActiveX controls
are on the Toolbox toolbar.

In Userforms, I don't think that you can access the older Forms controls or
if you can, I don't know how.