ComboBox ListFillRange

[Stefi]

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]

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]

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:

 

[Stefi]

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("mydefinedname").Value)
End Sub

but this doesn't work.

Please, help to fix this code!

Thanks,
Stefi

„Stefi†ezt írta:

 

[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.

 

[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.List = _
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]

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:

 

[Stefi]

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

Me.Parent.Worksheets("anothersheet").ComboBox1.List = _
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:

 

[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]

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:

 

[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.

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:

 

[Stefi]

Never mind, we keep stumbling on the lumpy road of MS VBA!
Thank you for your advice!
Stefi


„Dave Peterson†ezt írta:

 

[Stefi]

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:

 

[Dave Peterson]

Yep. That's the way it works.

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:

 

[Stefi]

Thanks, Dave! Not a very clever behaviour!
Stefi


„Dave Peterson†ezt írta: