Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

How do you pas a variable between forms in excel VB

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
whiwex
Asked:
whiwex
  • 8
  • 7
1 Solution
 
Ryan ChongCommented:
Try create a Module, then declare your variables As Public there.. like:

In the module:

Public myVar As String
...
0
 
anvCommented:
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
 
whiwexAuthor Commented:
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!

 
whiwexAuthor Commented:
Here's what I have
Userform1
docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm1.openArgs
0
 
anvCommented:
why r u using the statement UserForm2.Show

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

to open the form
0
 
whiwexAuthor Commented:
I removed the userform2 show.  It still dies on the arg. It's to a compile error: method or data member not found.
0
 
anvCommented:
use following

docmd.openform UserForm2, , row
UserForm2.Show

Userform2
row1 = UserForm2.openArgs
0
 
whiwexAuthor Commented:
it still errors on the openarg. It doesn't knowmthis command.
whiwex
0
 
anvCommented:
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
 
whiwexAuthor Commented:
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
 
whiwexAuthor Commented:
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
 
anvCommented:
whiwex
stDocName is not a command..
its just a variable that stores the form name to open..
read clearly my last comment..

0
 
whiwexAuthor Commented:
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
 
anvCommented:
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
 
whiwexAuthor Commented:
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
 
anvCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now