If you're using the _Change event, you may want to look at the cell(s) that are
changing.

This worked fine for me:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Me.Range("myDefinedRange")
If Intersect(Target, .Cells) Is Nothing Then
'do nothing
Exit Sub
End If
Me.Parent.Worksheets("anothersheet").ComboBox1.Lis t = _
Application.Transpose(.Value)
End With
End Sub

I named A1:J1 MyDefinedRange (single row, multiple columns).

If you used a dynamic range, are you sure it's what you think it is?

msgbox me.range("mydefinedname").address

may help

=====
I used Me to represent the worksheet that owned the code.
Me.parent is the workbook that owns the worksheet. So
me.parent.worksheets("anothersheet") pointed to the worksheet in the same
workbook.

It's probably better to use Me to represent the worksheet that owns the code.
If you use activesheet, it may not be the one you want--depending on what your
code does.




Stefi wrote:

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


--

Dave Peterson