Two problems with userforms

[Derek Dowle]

The first relates to public variables
The second to movement between userforms

I am using Excel 2003 for a database application. The database comprises
one Worksheet and two Userforms.
The Worksheet contains the database.
Userform1 is for data entry.
Userform2 is to search the database.

Userform2 is invoked by a cmdButton on Userform1.

The search criteria is entered on Userform2 and the forms code identifies if
and where the data exists on the Worksheet.

PUBLIC VARIABLES

Public nRow As Integer is placed in the General section of the code for
Userform2.
nRow is given a value in Public Sub cmdFind_Click(). When the focus of the
database returns to Userform1 the Public variable is not passed to the code
for another Public Sub.

How do you pass a Public variable from one form to another?

MOVEMENT BETWEEN USERFORMS

When the focus returns from Userform2 to Userform1 the code in the Public
procedure which initially called Userform1 does not resume. The code is
actioned once Unload Me is run, which is of little use as the Userform1 needs
to be open to read thr record that has been found by Userform2.

I am aware that when the the focus returns to Userform1 UserForm_Activate()
is run, but I have ensured that this code only runs when the Userform is
first activated.

The code used to go to Userform2 is:
Userform1.Hide
Userform2.Show

The code used to return to Userform1 is:
Unload Me
Userform1.Show

What code is required to ensure that when the focus returns to Userform1 the
code resumes where it left off before it went to Userform2?

TIA

 

[Bob Phillips]

Place the public variable in a normal code module, not in the userform.

On the movement, why not just load form2 and then exit it

The code used to go to Userform2 is:
Userform2.Show

The code used to return to Userform1 is:
Unload Me

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Derek Dowle]

Bob

Thank you for the speedy response.

You scored a bullseye on both points.