Named Range / Combo Box Problem

[damianhuerta]

Hi everybody,

I am using a combo box with a named range (Colors). However, the named range has the items written as a constant array. More specifically, In the "Refers to:" box, I wrote the following:

={"white";"black"}

Now, these are the only two options of colors that I need. Notice, they do not reference to a range of cells contained in a worksheet (just to avoid putting more lists in my workbook).

When I try to use this Named Range "Colors" in the "Input Range" field of the Combo Box (the one from the Form Controls), it gives me a msg box stating an invalid reference.

Basically, I want to hardcode the Items of my list in the Named Range "Colors" to then use the list in my combo box. Suggestions??

 

[ajm]

Hi everybody,

I am using a combo box with a named range (Colors). However, the named range has the items written as a constant array. More specifically, In the "Refers to:" box, I wrote the following:

={"white";"black"}

Now, these are the only two options of colors that I need. Notice, they do not reference to a range of cells contained in a worksheet (just to avoid putting more lists in my workbook).

When I try to use this Named Range "Colors" in the "Input Range" field of the Combo Box (the one from the Form Controls), it gives me a msg box stating an invalid reference.

Basically, I want to hardcode the Items of my list in the Named Range "Colors" to then use the list in my combo box. Suggestions??

damian, i think, if memory serves me correctly, that you need to specify the sheet oin which the named range resides. so say you put it on a sheet called "Sheet1", then in the input range you would put "'Sheet1'!Colors". Try it with and without the single quotes around the sheet name.

 

[T. Valko]

damian, i think, if memory serves me correctly, that you need to specify the sheet oin which the named range resides. so say you put it on a sheet called "Sheet1", then in the input range you would put "'Sheet1'!Colors". Try it with and without the single quotes around the sheet name.

It's not a named range. It's an array constant.

AFAIK, the Input RANGE must be a range address or a named range address.

 

[ajm]

It's not a named range. It's an array constant.

AFAIK, the Input RANGE must be a range address or a named range address.

Thanks TV. I read the question as how to feed the named range to the combo box and not how to create the named range using the constant array.

 

[Norie]

You could use the named array constant/whatever but you can't set it at design time.

Something like this perhaps:

Private Sub Worksheet_Activate()
Dim obj As Object
    Set obj = Me.Shapes("Drop Down 1").OLEFormat.Object
    obj.List = [Colors]
End Sub