Retaining value of a string from a textbox on a form ??

[Guest]

I am working with a form which has a textbox that I am entering text into and
than passing the the string value entered into the text box on my form to a
string variable.
for example...

Sub test()
Dim MyTxtString as String
MyForm.Show
MyTxtString = MyForm.Mytextbox.value
MyForm.Hide
MsgBox (MyTxTString)
End Sub()

The problem is that the string from the textbox on MyForm does get assigned
to MyTxtString variable, however as soon as the form his hidden the variable
MyTxtString looses the value entered into the textbox and the value of
MyTxtString becomes "" (nothing).
How do I maintain the scope of MyTxtString to keep the value assigned to it
even when the form is hidden ?

I have already tried declaring MyTxtString as a Public Global variable but
the scope and life of MyTxtString still is not maintained.
What am I doing wrong ?

Dan.

 

[Bernie Deitrick]

Dan,

Try declaring it as a public variable in a standard codemodule, not the
userform codemodule.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks for your input Bernie. However I already tried that and it doesn't work
I think it's realy retarded that you can't assign the value of the textbox
to a public global variable and have VBA maintain that value in memeory even
if the form with the txtbox is hidden it shouldn't matter because the value
was passed to the Public Global variable before the form was hidden. Im
begining to think that this is not possible with VBA. The only work around I
can think of is to have the value stored and saved to a text file and than
recal that string from the text file, But I realy don't want to have to do
that ??

 

[Tom Ogilvy]

In the code you show, it isn't declared as a global variable. Even if it
was, doing the assignment where you show it won't work if you unloaded the
form. If the form is just hidden (which is a different command), then it
will work. In fact, as long as the userform is hidden and not unloaded, you
can access the textbox directly.

This works fine for everyone else, so I suspect you are being loose in your
terminology or you are doing it correctly.

this code is flawed:
Sub test()
Dim MyTxtString as String
MyForm.Show
MyTxtString = MyForm.Mytextbox.value
MyForm.Hide
MsgBox (MyTxTString)
End Sub()


by using the Show method with no arguments, your form is shown as Modal.
therefore the next line of code (the assignment) will not be executed until
the userform is either hidden or unloaded - this would have to be done in
the the code module of the userform itself. If you unload there, then when
you reach
MyTxtSTring = MyForm.Mytextbox.value
the userform has been unload and by referencing it again, you reload it as a
new instance (thus it has no values but the default values). MyForm.Hide
at that point is meaningless. And Msgbox(MyTxtString) will of course show
nothing if the default value of the textbox is nothing.

Sub test()
Dim MyTxtString as String
MyForm.Show
MyTxtString = MyForm.Mytextbox.value
Unload MyForm
MsgBox (MyTxTString)
End Sub()


In the Userform something like

Private Sub CmdOK_click()
me.hide
End Sub

---------------------------------------
if you wanted to use the global varible.
---------------------------------------
' declared at the top of the general module, not a sheet module
Public MyTxtString as String

Sub test()
MyForm.Show
MsgBox (MyTxTString)
End Sub()

in the Userform Module

Private Sub CmdOK_Click()
MyTxtString = MyForm.Mytextbox.value
Unload Me
End Sub

 

[Toby Erkson]

How about assigning the textbox.value to a worksheet cell before hiding, then
referencing the cell?

Just curious, does this work?:
msgbox (MyForm.Mytextbox.Value)

 

[Guest]

Hi Tom, I see you alot on these boards and your Great! you have helped me a
number
of times before, you seem to be a staple of this news group

Anyhow You are right sorry about being loose with my description, I should
have been more detailed.

here is what I should have showed for sample code

Workbook Open Code.
-----------------------
Private Workbok_Open()
Application.visible = False
MyForm.Show
End Sub

Moulde1 Code.
--------------------
Option Explicit
Public MyTxtBoxValue As String
Sub MyModule()
MsgBox (MyTxtBoxValue)
End Sub
--------------------

UserForm Code.
--------------------
Sub HideMyForm On_Click()
MyTxtBoxValue = MyForm.TextBox1.value
MyForm.Hide
Application.Visible = True
Applicatoin.Visible = False
MyForm.Show
End Sub

 

[Tom Ogilvy]

in the Userform I had this:

Private Sub CommandButton1_Click()
MyModule
End Sub

Private Sub CommandButton2_Click()
Final
End Sub

Private Sub CommandButton3_Click()
Unload Me
Application.Visible = True
End Sub

Sub HideMyForm_Click()
MyTxtBoxValue = myform.TextBox1.Value
myform.Hide
Application.Visible = True
Application.Visible = False
myform.Show
End Sub


Since you showed no code that restored the Excel application, I put two
buttons on the form to call the MyModule and Final subs respectively. After
I clicked on the hidemyForm button, I clicked on them and each show the
value in the textbox (in the message box), so I had not problem seeing what
the value was. Other than that, I used the code you showed, although i
correct the spelling of Workbook_Open.

Also, not sure why you hide the form, make the application visible, then
hide it and then show the form again.

I also put in a button that allowed me to close the form and make the
application.Visible again.