Disappearance of assigned value of varaible when working with form

[vbaprog]

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!

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!
>

[vbaprog]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

[vbaprog]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
> >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> > >
> >
> >
> >

[vbaprog]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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!
> > > >
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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!
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[vbaprog]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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!
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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!
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[vbaprog]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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!
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>