You don't seem to understand the suggested solution. Go into the vbe and
for each userform you see, click on the x to close it.

Now go back to excel and try to run your code.

Does this solve the problem?

If not, then you left with recording the value somewhere. This shouldn't
be a lot of work because you shouldn't have more than a couple of global
variables.

--
Regards,
Tom Ogilvy


"vbaprog" wrote in message
...
Hai,

Thanks again. My application needs the form to pop up while running
the
macro. That is the problem. I am now trying to save the values of all
variables in a file before activating the form and then read them back
from
the file when needed. Or I shall put the values in some unused cells and
then
read them from there later. Both these approaches will be complicated.
Please
let me know if you have an easier solution.

With Regards,
vbaprog

"Tom Ogilvy" wrote:

Per the referenced article:

RESOLUTION
To resolve this problem and ensure that all global variables retain
their
scope, close all UserForm windows before running a macro in a Visual
Basic
project.

--
Regards,
Tom Ogilvy

"vbaprog" wrote in message
...
Hai,

Thank you for the answer. Is there some way to regain/retain the
value
of variables after returning from the form to the excel sheet?
Thanks in advance for any help.

"Tom Ogilvy" wrote:

http://support.microsoft.com/default...b;en-us;169621
OFF97: Displaying UserForm Designer Resets Global Variables

Believe this is only in Office 97.

--
Regards,
Tom Ogilvy



"vbaprog" wrote in message
...
I have a strange problem with Excel 97 (I think this
problem is
existing in the later versions also). Here is a simplified version
of
what
I
did.

I created three command buttons in sheet1 and a userform in
the
project. When I display a form and then come back to the xlsheet,
the
values
I assigned before for all variables are lost.

I wrote the following code:

Standard module:
Public x as integer

Sheet1:
Private Sub CommandButton1_Click()
Load UserForm1
End Sub

Private Sub CommandButton2_Click()
x = 4
UserForm1.show
End Sub

Private Sub CommandButton3_Click()
MsgBox x
End Sub

Userform1:
Private Sub Userform_Click()
UserForm1.hide
End Sub

This is what I do when I run the program:

I click buttons 1 and 2. Userform1 is displayed. When I click
the
form,
it disappears. Then I click button 3. Now the messagebox displays
the
value 0
for x. What happened to the value I assigned to x in the click
procedure
of
button 2?

If I remove the 'Userform1.show' command from the the click
procedure
of
button2, and repeat the above steps, I get the value 4 for x
displayed
in
the
messagebox. Can anyone explain? Strange isn't it!