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

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

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, 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

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

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

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

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

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

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

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

Never mind, we keep stumbling on the lumpy road of MS VBA!
Thank you for your advice!
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

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

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

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