referencing controls in userform from a different workbook

I want to be able to call up the same subform from a number of different 
forms in different workbooks.
I have "customers.xls" with a macro to show a form that lists all the 
customer names.
The user can be using various forms in different workbooks that need the 
exact customer name. So with Application.Run and refernce to the workbook and 
its macro I can open the form.
When the user double-clicks an item in the listbox the value should get put 
into a textbox on the form that called the subform.
I'm missing the final stage - how do I reference the target textbox so that 
the data will go where I want it?

-- 
WinXP - Office2003 (Italian)
0
Utf
5/28/2010 7:32:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
733 Views

Similar Articles

[PageSpeed] 39

Reverse the path you used to get to the list box that is clicked.

Workbooks("Customers.xls").<userformname>.<controlname> = 
<listboxname>.value

The form needs to be in show mode when you do it or the control cannot be 
accessed.


"David Macdonald" <DavidMacdonald@discussions.microsoft.com> wrote in 
message news:731897E8-D668-4CC5-8F62-98071C17CFDC@microsoft.com...
>I want to be able to call up the same subform from a number of different
> forms in different workbooks.
> I have "customers.xls" with a macro to show a form that lists all the
> customer names.
> The user can be using various forms in different workbooks that need the
> exact customer name. So with Application.Run and refernce to the workbook 
> and
> its macro I can open the form.
> When the user double-clicks an item in the listbox the value should get 
> put
> into a textbox on the form that called the subform.
> I'm missing the final stage - how do I reference the target textbox so 
> that
> the data will go where I want it?
>
> -- 
> WinXP - Office2003 (Italian) 


0
JLGWhiz
5/28/2010 7:13:00 PM
Thanks for the response but I'm still unable to get it to work. I have things 
set up like this:
workbook: Customers.xls
userform: FindCustomerForm
control: CustomerListbox

The first workbook I want to have opening the form and getting info from it 
is "Orders":
workbook: Orders.xls
userform: NewOrderForm
control: CustomerName
Double-clicking in the CustomerName textbox opens the FindCustomerForm. I 
find the right customer and double-click the listbox item.
At that point FindCustomerForm should transfer the value to CustomerName on 
NewOrderForm then unload itself.

If the two forms are in the same workbook I can get this to work perfectly, 
using a public string "ThisControl" to retain the name of the control that 
called the customer list for example:
Private Sub CustomerName_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ThisControl = "CustomerName"
    FindCustomerForm.Show
End Sub

and

Private Sub CustomerListbox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    NewOrderForm.Controls(ThisControl).Value = CustomerListbox.Value
    ThisControl = ""
    Unload Me
End Sub


My problem is that I want to call FindCustomerForm from Complaints.xls, 
Shipments.xls, and ChristmasGreetings.xls too.

So how do I construct the general purpose string (including network path) 
that will get the customer name to whatever control called it?
Do I need to put "Public ThisControl As String" in both workbooks?


-- 
WinXP - Office2003 (Italian)


"JLGWhiz" wrote:

> Reverse the path you used to get to the list box that is clicked.
> 
> Workbooks("Customers.xls").<userformname>.<controlname> = 
> <listboxname>.value
> 
> The form needs to be in show mode when you do it or the control cannot be 
> accessed.
> 
> 
> "David Macdonald" <DavidMacdonald@discussions.microsoft.com> wrote in 
> message news:731897E8-D668-4CC5-8F62-98071C17CFDC@microsoft.com...
> >I want to be able to call up the same subform from a number of different
> > forms in different workbooks.
> > I have "customers.xls" with a macro to show a form that lists all the
> > customer names.
> > The user can be using various forms in different workbooks that need the
> > exact customer name. So with Application.Run and refernce to the workbook 
> > and
> > its macro I can open the form.
> > When the user double-clicks an item in the listbox the value should get 
> > put
> > into a textbox on the form that called the subform.
> > I'm missing the final stage - how do I reference the target textbox so 
> > that
> > the data will go where I want it?
> >
> > -- 
> > WinXP - Office2003 (Italian) 
> 
> 
> .
> 
0
Utf
6/3/2010 11:42:32 AM
Reply:

