Solved

How do you pas a variable between forms in excel VB

Posted on 2004-08-30
16
137 Views
Last Modified: 2010-05-02
I am trying to pass the value of a variable betweens two forms in excel's vb. How do you do this?
Thanks
whiwex
0
Comment
Question by:whiwex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 11938634
Try create a Module, then declare your variables As Public there.. like:

In the module:

Public myVar As String
...
0
 
LVL 10

Expert Comment

by:anv
ID: 11938679
while u open a form in VBA..
u have one parameter in the docmd.openform frmname command..
where u can pass opening parameters... there u can pass the variable and in the other form retrieve the value of the parameter using openArgs method of that form...

e.g.

'from form1 one i want to pass the current id to form2
docmd.openform form2,,idVal

'in form2
dim id1 as string

id1=form2.openArgs
0
 

Author Comment

by:whiwex
ID: 11938750
it doesn't like the openArgs
whiwex
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:whiwex
ID: 11938768
Here's what I have
Userform1
docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm1.openArgs
0
 
LVL 10

Expert Comment

by:anv
ID: 11938787
why r u using the statement UserForm2.Show

the statement docmd.openform UserForm2, , row is enough.

to open the form
0
 

Author Comment

by:whiwex
ID: 11938829
I removed the userform2 show.  It still dies on the arg. It's to a compile error: method or data member not found.
0
 
LVL 10

Expert Comment

by:anv
ID: 11938858
use following

docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm2.openArgs
0
 

Author Comment

by:whiwex
ID: 11938879
it still errors on the openarg. It doesn't knowmthis command.
whiwex
0
 
LVL 10

Expert Comment

by:anv
ID: 11938915
here's the correct  syntax

'stDocName stores the name of the form to open
DoCmd.OpenForm stDocName, , , , , , "openArgs"

in form load event of the other form use

row1=form2.openargs

hope this time it will work
u r working in VBA right?
0
 

Author Comment

by:whiwex
ID: 11938943
it doesn'tlike the stdocname command.

i am writing a macro in excel. I have the excel visual basic editor opened and I am adding this code to the two forms I created. both forms work except for trying to pass value of row from one form to the other.
whiwex
 
0
 

Author Comment

by:whiwex
ID: 11938950
in form1 it looks like this.

DoCmd.OpenForm stuserform2, , , , , , "openArgs"
UserForm2.Show


inform two it looks like this

Public Sub UserForm_Initialize()
Dim row1 As Integer
row1 = UserForm2.Openargs
UserForm2.TextBox6 = Date
UserForm2.TextBox7 = Time
End Sub

thanks
0
 
LVL 10

Expert Comment

by:anv
ID: 11938976
whiwex
stDocName is not a command..
its just a variable that stores the form name to open..
read clearly my last comment..

0
 

Author Comment

by:whiwex
ID: 11939022
in form1 it looks like this.

DoCmd.OpenForm tuserform2, , , , , , "openArgs"
UserForm2.Show


inform two it looks like this

Public Sub UserForm_Initialize()
Dim row1 As Integer
row1 = UserForm2.Openargs
UserForm2.TextBox6 = Date
UserForm2.TextBox7 = Time
End Sub

it errors on the openargs. the error says method or data member not found
whiwex

thanks
0
 
LVL 10

Expert Comment

by:anv
ID: 11939554
whiwex

in this case, now, the best method would be to declare a global variable in a module..
from UserForm1 store the value in that variable

and from UserForm2 fetch its value on form load or initialize..
0
 

Author Comment

by:whiwex
ID: 11941246
thats what I have been trying to do. i can't get it to work.
how do you do this?
what are the commands because everything i try fails to pass the variable
whiwex

0
 
LVL 10

Accepted Solution

by:
anv earned 250 total points
ID: 11948951
open a module, if one exists, else add a new module
in the module declare a varaible as public,
say if i want name that variable as glbParam then in the module declare it as

public glbParam as string (or whatever datatype u want to provide)

in form1 before u open form2 add a statement to pass the value to this varaible like
glbParam="Something"
userform2.show

now in userform2 u can use that variable.

its a good practice if u provide option explicit statement in all ur modules or forms..
do this and u'll get the desired result..
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using "ScreenUpdating" 6 88
Access 2013 combo box not working 3 76
vb6 connector to SQL Server 2 42
Visual Basic 6: Code needed for TripleDES Encryption/Decryption 14 95
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question