Thanks, Dave! Not a very clever behaviour!
Stefi


€žDave Peterson€� ezt Ã*rta:

Yep. That's the way it works.

Stefi wrote:

Hi Dave,

Now everything works, but this morning, when I opened again the workbook, I
was surprised because the last listfillrange - set via code - was not saved.
I had to insert the code snippet also in the Workbook_Open event to have the
combobox listfillrange ready to use immediately after opening the workbook,
without triggering the Sheet_change macro. Is it Normal?

Regards,
Stefi

€žDave Peterson€Â� ezt ÃÂ*rta:

The way I picked it up was by making the mistake (a few times!).

I don't recall seeing this warning anywhere, but I never looked too hard,
either.

One thing that I do now (for anything important) is to do set all those
properties in code. Then I don't have to worry about me messing anything up.

Stefi wrote:

Bingo, that was the trick!
Tell me please, how can one get such information (otherwise then from you
and other gurus)! I didn't find any remarks in Help on conflict between
setting a combobox's listfillrange manually and via code.
Many thanks, Dave!
Regards,
Stefi

€žDave Peterson€ÂÂ� ezt ÃÂÂ*rta:

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


--

Dave Peterson


--

Dave Peterson