Passing variables between user forms and modules

[Bear]

I can't seem to find the best way to pass variables between a user form and a
module. Typically, I'm trying to send variables to a module to control how it
initializes fields during the load event, and sending variables back to the
module to determine subsequent processing based on the user's choices in the
form.

I know that if the form is still available (even if hidden) the module can
access the form objects with proper qualification. But it doesn't seem to
work the other way. That is, there doesn't seem to be any way to access
module variables from a form, unless I declare the variables as Public. But
that seems sloppy, somehow.

What's the best way to communicate variables between a module and a form?

Bear

 

[Tony Jollans]

If the variables relate to userform processing put them all in the userform
and set / check them from code modules.

If you want to do it the other way round you need to declare the variables
at module level as public - but that gives them greater scope (and more
potential for error) than you probably want.

 

[Greg Maxey]

Bear,

Another method is to use Property Let and Property Get statements.
Unfortunately I find them frustrating and complex to create, but maybe that
is just me. Here is an example where is string variable "pWord" is passed
to a userform and then the passed back to the project module. To test all
you need is a textbox and command button in a simple form:

Code in Module:
Sub LetGetExample()

Dim pWord As String
Dim MyForm As MyForm

pWord = "What is your name?" 'string value to pass to user form
Set MyForm = New MyForm
Load MyForm
With MyForm
.fInput = pWord 'Passes (Let) pWord value to form.
.Show
End With
pWord = MyForm.fInput 'Retrieves (Get) value of form property
Unload MyForm
Set MyForm = Nothing
MsgBox ("Here is the answer: " & pWord)
End Sub

Code for userform:

Option Explicit
Dim LetGetString As String

Public Property Let fInput(ByVal pWord As String)
LetGetString = pWord
End Property

Public Property Get fInput() As String
fInput = LetGetString
End Property

Private Sub CommandButton1_Click()
Me.Hide
End Sub

Private Sub TextBox1_Change()
fInput = TextBox1.Text
End Sub

Private Sub UserForm_Activate()
TextBox1.Text = LetGetString
End Sub

 

[Tony Jollans]

Wow, Greg!

Don't you think that's overkill for a UserForm? Because it is an actual
object rather than just an object definition all that hard work is
effectively done for you when you simply declare a module level variable.

 

[Greg Maxey]

Tony,

You are probably right. I only dabble in UserForms about once in a blue
moon when the urge strikes me to crack my skull over an annoyance in Word.
Case in point is the bookmarker tool. If you remember during that exercise
I asked for a primer on declaring variables. I still get all tangled up
with Public, and Private and what one will do and the other won't etc. I
think Jezebel blew this Get Let method passed me once when for some reason a
Public statement wasn't doing the trick.

I never claim to offer the best solution and sometimes I drive tacks with a
sledgehammer. Only offering the Get Let piece as another approach. Who
knows, there could be another bored soul out there interested in wrapping
his or her head around it.

Cheers

 

[Tony Jollans]

One of the joys of Word is that there is so much to dabble in. And I have my
own collection of sledgehammers which I often use

 

[Ed]

One way is to create a document variable and have the form read it.
Another, if you have only a few variables to pass, is to assign them from
the template module to the Tag property of a Form object (almost every
control, including the For itself, has a blank Tag field waiting to be
filled). The form can always read the properties of its own controls.

HTH
Ed

 

[Bear]

Thanks to Tony, Greg, and Ed. Now I have several approaches to experiment with.

Also, you confirmed for me that I wasn't missing anything big and simple.

Bear

 

[Greg]

Tony,

Can you explain this a little better?

If the variables relate to userform processing put them all in the
userform
and set / check them from code modules.

I mean can you give a basic example.

Thanks.

 

[Tony Jollans]

The truth of the matter is that there is limited need for this. Provided a
userform is in memory all its controls are available to module code and they
tend to hold the information that you're interested in.

This is all artificial but, for example's sake, let's suppose you have a
series of buttons and you want to hide certain of them after they have been
clicked a certain number of times (in other words you want to impose a
maximum number of clicks) and then check (outside the userform) which ones
the user has actually clicked and how many times.

Your userform will have five buttons in total - three accumulator buttons
called, say, Button1, Button2 and Button3, and also buttons called ButtonOK
and a ButtonCancel. When it is shown click a number of times on each of the
three accumulator buttons and click OK or Cancel to finish.

You could code the Userform like this:

Public Button1HideCount As Integer
Public Button2HideCount As Integer
Public Button3HideCount As Integer

Public Button1ClickCount As Integer
Public Button2ClickCount As Integer
Public Button3ClickCount As Integer

Private Sub Button1_Click()
Button1ClickCount = Button1ClickCount + 1
If Button1ClickCount >= Button1HideCount Then _
Button1.Visible = False
End Sub

Private Sub Button2_Click()
Button2ClickCount = Button2ClickCount + 1
If Button2ClickCount >= Button2HideCount Then _
Button2.Visible = False
End Sub

Private Sub Button3_Click()
Button3ClickCount = Button3ClickCount + 1
If Button3ClickCount >= Button3HideCount Then _
Button3.Visible = False
End Sub

Private Sub ButtonOK_Click()
Hide
End Sub

Private Sub ButtonCancel_Click()
Button1ClickCount = 0
Button2ClickCount = 0
Button3ClickCount = 0
Hide
End Sub

And code a module like this:

Sub Example()

With UserForm1

' Set maxima - choose your own
.Button1HideCount = 1
.Button2HideCount = 3
.Button3HideCount = 22

' Initialize counters (to be safe)
.Button1ClickCount = 0
.Button2ClickCount = 0
.Button3ClickCount = 0

' Make sure all buttons show (again to be safe)
.Button1.Visible = True
.Button2.Visible = True
.Button3.Visible = True

' Show the form
.Show

' Grab the information from the form
Debug.Print "Button1 was pressed " & .Button1ClickCount & " times."
Debug.Print "Button2 was pressed " & .Button2ClickCount & " times."
Debug.Print "Button3 was pressed " & .Button3ClickCount & " times."

End With

' Destroy the form when finished
Unload UserForm1

End Sub

The controls and public variables are available inside the userform without
qualification, and outside it by qualification with the userform object
reference (in this example the default instance created on first reference).

All of the variables in the Userform relate to the form and are not relevant
in other processing (a given in this example) so are kept with and destroyed
with (each instance of) the form instead of cluttering up a namespace with
wider scope.

 

[Greg Maxey]

Thanks Tony.

I didn't build your example, but I used the method to test on a simple
example that I was playing around with.