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!