Extract text from a "textbox"

[Robert Crandal]

My code so far looks like this:

'---------------------------------------------------
Sub DoStuff ()

Dim first_name as String
Dim last_name as String

UserForm.Show ' Show form with text boxes

' what goes here?

End Sub
'-------------------------------------------------

The code that shows the userform causes a
userform to be displayed which allows a user to
enter text into Textbox1 and/or Textbox2.

Once a user enters text into either textbox and
closes out the Userform, is it possible to get
the values from the textboxes and store them
in the string variables above in the DoStuff()
subroutine??

Thank you!

Robert

 

[Rick Rothstein]

Instead of Dim'ming your variables inside the DoStuff procedure (where only
code within the DoStuff procedure can see them), put those declaration
statements inside a standard module (Insert/Module from the VB editor's menu
bar) using Public instead of Dim and ALL procedures in your workbook will be
able to see them, in particular, your DoStuff procedure and any code in your
UserForm. And once you do that, make sure you remove the Dim statements from
the DoStuff procedure (if you leave them there, then those declarations will
take precedence).

Rick Rothstein (MVP - Excel)



"Robert Crandal" wrote in message

My code so far looks like this:

'---------------------------------------------------
Sub DoStuff ()

Dim first_name as String
Dim last_name as String

UserForm.Show ' Show form with text boxes

' what goes here?

End Sub
'-------------------------------------------------

The code that shows the userform causes a
userform to be displayed which allows a user to
enter text into Textbox1 and/or Textbox2.

Once a user enters text into either textbox and
closes out the Userform, is it possible to get
the values from the textboxes and store them
in the string variables above in the DoStuff()
subroutine??

Thank you!

Robert

 

[GS]

Robert Crandal formulated the question :

My code so far looks like this:

'---------------------------------------------------
Sub DoStuff ()

Dim first_name as String
Dim last_name as String

UserForm.Show ' Show form with text boxes

' what goes here?

End Sub
'-------------------------------------------------

The code that shows the userform causes a userform to be displayed which
allows a user to
enter text into Textbox1 and/or Textbox2.

Once a user enters text into either textbox and
closes out the Userform, is it possible to get
the values from the textboxes and store them
in the string variables above in the DoStuff()
subroutine??

Thank you!

Robert

I'd probably use the form's QueryClose event to put the text into
global variables only after the text passes some validation. Your
DoStuff sub could then use those variables to get the text, meaning
there's no need to declare local variables within the sub. I'd have
some conditions in place before acting on the text, though. (ie: make
sure the variables actually contain values) You could facilitate that
more efficiently if your DoStuff sub used a function procedure that
returns a boolean value if the global variables were successfully
updated with valid values. Something like:

<In a standard module>
Public gsFirstname As String
Public gsLastname As String


Sub DoStuff()
If bGetUserName Then
'If you got here then do your thing
Debug.Print "True" '//test the result is valid
End If
End Sub

Function bGetUserName() As Boolean
gsFirstname = "": gsLastname = "" '//clear any previous values
UserForm1.Show
bGetUserName = (gsFirstname <> "" And gsLastname <> "")
End Function

<In the code behind UserForm1>
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
gsFirstname = Me.txtFirstname.Text: gsLastname = Me.txtLastname.Text
End Sub

Note there is no validation in place. You could get tighter control on
input validation using the Exit event of the TextBox.

HTH

 

[AB]

In the userform you need a button (like btnOK) that has btnOK_click
event something like this:

btnOK_click
'do some validation here and exit sub in case fails
me.hide'Hides (as oppose to terminate/destroy) the form keeping all
controls alive/available
end sub

Your DoStuff then would be something like this:

Sub DoStuff ()

Dim first_name as String
Dim last_name as String

UserForm.Show ' Show form with text boxes
'user types the f/l names into the textboxes
'user clicks on the btnOK (that hides the form but keeps it alive)
first_name=UserForm.txtbx_FName.Text'Read First Name
last_name=UserForm.txtbx_FName.Text'Read Last Name

Unload UserForm'Terminate the form


End Sub

 

[GS]

AB expressed precisely :

In the userform you need a button (like btnOK) that has btnOK_click
event something like this:

btnOK_click
'do some validation here and exit sub in case fails
me.hide'Hides (as oppose to terminate/destroy) the form keeping all
controls alive/available
end sub

Your DoStuff then would be something like this:

Sub DoStuff ()

Dim first_name as String
Dim last_name as String

UserForm.Show ' Show form with text boxes
'user types the f/l names into the textboxes
'user clicks on the btnOK (that hides the form but keeps it alive)
first_name=UserForm.txtbx_FName.Text'Read First Name
last_name=UserForm.txtbx_FName.Text'Read Last Name

Unload UserForm'Terminate the form


End Sub

I agree that your suggestion will work, but I don't see the point to
keeping the userform loaded in memory. I whole heartedly agree with
Rick on this approach even though there's any number of other ways to
get this done!

 

[AB]

The reason i do it this way (and - as per the code - i terminate the
form and don't keep it in memory longer than necessary) is otherwise
there is way big of a chance (for me anyway) that the global variables
would have retained the value from previous user entry and not the
current one - as that's the whole point - you give it a value, it
keeps it as long as in compile state.
But when a code grabs that public varialbe - how would it know whether
that is an entry that the user typed into the form just now or 60mins
ago? To manage that aspect strict controls need to be in place and i
find the
form.hide
read control value
unload form
method more convenient and safer especially it's not such a big of a
drag onto the pc's memory anyway.
Just my 0.02 of worth.

 

[GS]

AB submitted this idea :

The reason i do it this way (and - as per the code - i terminate the
form and don't keep it in memory longer than necessary) is otherwise
there is way big of a chance (for me anyway) that the global variables
would have retained the value from previous user entry and not the
current one - as that's the whole point - you give it a value, it
keeps it as long as in compile state.
But when a code grabs that public varialbe - how would it know whether
that is an entry that the user typed into the form just now or 60mins
ago? To manage that aspect strict controls need to be in place and i
find the
form.hide
read control value
unload form
method more convenient and safer especially it's not such a big of a
drag onto the pc's memory anyway.
Just my 0.02 of worth.

You cite a good point. This is precisely why I used a function to load
the userform. That function clears any previous values in the vars
before it opens the userform, ensuring that any values in the vars will
be new to the current input.

Note that I suggest the input values should be validated by the
userform. This is so the userform purpose can be encapsulated and thus
reusable. The procedure that processes the data doesn't need (nor
shouldn't IMO) to know anything about how the data was collected in
order to do its task, which only happens if the function was
successful. This, in turn, depends on what happens in the userform as
far as validation of input goes OR if the user cancels. Again, this
should be handled in the userform.

<FWIW>
Seems that popular opinion regarding userforms that collect data/input
is that the userform should handle all validations and where the valid
data goes. Our procedures that use/process the data should not be
'tied' to the userform in any way.

Otherwise, wouldn't InputBox suffice?

 

[AB]

Validations should be in the userform - I'll give you that. The
external approach I used was more like a demonstration of code
possibilities rather than thougth-through methodology.

At the end of the day it's a matter of preference - the way i see it -
i don't like code with too many global variables - they get my head
spin. In addition to that in the setup one still ends up with the
global variable being tightly bound to the userform - so why not to
have it encapuslated in it to begin with.
I used to write codes with multiple global variables but along the way
i realized that it wasn't for me.

I recon the "Otherwise, wouldn't InputBox suffice?" wasn't really a
question.