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?

--
donwb

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:

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?

--
donwb

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.



donbowyer wrote:

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?

--
donwb

--

Dave Peterson

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.
--
donwb


"Dave Peterson" wrote:

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.



donbowyer wrote:

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?

--
donwb

--

Dave Peterson

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.
--
donwb


"Dave Peterson" wrote:

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.



donbowyer wrote:

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?

--
donwb

--

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?

donbowyer wrote:

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.
--
donwb

"Dave Peterson" wrote:

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.



donbowyer wrote:

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?

--
donwb

--

Dave Peterson


--

Dave Peterson

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.
?????

--
donwb


"Dave Peterson" wrote:

I would expect your code to work, too.

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

donbowyer wrote:

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.
--
donwb

"Dave Peterson" wrote:

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.



donbowyer wrote:

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?

--
donwb

--

Dave Peterson


--

Dave Peterson

donbowyer wrote:
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.
?????

--
donwb

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

Many thanks semiopen - that was the problem.
--
donwb


"semiopen" wrote:


donbowyer wrote:
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.
?????

--
donwb

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