Did you try to set the combobox's listfillrange manually?

If yes, then clear that out--either manually or via code.

me.combobox.listfillrange = ""

Stefi wrote:

I got a Run time error 70 - Permission denied at line

Me.Parent.Worksheets("anothersheet").ComboBox1.Lis t = _
Application.Transpose(.Value)

There is nothing particular in the workbook and in the worksheets (they are
not protected, etc.).

What can be the cause?

Thanks,
Stefi

€žStefi€� ezt Ã*rta:

Many thanks Dave, it is a great help, I'm giving a try to your code, I hope
it'll solve my problem.

Regards,
Stefi


€žDave Peterson€� ezt Ã*rta:

Me refers to the object that holds the code.

If you're in a worksheet module, it refers to the worksheet. If you're in the
ThisWorkbook module, it refers to the workbook. If you're in a userform, it
refers to that userform.



Stefi wrote:

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


--

Dave Peterson