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 - 25 Oct 2005 17:51 GMT
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.
--
Enjoy,
Tony
> 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
[quoted text clipped - 11 lines]
>
> Bear
Greg - 26 Oct 2005 16:02 GMT
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 - 27 Oct 2005 01:42 GMT
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.
--
Enjoy,
Tony
> Tony,
>
[quoted text clipped - 7 lines]
>
> Thanks.
Greg Maxey - 27 Oct 2005 02:17 GMT
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.

Signature
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
> 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
[quoted text clipped - 110 lines]
>>
>> Thanks.
Greg Maxey - 25 Oct 2005 22:49 GMT
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

Signature
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
>I can't seem to find the best way to pass variables between a user form and
>a
[quoted text clipped - 16 lines]
>
> Bear
Tony Jollans - 25 Oct 2005 23:11 GMT
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.
--
Enjoy,
Tony
> Bear,
>
[quoted text clipped - 73 lines]
> >
> > Bear
Greg Maxey - 25 Oct 2005 23:22 GMT
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

Signature
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.
> Wow, Greg!
>
[quoted text clipped - 82 lines]
>>>
>>> Bear
Tony Jollans - 26 Oct 2005 00:00 GMT
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 :)
--
Enjoy,
Tony
> Tony,
>
[quoted text clipped - 105 lines]
> >>>
> >>> Bear
Ed - 26 Oct 2005 00:49 GMT
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
> 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
[quoted text clipped - 11 lines]
>
> Bear
Bear - 26 Oct 2005 15:26 GMT
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