ComboBox.RowSource?

[JimAnAmateur]

Strange: When I draw a ComboBox on a UserForm, I can specify RowSource for
this control (to let it read data from a given range in a worksheet).
However, when I draw a ComboBox directly in a worksheet, I don't see
RowSource in the Properties-window.

What's wrong? Any suggestions?

br Jim

 

[Coza]

Try a ControlBox Combobox instead

 

[Nick Hodge]

Jim

It's ListFillRange on the ActiveX control in Excel. The difference is
because UserForms are part of Office rather than specific to Excel

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[JimAnAmateur]

Thanks, Nick!

But, no matter what I write (in the field "ListFillRange"), it's just not
recorded (the field gets empty right away).
What is the syntax for this field? I tried e.g. "Sheet1!a1:a10" (with and
without quotation marks), ...

br Jim

 

[Guest]

Jim, If you use the Control Toolbox to create your combo box, you will need
to be in design mode to access the properties box. Otherwise you can use the
AddItem or List method to fill the box.

If you use the Forms Toolbar to create the combo box then you will need to
use the format control option on the Forms toolbar and on the Control tab you
can specify the range for the row source and links.

 

[Nick Hodge]

Jim

In the property enter

Sheet1!$A$1:$A$10

No quotes but you need the absolute reference
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[JimAnAmateur]

Coza,

I don't find ControlTollbox. I am using Excel 2003.
Where do I find it?

br Jim

 

[JimAnAmateur]

Nick,

I did as you describe, but still, the field returned empty right away.
I also did it in macro, e.g. ComboBox1.ListFillRange = "Sheet1!$A$1:$A$10",
and it didn't work either (the list remains empty).

More problems: ActiveX controls that I add on my sheet (TreeView,
RichTextBox), no matter how I specify the size (both in properties-windows,
and programmed in macro), they appaer as a little window (not the size I
specified) on the sheet when I reopen the file. I do have a Sub that is
called in Auto_Open, and that "re-draws" the controls on the sheet, but the
controls remains "tiny" on the sheet.

br Jim

 

[JimAnAmateur]

I used FormsToolbar first, since there is too little formatting facilities
on FormsToolbar (e.g., font size), I went over to using Control Toolbox.

So:
1) with FormsToolbar I can specify the row source (as you wrote here), but
not tthings like font face/size, ...?
2) with Control Toolbox I could change font face/size, but I (so far) seem
not to be able to specify row source (or ListFillRange).

br Jim

 

[Dave Peterson]

If you can change the font using the properties, then you should be able to see
listfillrange. It's about 7 entries below the font on the alphabetic tab.

 

[JimAnAmateur]

Yes, I do see it, and I can type in a value, but the value just disappears
and the field remains empty.
When I code it in the macro, no effect, the list remains empty. Strange.

 

[JimAnAmateur]

Now I have solved this, but in the "hard" way:
In macro using AddItem in a loop :-(

But, I still don't know why the field ListFillRange remains empty when I
just type in the range there in the Properties-window - I must have typed
the range in a wrong way/syntax.

Yes, I do see it, and I can type in a value, but the value just disappears
and the field remains empty.
When I code it in the macro, no effect, the list remains empty. Strange.

 

[Jon Peltier]

I can't reproduce your problems.

This works for me programmatically:

ActiveSheet.OLEobjects("ListBox2").ListFillRange = "Sheet1!A2:A11"

Manually I can enter A2:A11 without absolute references, with or without the
sheet name, and it works fine.

- Jon

 

[Nick Hodge]

Jon

After reading another post from the OP I think these are VB6 controls not
from Excel???

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Guest]

Hi Jim-

Follow these steps exactly and let us know if the ListFillRange property
accepts your entry:

1. Save all files and exit from excel. Restart Excel.

2. Use the Control Toolbox to add a single listbox to a worksheet in the new
and empty workbook.

3. Right-click the listbox and choose Properties.

4. In the ListFillRange property, type A1:A10 and press Enter.

Let us know what happens; maybe we can isolate the symptom or the
circumstances leading to it.
--
Jay

Now I have solved this, but in the "hard" way:
In macro using AddItem in a loop :-(

But, I still don't know why the field ListFillRange remains empty when I
just type in the range there in the Properties-window - I must have typed
the range in a wrong way/syntax.

 

[Guest]

Jim, when you enter the range in the properties box for ListFillRange do it
like this:

a10:c18

No quotation marks, no dollar signs, no spaces, no nothin except the colon
between the range references. The ListBox is automatically tied to the sheet
you create it on so you don't need the sheet1! reference for the range.

For sizing the controls, use the handles that you see when you right click
on them.
Just grab a node with your mouse pointer (cursor) and stretch the control to
the size you want.