Prev: Structured Table Refs in VBA
Next: LookUp Returning more than one rows
From: Andrew on 9 Mar 2010 17:21
Hello,
I'm trying to fill a combo box from a column of names in another
workbook. Here's what I have:

menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100"

This appears to be correct. I get an error code 380 - could not set
row source property. Can someone tell me what is wrong with this
code?

thanks,
From: joel on 9 Mar 2010 17:52

You are missing a sheet name


from
"'C:\[data.xls]'!A1:A100"


to
"'C:\[data.xls]sheet1'!A1:A100"


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=186101

[url="http://www.thecodecage.com/forumz/"]Excel Live Chat[/url]

From: Dave Peterson on 9 Mar 2010 17:58
First, you'll have to open the data.xls workbook.

Then you can use:

Dim myRng as range

with workbooks("data.xls").worksheets("youdidn'tgivethename!")
set myrng = .range("a1:A10")
end with
....more code...
menu.combobox1.rowsource = myrng.address(external:=true)

Or if you want to close that data.xls workbook, you could just plop the values
from the range into the combobox.

menu.combobox1.list = myrng.value



Andrew wrote:
>
> Hello,
> I'm trying to fill a combo box from a column of names in another
> workbook. Here's what I have:
>
> menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100"
>
> This appears to be correct. I get an error code 380 - could not set
> row source property. Can someone tell me what is wrong with this
> code?
>
> thanks,

--

Dave Peterson
From: OssieMac on 9 Mar 2010 18:35
Hi Andrew,

'[Data.xls]Sheet1'!A1:A100

It appears that Data.xls needs to be open to use it as the Rowsource for the
Combo Box.

You can get the syntax for stuff like that by selecting an empty cell in
your workbook and enter = and then select the workbook and then the range and
press Enter. Then just select the formula in the formula bar and copy, press
enter or Esc to get out of the formula bar and paste it into the RowSource.

You might be better to make a range in the workbook with the ComboBox equal
to the range in Data.xls and then use the range in the workbook. That way
Data.xls does not need to be open.


--
Regards,

OssieMac


"Andrew" wrote:

> Hello,
> I'm trying to fill a combo box from a column of names in another
> workbook. Here's what I have:
>
> menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100"
>
> This appears to be correct. I get an error code 380 - could not set
> row source property. Can someone tell me what is wrong with this
> code?
>
> thanks,
> .
>
From: Andrew on 10 Mar 2010 07:09
On Mar 9, 4:35 pm, OssieMac <Ossie...(a)discussions.microsoft.com>
wrote:
> Hi Andrew,
>
> '[Data.xls]Sheet1'!A1:A100
>
> It appears that Data.xls needs to be open to use it as the Rowsource for the
> Combo Box.
>
> You can get the syntax for stuff like that by selecting an empty cell in
> your workbook and enter = and then select the workbook and then the range and
> press Enter. Then just select the formula in the formula bar and copy, press
> enter or Esc to get out of the formula bar and paste it into the RowSource.
>
> You might be better to make a range in the workbook with the ComboBox equal
> to the range in Data.xls and then use the range in the workbook. That way
> Data.xls does not need to be open.
>
> --
> Regards,
>
> OssieMac
>
> "Andrew" wrote:
> > Hello,
> > I'm trying to fill a combo box from a column of names in another
> > workbook.  Here's what I have:
>
> > menu.ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100"
>
> > This appears to be correct.  I get an error code 380 - could not set
> > row source property.  Can someone tell me what is wrong with this
> > code?
>
> > thanks,
> > .

Thanks for your help on the sheet name. But if I use a range then I
can simply call the information from another sheet without having to
open the other sheet. Okay, now another question. How do I call a
range in another workbook?
 |  Next  |  Last
Pages: 1 2
Prev: Structured Table Refs in VBA
Next: LookUp Returning more than one rows