RowSource for my ListBox

[ordnance1]

Below is the RowSource I would like to use for the ListBox on my UserForm.
Can this be done without actually opening the source document
(EmployeeList.xlsm)?


'[EmployeeList.xlsm]Employee_List'!$A$2:$Z$300

 

[p45cal]

It might be, but it would have to include the path.
What can be done for sure is data in cells from a closed workbook ca
be placed in an open workbook, just with formulas like:
='C:\Documents and Settings\FredBloggs\M
Documents\[Boook1.xls]Data'!$C$3
Now all you need to do is put that local cell range inot your rowsourc
property - perhaps even making it a dynamic named range.


Below is the RowSource I would like to use for the ListBox on m
UserForm.

Can this be done without actually opening the source document
(EmployeeList.xlsm)?


'[EmployeeList.xlsm]Employee_List'!$A$2:$Z$300

 

[joel]

It will work the first time you enter the row source but won'
automaticaly update unless you open the workbook

 

[p45cal]

Even with something like this:


VBA Code:
--------------------


ActiveWorkbook.UpdateLink Name:="C:\Docum......boook1.xls", Type:=xlExcelLinks
--------------------



in the code that initialises your userform?


It will work the first time you enter the row source but won'
automaticaly update unless you open the workbook

 

[joel]

From the help on Updatelinks

Remarks
XlUpdateLinks can be one of these XlUpdateLinks constants.
xlUpdateLinksAlways Embedded OLE links are always updated for th
specified workbook.
xlUpdateLinksNever Embedded OLE links are never updated for th
specified workbook.
xlUpdateLinksUserSetting Embedded OLE links are updated according t
the user's settings for the specified workbook.


Updatelinks says the links can be updated, not when the links ge
updated. The links do not get uypdated until the workbook is opened.

Don't blame me for things that don't work that way people expect the
to work.

 

[p45cal]

The Updatelinks property is different from the UpdateLink method.
From the UpdateLink help:

---------------------------------
UpdateLink Method
See AlsoApplies ToExampleSpecificsUpdates a Microsoft Excel, DDE, o
OLE link (or links).

expression.UpdateLink(Name, Type)
expression Required. An expression that returns a Workbook object.

Name Optional String. The name of the Microsoft Excel or DDE/OL
link to be updated, as returned from the LinkSources method.

Type Optional XlLinkType.

XlReferenceStyle can be one of these XlReferenceStyle constants.
xlLinkTypeExcelLinks default.
xlLinkTypeOLELinks (also used for DDE links)

Remark
Note When the UpdateLink method is called without any parameters
Excel defaults to updating all worksheet links.

Example
This example updates all links in the active workbook.

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources