Disappearing Public Variable

[Guest]

I declare a Variable as Public (to preserve its value throughout code
execution) eg:-
Public MyText As Variant (this is done in a Standard Module)
Then in a UserForm I have:-

"Private Sub CommandButton1_Click()
MyText = TextBox1
(The Locals Window correctly shows MyText has whatever I put in TextBox1)
Do some other things
Unload UserForm3
End Sub"

When the UserForm is unloaded and execution returns to a Standard module I
need to use the value in MyText - but in the Locals Window I see that
MyText=0.

I expected the value in MyText to be retained even though the UserForm where
it was created has been unloaded.

What am I doing wrong?

 

[Guest]

I think you should declare MyText as workbook level public variable. You can
do that - as I was instructed in this forum some month ago - by assigning a
value to it in workbook_open event sub and declare it public in a standard
module like you did it.


Regards,
Stefi

„donbowyer†ezt írta:

 

[Dave Peterson]

Did you hit the reset button in the VBE?

Do you have any "end" lines in your code--not "end if", "end sub"--just plain
old "End". This will reset these global variables.

 

[Guest]

Thanks for the inputs.

Stefi, I'm not sure how I assign a value to MyTextin workbook_open event sub.

Dave, what you are asking, suggests what I have done should work. But I
still lose the variable value when I don't press the reset button and I have
no plain "End" commands in the WorkBook.

 

[Guest]

I suppose a simple

MyText=0

will do!

Regards,
Stefi

„donbowyer†ezt írta:

Thanks for the inputs.

Stefi, I'm not sure how I assign a value to MyTextin workbook_open event sub.

Dave, what you are asking, suggests what I have done should work. But I
still lose the variable value when I don't press the reset button and I have
no plain "End" commands in the WorkBook.

 

[Dave Peterson]

I would expect your code to work, too.

Any chance you have multiple variables named myText and you're looking at the
wrong one?

Thanks for the inputs.

Stefi, I'm not sure how I assign a value to MyTextin workbook_open event sub.

Dave, what you are asking, suggests what I have done should work. But I
still lose the variable value when I don't press the reset button and I have
no plain "End" commands in the WorkBook.

 

[Guest]

Hi again Dave
This is most strange.
I've tried a very stripped down version of my code bur the problem persists.
I have a UserForm (with OK, Cancel and a TextBox) and a Standard Module.

The Module code is:-
""Public MyText
Sub MyAddSheet()
UserForm1.Show
MyHold_1 = MyText
MyHold_2 = UserForm1.TextBox1.Text
End Sub""

The UserForm code is:-
""Private Sub CommandButton1_Click()
Dim MyText As Variant
MyText = UserForm1.TextBox1.Text
Unload UserForm1
End Sub""
The module code takes me to the UserForm. I insert text into TB1 and click OK.
As I step through, the text I put in TB1 appears in the Locals window as a
value under my Public Variable MyText.
When control returns to the module code, MyHold_1 says "Empty" and MyHold_2
says "" in the Locals window.
In other words the value in the variable disappears when control leaves the
UserForm.
?????

 

[semiopen]

Hi again Dave
This is most strange.
I've tried a very stripped down version of my code bur the problem persists.
I have a UserForm (with OK, Cancel and a TextBox) and a Standard Module.

The Module code is:-
""Public MyText
Sub MyAddSheet()
UserForm1.Show
MyHold_1 = MyText
MyHold_2 = UserForm1.TextBox1.Text
End Sub""

The UserForm code is:-
""Private Sub CommandButton1_Click()
Dim MyText As Variant
MyText = UserForm1.TextBox1.Text
Unload UserForm1
End Sub""
The module code takes me to the UserForm. I insert text into TB1 and click OK.
As I step through, the text I put in TB1 appears in the Locals window as a
value under my Public Variable MyText.
When control returns to the module code, MyHold_1 says "Empty" and MyHold_2
says "" in the Locals window.
In other words the value in the variable disappears when control leaves the
UserForm.
?????

You have created a local variable in your user form which is hiding (or
"shadowing") the global variable. The user form loads the data into
*its* MyText - which goes out of scope when that routine is done. The
solution is to just get rid of the line

Dim MyText As Variant

Which should fix everything.

-semiopen

 

[Guest]

Many thanks semiopen - that was the problem.