RowSource for my ListBox

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 

0
ordnance1
4/12/2010 6:16:40 PM
excel.programming 6508 articles. 2 followers. Follow

5 Replies
633 Views

Similar Articles

[PageSpeed] 18

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 can
be placed in an open workbook, just with formulas like:
='C:\Documents and Settings\FredBloggs\My
Documents\[Boook1.xls]Data'!$C$3
Now all you need to do is put that local cell range inot your rowsource
property - perhaps even making it a dynamic named range.



ordnance1;697172 Wrote: 
> 
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

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194950

http://www.thecodecage.com/forumz

0
p45cal
4/15/2010 3:44:45 PM
It will work the first time you enter the row source but won't
automaticaly update unless you open the workbook


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

http://www.thecodecage.com/forumz

0
joel
4/15/2010 4:06:58 PM
Even with something like this:


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

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



in the code that initialises your userform?



joel;700211 Wrote: 
> 
It will work the first time you enter the row source but won't
automaticaly update unless you open the workbook


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194950

http://www.thecodecage.com/forumz

0
p45cal
4/15/2010 4:36:01 PM
From the help on Updatelinks

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


Updatelinks says the links can be updated, not when the links get
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 them
to work.


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

http://www.thecodecage.com/forumz

0
joel
4/15/2010 4:42:23 PM
The Updatelinks property is different from the UpdateLink method.
From the UpdateLink help:

---------------------------------
UpdateLink Method
See AlsoApplies ToExampleSpecificsUpdates a Microsoft Excel, DDE, or
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/OLE
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
---------------------------------
but I haven't tried it.


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194950

http://www.thecodecage.com/forumz

0
p45cal
4/15/2010 4:56:59 PM
Reply:

Similar Artilces:

Listbox
Bonjour, Est-il possible, dans un ListBox � plusieurs colonnes, de modifier l'alignement des donn�es. (ex. Les colonnes nombres align�es � droite et les colonnes texte avec l'alignement � gauche)? Merci pour votre aide Charles ...

Fast query but slow listbox
Hi everybody. I have a query that works on linked tables from two distinct backends (SQL Server and MySQL) and is very fast to fetch data, about 2" to be executed. In a form this query act as rowsource for a listbox. The problem is that when I try to open the form, it takes about 20" to populate the listbox. There are 7 columns. I've made several test and I'm quite sure the problem is in the GUI that manages the listbox. Watching with task manager and a network traffic monitor the form opening process, in the first 2" since the double click on the form the network tra...

A macro listbox in the quick launch area
I have a macro that I wrote way back in word 2003 that added a listbox of mine to a menu and the user could click on the listbox and make their choice. In word 2007 since I can’t seem to add the listbox to the Quick Launch Ribbon, I have added “Add-In Toolbar Commands” to the Quick Launch, but that adds a level of indirection to get to the listbox (click on the Toolbar Commands” and then click on the listbox). Is there a way I can add the listbox to the Quick Launch by hand (in Normal.dotm) such that the user can click on it and get directly to the list? Thanks for any help. ...

listbox
[Excel 2003] I use listboxes to display tables of data. Is there a way to adjust individual column widths such that one can make the columns fit - some of my columns need to be narrow and some wide. Thus far I have only been able to make the first column width adjusted (ColumnWidth Property) to what i want, but the others seem to be set to a predefined default width. Surely one can use code to adjust all the widths?? Roger To manually adjust the columns of a listbox in the properties window, if you have three columns: ColumnWidth | 20, 40, 25 Would set column 1 to...

updating a listbox 05-24-07
I'm having trouble with a pair of multilist boxes. The first (NameListBox) provides parameters for a query that updates on click. The second multilist box (List136) is populated by the results of the query. My problem is that the second list box does not refresh when I requery with additional parameters. I have been able to accomplish this by giving the second list box a GotFocus Refresh command, but I would rather have it automatically refresh each time the query is run - it's just too clunky without an automatic refresh. I'm including the two sub routines. I'm s...

Blank spaces in a listbox
Hi all, I've got a userform with a listbox. In the properties, I've selected the row source & it works as expected. My problem is when I delete an item out of the middle of that list, the listbow shows a blank space instead of skipping it. I've looked at all of the properties & there doesn't seem to be one that works on this, so I tried assigning the row source range in VBE, but I kept getting a message that the object was already in another module. Does anyone have an efficient way to fix this? Thanks -- Soundman ------------------------------------------------...

Filter Parent Form by Subform's Listbox Selection
Hi, I'm at my wits end with this. Could someone please help out? I have a MainForm which contains a SubForm. The SubForm contains a Listbox (single select, not multiselect). I can return the value selected in the SubForm Event OnClick. The MainForm is bound to a table as it's RecordSource. I would like to take the value selected in the listbox on the SubForm and use it to filter the results on the MainForm. My Current SubForm Event OnClick Code: Private Sub StuffList_Click() Forms![FM_TB_MainForm]!TestStuff = Me.StuffList 'Returns selected item to TextBox on Main...

