Solved

How do you pas a variable between forms in excel VB

Posted on 2004-08-30
16
138 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
Independent Software Vendors: 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 15 hours left to enroll

632 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