Pass Variable between Form and Sheet code in Excel?

[Geneticus]

I am having trouble passing a variable between a Userform and Code contained within a sheet. I have built a workbook that is used by our training department for gathering and storing all data relevent to a class(attendance, scoring, Logins,etc.) One of the things I am doing is pulling data from the request form that IT uses to create NT logins. The problem is it only stores new employees. So, I may have to search more than one workbook to get all of the data for my class. I am using this data to Issue Login Sheets to each employee with thier account names so I don't have to explain verbally all of the different tool's Login formats.

What I have done is create a button on a sheet that starts the search process then launches a userform with a Yes/No Question. No Drops the form and returns execution to the Sheet's Sub If all of the Logins are found on the same sheet. I want the YES button to launch a different Sub within the Sheet's Code as well as hiding the form which searches all of the remaining forms for employees that may have started in a different class.

I have tried Setting a Global variable in the Form Code and Sheet code, as well as trying to catch the click but it is not passed back to the Sheet code. I have also tried initating the Sub from the form, but I get a SUB OR LABEL NOT DEFINED error. I am Using Excel 2k
From the Form:

Private Sub CommandButton1_Click()
UserForm2.Hide
CheckAllNames
End Sub
Private Sub CommandButton2_Click()
UserForm2.Hide
End Sub

 

[Zathras]

This is similar to thread707-963941 (at least the first few posts). The idea is to use global variables in a code module.

 

[Geneticus]

Not much in that thread I didn't try first.
After some playing around I discoverd something more confusing.
Instead of recording the click I made a Global variable again. If declared in either the Form or the Sheet, the data would not pass. Declared in both, the data would pass but it was flipping the value of the boolean.

Public ALL As Boolean

Public Sub CommandButton1_Click()
ALL = True
UserForm2.Hide
End Sub
Public Sub CommandButton2_Click()
ALL = False
UserForm2.Hide
End Sub

In the Sheet

Public ALL As Boolean
Sub NewGet()
If ALL = True Then CheckAllNames
End If

End Sub


If I selected button 1 the value passed was False
If I selected Button 2 the value passed was True
I checked the properties to make sure I didn't have the Buttons reversed.
Setting Both to True resulted in a false value in the sheet and vice versa.

 

[Geneticus]

Bah! I want to edit after posting....

I resolved it by moving all of the code into the UserForm, but I would still like to know what is happening since I have another project that will pass data from a form into various cells in several sheets.

 

[Zathras]

Geneticus, I'm sorry, but you aren't getting the results you think you are.

By defining a variable ALL in both sheet code and form code, you are defining 2 separate variables. Each variable only has scope in its own module (despite the declaration "Public").

In order to have both the sheet code and the form code refer to the same variable, it must be defined in a third module, not a form, nor a sheet. Alternatively, both your form code and your sheet code could update the same cell in the worksheet, if you wish.

To see what is going on, open the "Watches" window (From the VBA menu: View/Watch Window) and right-click to add a watch. Use ALL as the expression and select Sheet1 as the Module. Then right-click again and use ALL as the expression and select UserForm2 as the module. Now, as you execute the code, you can see that the behaviour is quite different depending on where you are. It is possible to select "(All Modules)" as the Module, but try it and see what happens (or rather doesn't happen).

In any case, defining the same variable in multiple places is not a good programming practice. It can only lead to confusion at best, or unpredictable results at worst.