Out of control listbox height setting
I have a listbox on an Access 2007 form with a height setting of two inches. In form view the listbox displayed is four or five inches in height. No matter what I set the height value to the form displays a height that extends to within about a half inch of the bottom of the form. Does anyone have any idea on what might be causing this behavior? TIA, Ken Warthen kenwarthen@gmail.com ...

unbound listbox... how do i make the current record selected
I have a listbox that displays the name of all the records, as I use the navigation buttons to go through different records my listbox does not update what it has selected. My listbox is unbound and here are some things you might want to know: Row Source: SELECT InspectionsDI.ID, InspectionsDI.[Manhole Number] FROM [InspectionsDI Query]; Private Sub lstDI_AfterUpdate() DoCmd.GoToRecord acDataForm, "DropInletForm", acGoTo, lstDI.ListIndex + 1 End Sub Thanks for help "dannie" <dannie@discussions.microsoft.com> wrote in message news:F12F5D5C-85...

Problem with click and copy from one listbox to another
Hello, I have two list boxes, List27 and List29. List 27 is a multiselect. The following code is associated with List27 and runs whenever an item is clicked in List27. I had hoped the following code would do the following. When an item is clicked in List27, it is added to List 29 If the item is clicked again in List27 it is removed from List 29 What is actually happening is that items are added to List29, sometimes two at a time, and nothing is removed. What do you suggest? Thank you, Keith code follows: Private Sub List27_Click() Dim ctlSource As Control Dim a...

Cascading Listboxes feeding off each other
I have 2 Multi-select list boxes on a form to filter a report, 1 for customers, one for lots. The filter works just fine, and I finally have each set up so that when you change 1, the other reflects only items assocaited with your selection(s) (select a customer or customers, it shows only lots bought by your selection, select lot or lots, shows only customers that bought your selection, there are other date and plant selections that feed in as well, but for now thats not relevant). My issue is that the automatic requeries that happen when one list gets updated from the other...

Mousedown event for listbox on userform disables multi select
Hi, I have a listbox on a userform with its property set to MultiselectExtended. I should therefore be able to select multiple items from the list with the Ctrl button pressed down. When the userform initiates, I ensure all items on the list are selected. However, I have added a mousedown event to the list, which deselects all items, so a custom selection can be made. The problem is, I can no longer select multiple items with the Ctrl button pressed (I can select multiple contingous items with the shift button pressed down). Any idea why this might be? Regards, JVLin ...

A97 ComboBox Rowsource (Continued)
Hi Folks as a follow up from the help I got yesterday regarding the proper opening of the ComboBox: Ahh - thanks for help guys but it seems there is another part to my challenge. I have the combo loading successfully now (thanks Marshall), but I am loading other text box's based on the other columns in the rowsorce for the combo - so eg: =[cboMatTypeDesc].[Column](5) loads the text field with data from column 5 of the combo (or 6 in fact as its 0 based) rowsource (or should). However this is working sporadically. I have 35 or so fields being populated in this way and many of them are...

Problem with printing listbox bigger than one page
Hi, I created form "Invoice". It contains few list boxes (like: Brand, Product, Price, etc.). Each list box is big enough to display 50 items. It works in this way: user is chosing product from table Products_tbl and clicks on "Add to invoice" button which adds to each list box new item containg apropriate information about chosen product. At the end, user can print the form. Before it was limited, and user could put max. 50 products on one invoice, but I wanted to get rid of this limitation. I did it in this way: after 50th item, with each added product I extend list boxe...

question regarding listbox
Hi all, In my listbox whenever the line exceeds the size of listbox, i am unable to see the line completely. Some of the part is hidden. Now i dont want to include a horizontal scroll bar to it but want the list box to wrap around and start from the next line if the length is greater than size of listbox. Is there a property which would help me enable this?? Kunal On Mar 2, 1:54 pm, kunal s patel <kunalspa...@discussions.microsoft.com> wrote: > Hi all, > > In my listbox whenever the line exceeds the size of listbox, i am unable to > see the line completely. Some of the...

Listbox selected count
I have a form with a multiselect listbox. I also have a textbox that gives me a count of the items on the list using: Controlsource.....=lstbox.listcount I would like a textbox next to that one that gives me a count of the items selected updated as i select items. Is this possible? If so please help. I just can't seem to figure it out. Thank you in advance. > Is this possible? =A0 Ofcourse it is possible. Just enter in Click event of Your list box following code: Me.txtCountSelected =3D Me.MyListBox.ItemsSelected.Count Hope this helps. Regards, S...

