Initialize Excel 2007 ComboBox on Chart

Question

Hello,

I'd like to initialize a ComboBox that exists on a Chart (a stand-alone chart, not a chart embedded in a sheet) to have two values, "Expenses" and "Income".

I tried entering a literal list ('={"Expenses", "Income"}' or '=("Expenses", "Income")') but trying either of these gave me the error "Reference is not valid". (The place I tried entering the literals was the "Input range" field accessed via right-click on control > Format Control > Control.)

I also tried initializing the control with VBA every time the chart was activated:

Private Sub Chart_Activate()

    Dim expensesIncomeDropDown As Object

    Set expensesIncomeDropDown = Me.Shapes("Drop Down 1") 'Error here
    expensesIncomeDropDown.DropDownLines = 2
    expensesIncomeDropDown.AddItem "Expenses"
    expensesIncomeDropDown.AddItem "Income"

End Sub

("Drop Down 1" is the index used by the macro recorder when I record modifying the control. I also tried "DropDown1".) But the line indicated throws the error: "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found." Using the locals window of the VBA IDE to inspect the Shapes property of Me (the Chart) indicates that the collection is empty. I tried accessing the control from the Chart_BeforeDoubleClick event (hypothesizing that the Shapes collection had not been initialized for the Activate event) but I had the same result.

Others are experiencing the same problem. Advice appreciated.

Answer 1

From your description of the right-click menu, you are using a form control rather than an ActiveX control.

I don't think you can enter values into the dropdown like you can with the ActiveX combobox. You must give the dropdown an input range that contains the values for the list, either manually or via VBA:

Dim shp As Shape

Set shp = chtTest.Shapes(1)
If shp.Type = msoFormControl Then
    If shp.FormControlType = xlDropDown Then
        shp.ControlFormat.ListFillRange = ThisWorkbook.Names("rngDDValues")
    End If
End If

You should also set the cell link so you can retrieve the value selected.

I would suggest using the ActiveX control, but it doesn't look like you can add one to a chart sheet, unfortunately.