ComboBox and rowsource from an external sheet
in [Programming]
|
From: Andrew on 10 Mar 2010 07:47 On Mar 10, 5:09 am, Andrew <andrewkgent...(a)gmail.com> wrote: > 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? 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
From: joel on 10 Mar 2010 08:43 When you use anther workbook in a formula on the worksheet it doesn't have to be opened initially, but you do have to open the 2nd workbook to get the data refreshed. You don't have to use menu to get a combox but instead you can use a 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" -- 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: OssieMac on 11 Mar 2010 02:44 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 "Andrew" wrote: > On Mar 10, 5:09 am, Andrew <andrewkgent...(a)gmail.com> wrote: > > 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? > > 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 > . >
First
|
Prev
|
Pages: 1 2 Prev: Structured Table Refs in VBA Next: LookUp Returning more than one rows |