WPF using Usercontrol in Listbox
I sent a previous post about binding to a usercontrol in a listbox / listview. I had assumed that I could use a usercontrol in the itemtemplate of a listbox but NO No NO (apologies to Amy Winehouse). When I attempt to use the usercontrol I get a compile error: Error 10 The tag 'RFolder' does not exist in XML namespace 'clr-namespace:Media_Player;assembly=MediaPlayer'. Line '1' Position '143'. C:\Visual Studio Projects\Media Player\Media Player\Windows\ArtistBrowswer.xaml 42 17 Media Player Now intellisence guides to add the control and if I...

Filter Listbox
I have a listbox that contains two columns Date and Quantity above the listbox I have a two text boxes one showing the current month and one showing the current year. I also have two command buttons for each textbox i.e. to increase/decrease the month or year field. In the query for this listbox I've set the criteria for the date field to "Month([Date])=Month(Now())" and when I run the query all records for the current month are displayed. Now what I want to do is when I click on the command button to set the month back to March I want the records to filter so that only the enti...

Add a ListBox to the QuickAccessToolBar
I have a macro that created a ListBox. Since I can’t seem to add the ListBox programmatically to the Quick Access Toolbar, is there a way I could add the ListBox by hand to the Quick Access Toolbar so it is always there when I open Word? JeffG wrote: > I have a macro that created a ListBox. Since I can't seem to add the > ListBox programmatically to the Quick Access Toolbar, is there a way > I could add the ListBox by hand to the Quick Access Toolbar so it is > always there when I open Word? No, you can't. How about creating a UserForm (http://gregmaxe...

Disable listbox items...
I have created a program which contains a listbox with 100+ items. I want to distribute a sample program by disabling approx 90% of the items in the listbox (but allow the potential user/buyer to see them). I have searched the newsgroups and understand I need to create an owner drawn listbox but could not find any sample code that made any sense. Could someone enlighten me with some sample code? Thanks, RAB_Missouri Do it as owner-draw, and draw the disabled items grayed out. Then if the user clicks on one, or double-clicks on one, refuse to deal with it. To do this, you will probably pu...

Copy from ListBox to TextBox
Hi I want the copy to have a new line for each entry but I am stuck can you help. Private Sub Command2_Click() Dim i As Integer If List1.ListIndex = -1 Then Exit Sub For i = List1.ListCount - 1 To 0 Step -1 If List1.Selected(i) = True Then txtHold.Text = txtHold.Text & List1.List(i) End If Next i List1.Visible = False End Sub I tried VbCrLf but that did not work. Ron =?Utf-8?B?TG9uZG9uTGFk?= <LondonLad@discussions.microsoft.com> wrote: > txtHold.Text = txtHold.Text & List1.List(i) > >I tried VbCrLf but th...

Creating Chart from Userform ListBox selections
This is a multi-part message in MIME format. ------=_NextPart_000_0016_01C911A4.100F32B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have created a Userform with 2 Listboxes on it Listbox1, is populated with ALL values in Sheet3 Column B Listbox2, is populated with ALL values in Sheet3.Column A Column B values are Names Column A are Dates Both Listboxes have Multi-Select enabled. I am trying to set up a chart in Sheet6 with the data from Sheet3, but = am up to the stage of how to create it. Sheet3 data is stored in rows, and eac...

Listbox Scroll Bar
I'm working with AC2003. I have a listbox on a form that gets populated via a query. It loads it find. However, when there are more entries than can be displayed, it automatically displays the vertical scroll bar, which I would expect. However, when I click on the scroll bar to scroll down to see the hidden entries, it highlights the first entry in the listbox and does not scroll down. The listbox is enabled and locked. I want users to see but not select the entries, all of the entries therefore the need for the vertical scroll bar. What am I doing wrong? Thanks in ...

filter on a listbox
Is it posible to activate the same filter at a listbox, that i use at my form? ex. activate filters as form Use the same query as the source for both the form's recordsource and the listbox's rowsource. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "david" <david@discussions.microsoft.com> wrote in message news:8FEBDDD7-D2FC-4D3B-8FEC-E829FF117C43@microsoft.com... > Is it posible to activate the same filter at a listbox, that i use at my > form? > > ex. activate filters as form ...

MFC: Virtual ListBox control on a Property Page
Hi, I have a PropertySheet with two PropertyPages. One of the PropertyPages contains a ListBox control with LVS_OWNERDATA and LVS_REPORT style. NOTE I ----------- I added the following piece of line in the Data Exchange Function: "DDX_Control(pDX,IDC_LIST,m_list);" where m_list is the object variable of type "CListCtrl" All this works fine. NOTE II ------------ Now, I try to derive a class (using MFC class wizard) from CListCtrl i.e. class CMyListControl : public CListCtrl { //.... }; and declare an object variable like this in the Property Page's header file. CM...