How do I move a userform into a module?
Hi Michael,
To start with your first suggestion
"Why not make it a Public procedure? "
Is that done by simply replacing Private with Public.
Thank you.
Thank you Michael,I will take some time to understand all you’ve written then try to close the book on this project tomorrow. Thank you very much for your time.
yes, you can.
It doesn’t matter how many user forms you have. But if you’re only using
the sub in your module specifically for the one form, then I don’t see
why you have it in a separate module.
An example of a sub you might put into a module so it can be accessed
from all the forms would be a routine to load the next record in a
recordset, when the user has clicked on an arrow. Not a particularly
good example as it will be a simple routine, but nonetheless it is a
routine which you’ll use frequently, & it isn’t form-specific.
A form-specific routine might be one to calculate which will generate &
populate a report based on information from that specific form’s values.
I’m not sure that’s a particularly good example but y mind’s a blank at
the minute!
Donovan,
Why not make it a Public procedure?
If you can’t (or don’t want) to change the scope of the procedure, create a
separate CLASS module that is a “wrapper” class for your form. In the class
module, create a procedure to handle the Initialize event for the class:
…
Option Compare Database
Option Explicit
Public Event SignalClose(abDone As Boolean, aszName As String)
Dim frmMyForm As Form_MyForm
Private Sub Class_Initialize()
’
’ Open (hidden) an INSTANCE of the form
’
…Set frmMyForm = New Form_MyForm
End Sub
…
Now add a procedure that you will CALL from whatever code would launch the
FORM:
…
’
’ This is a callable Initialization procedure
’
’
Public Function Init(Optional aszMyName As String)
…On Error GoTo Err_clsWrapMyFormInit
’
’ Tell the newly opened form who we are, so the form can use our events
’
…frmMyForm.ClassParent = Me
’
’ Tell the newly opened form what record to display (if the caller
specified one)
’
…If (Not IsMissing(aszMyName)) And (Not IsNull(aszMyName)) Then
frmMyForm.GoToTarget = aszMyName
’
’ A form that is created this way (rather than by docmd.open) isn’t
automatically visible (which is actually
’ a good thing, because it allows us to set properties before it becomes
visible). Make it available to the user.
’
…frmMyForm.Visible = True
Exit_clsWrapMyFormInit:
…Exit Function
Err_clsWrapMyFormInit:
…MsgBox Err.Description
…Resume Exit_clsWrapMyFormInit
End Function
…
And add a procedure to handle the Terminate event for the class:
…
Private Sub Class_Terminate()
…Set frmMyForm = Nothing
End Sub
…
Finally, add an EVENT signaler to the class:
…
Function SignalDone(abDone As Boolean, aszName As String)
…RaiseEvent SignalClose(abDone, aszName)
End Function
…
Now, in your form, add the following Property procedures:
…
Dim fclsWrapper As clsWrapRelation ’ This is OUR wrapper
class (it calls us)
’
’ This procedure stores a “parent” object — the instance of a wrapper
class through which we can
’ communicate with our caller
’
Public Property Let ClassParent(aclsParent As clsWrapMyForm)
Set fclsWrapper = aclsParent
’
’ Assume that we’re being invoked to add a new relation (if GoToTagret is
called, that will find the record)
’
…Call btnAdd_Click
End Property
…
Public Property Let GoToTarget(aszKey As String)
’
’ We can take advantage of the fact that Relations have unique names, and of
the fact that this is an UNBOUND
’ form, so we have a recordset that we can use to do our search. The first
fact allows us to do a simple move
’ to the record of choice, while the second allows us to use an existing
procedure to display the record after
’ “navigating” to it.
’
…On Error GoTo Err_GoToTarget
…rstCurrent.FindFirst “[MyKey] = “”” & aszKey & “”“”
…If Not rstCurrent.NoMatch Then Call DisplayCurrentRecord
Exit_GoToTarget:
…Exit Property
Err_GoToTarget:
…MsgBox Err.Description
…Resume Exit_GoToTarget
End Property
…
And add the following Form Event Procedures:
…
Private Sub Form_Close()
…If (Not IsEmpty(fclsWrapper)) And (Not fclsWrapper Is Nothing) Then
…fclsWrapper.SignalDone bCloseOK, Nz(txtRelationship)
…End If
End Sub
Private Sub Form_Open(Cancel As Integer)
…Dim szQuery As String
’
’ Create a recordset that we can use to access the table – with sorting
’
…szQuery = “SELECT * FROM tMyTable ORDER BY MyKey”
…Set rstCurrent = CurrentDb.OpenRecordset(szQuery, dbOpenDynaset)
…If rstCurrent.RecordCount <> 0 Then rstCurrent.MoveFirst
’
’ Fill in the form
’
…Call DisplayCurrentRecord
End Sub
…
Once you have done ALL of this, you can invoke your form THROUGH the class
module, and then you can use the same technique to call other procedures IN
the form, or to enable the form to call procedures through the class module.
Michael S. Meyers-Jouan
Don’t know about Access forms, but in Excel it has been suggested that
one treat a form as one would any class module:
Declare the form where you intend to use it.
Instantiate the declared object when it is needed
Assume you have a user form named frmMyForm as the class module.
Sub DoMyForm()
Dim MyWorkingForm as frmMyForm
…your code
… you need the form
Set MyWorkingForm = New frmMyForm
… the rest of your code that is external to the form
End Sub
… all of the objects and events associated with the form are
contained in the frmMyForm object and implemented in the declared and
instantiated MyWorkingForm object
David
Yes, I am considering that. You know if they can’t come to you then you go to them. I am guessing the reverse is not true…you can call a Pub Sub from a Private Sub. Correct?
Oh but I have 2 userforms in my project…I am still in a mess aren’t I?
Thank you.
I am using an Excel worksheet for my database.
why have you put the subroutine in the module as opposed to being in the
form’s own module? What triggers the code - is the user clicking on a
button? Do you intend to use the routine from other modules?
If you are just running a sub in response to an event within the form,
then why not take the routine out of the separate module & put it into
the form’s own module?
Application.run “Whatever”
Are you trying to show a form? frmUserForm.Show
Or
Call a private procedure that is just IN a form from somewhere else?
You will load the form if you try that.
Follow my original post and move the sub out of the form into a standard module.
If you need the sub with or without the form it is now available.
JPB
From: Kempes78 via vb-vba-l [mailto:vb-vba-l@Groups.ITtoolbox.com]
Sent: Tuesday, April 28, 2009 4:43 PM
To: JustPlainBill
Subject: Re:[vb-vba-l] Can I move a UserForm into a Module?
Since it is a Private Sub & a Private Sub cannot be called unless it is in the
same module how do I get it to run?
Since it is a Private Sub & a Private Sub cannot be called unless it is in the same module how do I get it to run?
you can’t - a form is basically a user interface which is a means of
displaying your data & allowing your user to manipulate it; it reacts to
a series of events, such as being displayed (opened), or again, the user
clicking a button. The code which is behind every form consists of the
responses to these events, & may also call general routines which are in
the modules.
What are you using for your database?
Thank you; can you tell me how?
You can copy the code in the userforms module into standard modules. Just call
them as you would any other sub. You need the Event handlers for the form and
any controls to be IN the form for them to work so.
This is a good practice to have, as it can keep your forms code pretty well
organized, and you can keep your modules named and have similar code in easy to
find modules!
In the form:
Private Sub UserForm1_Initialize()
Call ThisSubIsInAStandardModule(“YouMightUseAParameter”)
End sub
In the Standard Module:
Public Sub ThisSubIsInAStandardModule(strParam as string)
msgbox strParam
End sub
Just Plain,
Bill
From: Kempes78 via vb-vba-l [mailto:vb-vba-l@Groups.ITtoolbox.com]
Sent: Tuesday, April 28, 2009 2:50 PM
To: JustPlainBill
Subject: [vb-vba-l] Can I move a UserForm into a Module?
How do I move a userform into a module?
a userform IS a module in VBA
you don’t, you can load a userform
