Dave, your example worked when the worksheet containing ComboBox1 was the
active sheet. I'd like to modify the code in the following way:
It should be called from a Change event macro and ComboBox1 is located in
another sheet, something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("anothersheet").ComboBox1.List = _
Application.Transpose(ActiveSheet.Range("mydefined name").Value)
End Sub

but this doesn't work.

Please, help to fix this code!

Thanks,
Stefi

€žStefi€� ezt Ã*rta:

Thanks Dave, I tried to use a defined name as the listfillrange, but it works
only if the defined name refers to a vetical range, otherwise the dropdown
list displays only the first item in the list. Anyway, now I can manage the
issue in code according to your suggestion, though I think that it's a silly
behaviour of Excel, unworthy of its otherwise high quality (I'm not sure that
"unworthy" really takes "of" preposition).

By the way, could you explain me the exact scope of ME. object?

Regards,
Stefi


€žDave Peterson€� ezt Ã*rta:

You could always populate the combobox's listfillrange in code with .additem or
..list (and return the transposed .value of the range):

Me.ComboBox1.List = Application.Transpose(Me.Range("a1:c1").Value)

And you could use a defined name as the listfillrange, but I think you'll have
to use another name to do the offset() stuff.


Stefi wrote:

Hi All,

Why a horizontal range, e.g like A1:G1 cannot be used in ComboBox
ListFillRange?
Is there a workaround for that?
Another question: can an OFFSET function or a named range be used in
ComboBox ListFillRange?

Thanks,
Stefi

--

Dave Peterson