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!

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!

Hai,
Thank you for the answer. Is there some way to retain the value of
these variables after returning from the form to the xl sheet? Thanks in
advance

"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!

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!

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!

Hai,

Thanks again for pointing out the source of error. I was looking
for a solution to the problem I was facing due to it.

My application demanded a program where the activities are to be
done in a form that is nested within the macro/program. Values have to be
exchanged between the form and the program. So it is not possible to work
separately with the form and run the program from the start after closing the
form. So my question in the second post was if there is some way to get out
of the trouble. As I mentioned in the previous post, I saved the values in a
file before opening the form and I read them from the file when I need them
again in the program - A patch up. Works fine!!

With Regards,
vbaprog

"Tom Ogilvy" wrote:

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!

You still don't seem to understand the solution. The solution is not to
work with the form separately as you seem to think. The solution suggested
says to go into the VBE. In the VBE you see many windows such as the project
window, various modules and so forth. You probably also have a userform or
two visible as windows. If you go to the upper right corner of the userform
window - whether it is showing the userform or code for the userform - click
on the close button. This make the userform not visible in the VBE, but has
no affect on how your code operates. If you then go back to excel and run
your program, according to the KB article, your global variables won't be
zeroed. Again, this requires no change to how you are running your program
and if this is something you are going to distribute, would be the norm -
the VBE would probably not even be open (though you can't assume this).

There is certainly nothing wrong with recording your variables - but you
should at least understand what has been suggested.

--
Regards,
Tom Ogilvy


"vbaprog" wrote in message
...
Hai,

Thanks again for pointing out the source of error. I was looking
for a solution to the problem I was facing due to it.

My application demanded a program where the activities are to
be
done in a form that is nested within the macro/program. Values have to be
exchanged between the form and the program. So it is not possible to work
separately with the form and run the program from the start after closing
the
form. So my question in the second post was if there is some way to get
out
of the trouble. As I mentioned in the previous post, I saved the values in
a
file before opening the form and I read them from the file when I need
them
again in the program - A patch up. Works fine!!

With Regards,
vbaprog

"Tom Ogilvy" wrote:

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!

Thank you. m...I can get your point now :-)

"Tom Ogilvy" wrote:

You still don't seem to understand the solution. The solution is not to
work with the form separately as you seem to think. The solution suggested
says to go into the VBE. In the VBE you see many windows such as the project
window, various modules and so forth. You probably also have a userform or
two visible as windows. If you go to the upper right corner of the userform
window - whether it is showing the userform or code for the userform - click
on the close button. This make the userform not visible in the VBE, but has
no affect on how your code operates. If you then go back to excel and run
your program, according to the KB article, your global variables won't be
zeroed. Again, this requires no change to how you are running your program
and if this is something you are going to distribute, would be the norm -
the VBE would probably not even be open (though you can't assume this).

There is certainly nothing wrong with recording your variables - but you
should at least understand what has been suggested.

--
Regards,
Tom Ogilvy


"vbaprog" wrote in message
...
Hai,

Thanks again for pointing out the source of error. I was looking
for a solution to the problem I was facing due to it.

My application demanded a program where the activities are to
be
done in a form that is nested within the macro/program. Values have to be
exchanged between the form and the program. So it is not possible to work
separately with the form and run the program from the start after closing
the
form. So my question in the second post was if there is some way to get
out
of the trouble. As I mentioned in the previous post, I saved the values in
a
file before opening the form and I read them from the file when I need
them
again in the program - A patch up. Works fine!!

With Regards,
vbaprog

"Tom Ogilvy" wrote:

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!