Enable/disable Excel 2007 combobox

Question

I need to prohibit user from selecting value in some Excel 2007 combobox control inserted via Developer menu by condition. Now I managed only to show/hide the control.

ActiveWorkbook.Worksheets("summary").Shapes("months").Visible = year <> ""

Is it possible to enable/disable it instead?

Answer 1

This doesn't result in the "typical" enabled/disabled control that you would get if you were using an ActiveX combobox rather than a Forms combobox, but it does prevent the user from accessing it:

Sub ChangeState()

    Dim shp As Shape
    Set shp = ActiveWorkbook.Worksheets("summary").Shapes("months")
    shp.ControlFormat.Enabled = Not shp.ControlFormat.Enabled

End Sub

Answer 2

I have found what the combobox control could be disabled by

ActiveWorkbook.Worksheets("summary").DropDowns("months").Enabled = year <> ""

but unfortunately it won't be grayed to visually show its state.

Answer 3

I used to have the same problems with MS Access.

State (enabled / disabled) is different to presentation (background color = grey)

The way I used to get around this was to do both:

Dim enabled As Boolean
enabled = year <> ""

ActiveWorkbook.Worksheets("Sheet1").Shapes(1).Enabled = enabled

If enabled Then
    ActiveWorkbook.Worksheets("Sheet1").Shapes("months").BackColor = rgbGrey
Else
    ActiveWorkbook.Worksheets("Sheet1").Shapes("months").BackColor = rgbWheat
End If