ComboBox and rowsource from an external sheet

[Andrew]

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,

 

[joel]

You are missing a sheet name


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


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

 

[Dave Peterson]

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

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,

 

[OssieMac]

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.

 

[Andrew]

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

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?

 

[Andrew]

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.

OssieMac

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?

Please allow me to restate: I haven't been able to find any
information on using paths in VBA. If found an online example for a
function called GetValue(), but that function appears to be
unsupported or outdated. Can someone explain how to deal with paths.

set mypath = "C:\datafiles\data012210.xls" - this "would be the path
to the spreadsheet

 

[joel]

When you use anther workbook in a formula on the worksheet it doesn'
have to be opened initially, but you do have to open the 2nd workbook t
get the data refreshed.

You don't have to use menu to get a combox but instead you can use
sheet reference

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

or

Sheets("Sheet2").ComboBox1.RowSource = "'C:\[data.xls]'!A1:A100"

 

[OssieMac]

Hi Andres,

Basically the workbook must be open to use it as the Rowsource of the
ComboBox in which case the path is not required. The path in this instance
will cause an error because it is not part of an open workbook name. If you
don't want the workbook to be open then set a range in the workbook with the
combo box equal to the range in the other workbook then use that range with
the combobox.

When setting a range equal to the range in the Data workbook, simply select
the range in the data workbook then select the first cell of where you want
it in the workbook with the combo and paste special link. Close the Data
workbook and excel will look after setting the path, workbook and worksheet
name to the link.

Your use of paths is not incorrect for places where you do require them.


--
Regards,

OssieMac

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.

:
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?

Please allow me to restate: I haven't been able to find any
information on using paths in VBA. If found an online example for a
function called GetValue(), but that function appears to be
unsupported or outdated. Can someone explain how to deal with paths.

set mypath = "C:\datafiles\data012210.xls" - this "would be the path
to the spreadsheet
.