Similar Artilces:

using common control version 6 in VC6
Hi all, I'm trying to use the common control version 6 in my MFC project, I've imported the manifest.xml adn specified 6 as the platform, but I still can't use the version 6 members of the NMLVCUSTOMDRAW structure in my custom drawn controls. for instance, when I tried to use clrFace of the structure, it would say 'clrFace' : is not a member of 'tagNMLVCUSTOMDRAW', and I've traced the problem to the definition of the strucfure itself, typedef struct tagNMLVCUSTOMDRAW { NMCUSTOMDRAW nmcd; COLORREF clrText; COLORREF clrTextBk; #if (_WIN32_IE...

How to search multiple worksheets in a workbook for information?
Let's say I have a workbook containing multiple worksheets, each a separate materials requisition. I want to find out when (and details) I last ordered a particular item. Other than selecting each worksheet one at a time and then using "find," is there a search that would look through all the worksheets in that particular workbook? Download Jan Karel Pieterse's FlexFind. You can find it here: http://www.oaltd.co.uk/MVP/ medic2816 wrote: > > Let's say I have a workbook containing multiple worksheets, each a separate > materials requisition. I want to fi...

Database Size difference after execution
I have recently converted an Access 97 application to Access 2002, simply put all the application does is read in a datafile, run calculations, and output a new datafile. What I have noticed in testing the conversion is that at the end of the execution the Access 2002 database is nearly 3 times the size of the Access 97 database running the same execution. Once a manual compact is done the Access 2002 database is much smaller however during execution is is exceeding 2GB in size depending on the input file compared to 800MB with the Access 97 version. My question is if anyone has a...

Breaking One workbook into many Tabs
Hi There I have a tab called "Master" with information on lines 2 to 130, and Columns A to S. Column F has information that is repeated on several lines (persons name) I want to break the single tab "master" into a number of sub tabs, each tab containing the information relevant only to the individual. Thus for example Rows 1, 4, 7 and 9 within Column F state John. I want to extract all relevant info for John (ie contents of Rows 1,4 and 7) into a new tab called John (such that I can send JOhn his tab) Is there a way to break up the Master tab into sub ...

Control Tab for Combo box
Hi, I have created a combo box in Excel 2002. When I right click and select Format Control the Control tab is missing. Any ideas about how to fix this? You created a combobox using the controltoolbox toolbar. The "control tab" doesn't exist for this combobox. But if you use a dropdown (aka combobox) from the Forms toolbar, you'll be one happy camper again. Ruth wrote: > > Hi, > > I have created a combo box in Excel 2002. > When I right click and select Format Control the Control > tab is missing. > > Any ideas about how to fix this? -- D...

Units of Measure in Edit Controls
Is there a good way for me to format data in an edit control by that control's unit of measure? For example: CString data = foo; FormatUnits(data, IDC_VALUE_CTL); c_value.SetWindowText(data); In FormatUnits(), of course, I have a basic problem: how do I determine how to format "data" by knowing my control ID? In the COM world, I'm used to creating and storing a custom property that I can access later. Is there a way to do this with MFC? Or is there a "best practices" way of doing this and I'm barking up the wrong tree? Seeing how no one has replied yet,* ...

Docking Multiple Control Bars on the same side
Hello, I am trying to initiate my application custom made ControlBars docked on the left of the frame but I am not getting the right result: To illustrate what I want to do picture the Microsoft Visual Studio enviroment. Normally you start with a few Control bars docked to the side of the frame. Mine for instance has the "Class View" and then just below the "Properties Window" , they are both docked to the same side one above the other. My problem is iamigne if when you started your MS Visual Studio instead of having both controlBars one above the other, you would...

correct formulation of expression in control source
How can I combine 2 expressions that use the DCount function? For example, in control source, =DCount("[Field1]","qryX","[Field1]=True") will give me a value Y which I need to add to another value obtained by =DCount("[Field1]",qryZ","[Field1]=True"). I have tried various bits such as =DCount("[Field1]","qryX" And "qryZ","[Field1]=True") but that does not work. Can anyone help? Regards Have you tried =DCount("[Field1]","qryX","[Field1]=True") + DCount("[Field1...

What is differeence in opening a feature from the Outlook Bar or Folder List
in Outlook 2002. Seems extremely redundant. TIA normc wrote: > in Outlook 2002. > > Seems extremely redundant. > > TIA > I just noticed that one can also open some of the features from VIEW | GO TO, but the set available is not the same as in the Outlook Bar or the Folder List sets. Is this some kind of reduncancy again? TIA Nothing. Some people prefer to keep the Folder List visible, others the Outlook Bar. You choose which you prefer. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, a...

Changing a control on a subform?
I am trying to turn some controls in a subform on or off by using a check box on the main form, can�t get it to work Here is the code on the check box click event Private Sub ckShowAWIPrice_Click() If Me.ckShowAWIPrice = -1 Then Me!frmDataSubform.Form!txtStandardBond.Visible = True Me!frmDataSubform.Form!txtStandardNet.Visible = False End If End Sub i'm guessing that "frmDataSubform" is the name of the form (that you're using as a subform) as it shows in the database window. is it also the name of the *subform control within the main form*? your expression has ...

Sending email via a different program
Outlook 2002 prevents programs from accessing your address book or sending mail on your behalf. When such actions occur, Outlook notifies you and gives you the option of choosing to continue with the action. I want to know how to get rid of this feature or type of security so I don't get any knid of message asking permission. I want Outlook to just send the email regardless of what program I'm using. See if the information on the following page helps: http://www.slipstick.com/outlook/esecup.htm#autosec -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address ...

3 Installs on 3 Different Computers?
I installed Microsoft Office 2004 (Student Edition) on my iBook when I purchased it, then reinstalled it after some slowdown. I used two different access keys...does this mean that I have installed it on two different computers? Can I use the access key that I used during the first install again, or will I have to use the third key? The reason I ask is that my family's going to get an iMac and I will install it on that, but then when I get a new laptop will I be able to install using one of the first two access keys? Thanks. You could have reinstalled with the same key, that you used ...

Merging Multiple documents having different headers and footers ma
Dear All, I am trying to merge a several word/rtf documents into a single word documents with the below macro, the problem is now each document is having a different header and footer and some fields, Can anyone suggest me a macro code for merging multiple word documents into a single document without disturbing the headers and footers of each document? Sub MergeDocs() Dim rng As Range Dim MainDoc As Document Dim strFile As String Const strFolder = "c:\tes\" 'change to suit Set MainDoc = Documents.Add strFile = Dir$(strFolder & &quo...

Referencing a worksheet name
Dear All, I am creating a generic workbook. Each worksheet of which relates to a member of staff. An example of the sheets is Summary, Name 1, Name 2, Name 3. I would like to define cells in the summary worksheet so that the value returned is the name of the sheets. So if sheet "Name 1" is renamed "Bill Smith" Bill Smith appears in the relevant cell in the summary sheet. I hope this is clear and if you could help i would be grateful. Many thanks, Danny Take a look here: http://www.mcgimpsey.com/excel/formulae/cell_function.html Also, see David McRitchie'...

Linked cells in Multiple Workbooks
I am tracking dates and have linked cells from another workbook so as t not have to duplicate the data entry of dates. The dates are linked t projects that are listed in rows and there is information in m workbook that is additional information within the row. So if someon changes the order of the other workbook, the information that is in m workbook does not get sorted with the order. Is there a way to link m cells to the linked cells? ie. Project ID Start Date Milestone EndDate Person Assigned 1234 (linked) 11/15/04 11/20/04(lin...

How to open workbook without hardcoding path
Hi Could anyone tell me how to open a workbook from another workbook, where both files are in the same folder, without hardcoding the path. I have tried code like this without success. Workbooks.open (path & "\somefile.xls") Thanks in advance Mick Excel automatically looks in the current folder, so :- Workbooks.open "somefile.xls ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

limking workbooks
I have 4 workbooks in seperate folders that I need to combine into one monthly summary. The workbooks are different but each contain a columns named, B/L,Amount,Billed. These are the 3 columns I want merged into one spreadsheet. What would be the best way to do this? Thanks KJH copy the column(s)(or cells) that you want to summarize and paste special>paste links to the destination column(s) in your summary worksheet.each time there is a change in your workbooks the summary sheet updates too.Once you have the cells on your sheets you can organise them how you like,ie you can have...

why are my pages displaying in different widths?
I have my documents in landscape, but halfway through the document, the pages switch to a narrower landscape format than the rest. You must have a Section Break in there somewhere with different page setups for some of the Sections in the document. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "woelke" <woelke@discussions.microsoft.com> wrote in message news:581F1425-8EAB-4D50-96E8-6E0472389A0D@microsoft.com....

Security problem with controls in worksheet
I've built a worksheet that contains a few controls (check box). I need to protect this worksheet, but maintain access to these check boxes. I've set the security to unlock on the cell in back of my control and also my control is unlocked but I always get and error saying that my worksheet is locked when I check or uncheck my control. Can someone help me out on this one! Thanks alot. I'm afraid all you can do with the Forms' check boxes is in the Format Control. I don't know of anyway to change the font size or color of the text. For more features you will have to ...

How to refresh all Cells in an Excel Workbook automatically?
I have an Excel Workbook that is Shared and it uses the Vlookup function to look up values in another Excel Workbook. Both files are stored in a Network drive and I use \\server_name\shared_folder\filename.xls format to reference the file in the first Workbook. Problem: Upon opening the Shared Workbook, it does not always display the correct Cells values until I manually press F2 and Enter on each of the wrong-value Cell. Is there a way to refresh all the Cells' value without manually pressing F2 and Enter on the affected Cells? Maybe Edit|Links|Update values Or even selecting all...

vc6 using SSTab control
Hi, Is there a way to use this control so that each tab holds a variety of buttons,labels,etc? When I add them similarly to adding in VB, they appear when project is first run but not after leaving/returning from/to the tab where I thought (!) that I placed them. thanks, J No. Control Containment such as this is a VB concept and relies on the control actually running at design time so that it can be hidden / shown... The closest you will get to this is by using Dialog resources with controls within them. Dialogs can be used as Child windows so their visibility can be switched at run t...

One link breaks every time I open a workbook...
I have 3 workbooks tied together with links. One is a source only, the other two push and pull data. One of the sources, Payroll Master, on opening updates all links fine to the source only workbook but not to the third workbook (which is in the parent directory). Every time I open it it prompts me to show it where the Payroll Entry is. Doesn't matter if I hit cancel and do Edit Links, I can change it there, but will still lose it upon closing (the link appears in the box and looks ok, just won't update...) Try going Edit->Links. Break the link and re-establish it? That m...

change all controllers' font in a dialog
Env: WindowsXP, VC+6.00 There are any easy ways to change all controllers' font in a dialog(NOT one by one)? TIA William If you mean to change font for the controls in a dialog, you can enumerate over all the children of the dialog and call SetFont on each of them(Use EnumChildWindows). Another option is to use SendMessageToDescendants. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "William" <port@mx15.freecom.ne.jp> wrote in message news:u0W39WplHHA.4848@TK2MSFTNGP05.phx.gbl... > Env: WindowsXP, VC+6.00 > > There are any easy ways to change all contro...

Tab order of dynamic controls
Hi All, I am creating 3 group controls(frames) and 3 edit controls dynamically but having trouble setting their tab order. Apart from these control I have a command button control which was created at design time using resource editor. i want to move from edit control 1 to edit control 2 then edit control 3 and finally in command button. After command button it should go back to edit control 1. Here's the code code that i'm using(focus doesnt go to any edit control...). Any idea would be greatly appreciated. int iTop = 2; int iBottom = 50; for (int i=0;i<3;i++) { m_objGroup[i]...

Cell referencing on a chart
I would like to be able to reference a cell in a textbox on a chart. Ex. Cell A1 value = "12" Text box on chart = "12" I need to be able to change cell A1 and have the textbox change with it. Please give me advice on how to do this. Thanks. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ 1) Click on chart to activate it 2)In formula bar, type = 3) Use mouse to point to a cell (formula now reads =Sheet1!A1 4) Click green check mark in Formula Ba...