The Anchorage
Personal website of Gregory K. Maxey, Commander USN (Retired)
The Anchorage
Personal website of Gregory K. Maxey, Commander USN (Retired)
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
This Microsoft Word Tips & Microsoft Word Help page has been a long time in preparation. I started it four or five years ago, then lost interest, and then dusted it off to try to finish it at least a dozen times. Why? Because as these are advanced notes and tips, I'm not 100% certain that I am smart enough or even qualified to present them properly.
My goal in publishing these notes and tips is to spare other VBA enthusiasts, who may chance to find them, some of the angst and mystification that I've experienced over the years with userforms in general and these topics in particular. With that in mind, let's jump right in.
The two terms paired off in the heading above are what I chose to call the two methods of initializing a userform in a project. Both have avid supporters and both have fierce critics. My purpose is to try to explain both, offer my preference, but let you decide which method is best for you.
A userform definition is an object definition that contains the form (what you see) and a class module (the "machinery" behind what you see). When you insert and create a userform in a new project, two things occur:
A general instance of the class can be declared and created just as for any other class or object, like this:
However, the special userform class behaves as if somewhere invisible to the code window, the following statement is made a part of the project:
The behavior (or invisible statement) serves to declare (and tell VBA to load on first use) an object variable, called UserForm1, of type UserForm1.
This is called the default instance of the class (also called the "magic form" or "self/auto initiating form" by some people).
This means that, while a userform operates in most ways like any other class, there is always an instance of the userform available for instant loading and use at any time. Any reference to that instance calls it into existence.
The following simple demonstration should help illustrate this:
Sub DemoDefaultInstance() 'Create an explicit variable declaration. Dim oFrm As UserForm1 'Declaring the variable doesn't make it an instance. Debug.Print "The explicit declarled instance is: " & TypeName(oFrm) 'The default instance is there. Always there! Debug.Print "The default instance is: " & TypeName(UserForm1) 'Really, go ahead and try to kill it. Unload UserForm1 'It takes a licking but keeps on ticking!! Debug.Print "The default instance is still: " & TypeName(UserForm1) 'Create an explicitly declared instance of theform. Set oFrm = New UserForm1 Debug.Print "The explicit declared instance created is: " & TypeName(oFrm) End Sub
Private Sub UserForm_Initialize() MsgBox "Eureka!! I've been initiated and loaded in memory." End Sub
It is possible to defeat and override the default instance. To stop the default instance being provided you can explicitly code:
After which any code that attempts to use the default instance will error with "Object variable not set." To resolve the error you must accept that the default instance of the form is dead and use an explicitly declared instance instead. To demonstrate, copy and paste the following code in the standard module and then execute the code line by line:
Sub DemoOverideDI()
Dim UserForm1 As UserForm1
On Error GoTo Err_Load
UserForm1.Show
Set UserForm1 = New UserForm1
UserForm1.Show
Exit Sub
Err_Load:
If MsgBox(Err.Description & vbCr + vbCr _
& "The default instance of this form is kaput!! " _
& "Do you want to load and use an explicitly " _
& "declared instance instead?", vbQuestion + vbYesNo, _
"Decisions, Decisions") = vbYes Then
Resume Next
End If
End Sub
You can also assign a alias to the default instance. To demonstrate, copy and paste the following code in the standard module and then execute the code line by line:
Sub Demo_DI_Alias()
'Note use of "New" keyword.
Dim oFrm As New UserForm1
oFrm.Show
End Sub
Many people, and most old school purist, shun the default instance and on principle will never use it. More pragmatic people can compare the two and see that both, in most cases, essentially do the same thing and that the default instance method uses fewer lines of code:
Despite any perceived "magic" associated with the default instance, it is perfectly safe to use if you understand and follow certain basic rules:
To illustrate rule 3:
Sub CallDefaultInstance() UserForm1.Show MsgBox UserForm1.TextBox1.Text End Sub
Private Sub cmdOK_Click() Me.Hide End Sub Private Sub cmdCancel_Click() Unload Me End Sub Private Sub UserForm_Initialize() MsgBox "Eureka!! I've been initiated and loaded in memory." End Sub
The exercise outline above has illustrated how any reference to the default instance of the class will initiate a new instance if an instance does not already exist in memory. When the form is closed with the form's "X" close button or if the "Cancel" button is clicked, the existing instance of the form is unloaded from memory and destroyed. A new instance is immediately created by the next reference to the default instance. Any user entered changes in control values in the previous instance are not reflected in the new instance.
Now that you understand the difference between the default instance and an explicit declaration instance of a form, I want to discuss methods of calling both types and the behavior observed in both with respect to the VBA Load and Set statements, and the userform .Show method, Initialize and Activate events.
The easiest way to do this is with a series of demonstrations and experiments. To set up the demonstrations, perform these steps:
Private Sub cmdHide_Click() p_strReport = p_strReport & vbCr & "Hide command/method executed." Debug.Print "Hide command/method executed." Me.Hide End Sub Private Sub cmdUnload_Click() p_strReport = p_strReport & vbCr & "Unload command/Explcit Unload statement executed." Debug.Print "Unload command/Explcit Unload statement executed." Unload Me End Sub Private Sub UserForm_Activate() p_strReport = p_strReport & vbCr & Me.Name & " activated." Debug.Print Me.Name & " activated." End Sub Private Sub UserForm_Deactivate() p_strReport = p_strReport & vbCr & Me.Name & " deactivated." Debub.Print Me.Name & " deactivated." End Sub Private Sub UserForm_Initialize() p_strReport = p_strReport & vbCr & Me.Name & " initialized." Debug.Print Me.Name & " initialized." End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) p_strReport = p_strReport & vbCr & Me.Name & " queryclosed, mode " & CloseMode Debug.Print Me.Name & " queryclosed, mode " & CloseMode End Sub Private Sub UserForm_Terminate() p_strReport = p_strReport & vbCr & Me.Name & " terminated." Debug.Print Me.Name & " terminated." End Sub
Option Explicit Public p_strReport As String 'These demonstrations are provided to help you to see and understand how default 'instance (DI) and explicitly declared (ED) instances of a userforms interact with your 'code. 'Step through each demo using the F8 key. After you have step through the Activate event, 'switch to the document and click the command button identified in the form. Sub Demo1() p_strReport = "" 'Load and show DI instance of form. frmBasic.Show p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The SDI form's Initialize and Activate events occur immediately " _ & "one after the other when the .Show method is executed." & vbCr _ & "2. The form's QueryClose and Terminate events were " _ & "not executed when .Hide method was executed." & vbCr + vbCr _ & "Now run Demo2" MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo2() p_strReport = "" 'Show DI instance of form. frmBasic.Show p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. Only the form's Activate event was executed." & vbCr _ & "2. The form's Initialize event was not executed. This is because a form " _ & "is initialized when it is loaded into memory." & vbCr _ & "When the DI of the form was hidden in Demo 1, it remained " _ & "in memory and did not need to be loaded." & vbCr + vbCr _ & "To remove and unload a default instance of a form from memory, " _ & "you must close and unload the form." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) Unload frmBasic End Sub Sub Demo3() p_strReport = "" 'Load DI instance of form in memory. Load frmBasic 'Show DI instance of form. With frmBasic .lblNotes.Caption = "For this Demo, click the Unload command." p_strReport = p_strReport & vbCr & "Caption changed at run time." .Show End With p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The explicit Load statement loaded the form in memory " _ & "and triggered the form's Initialize event." & vbCr _ & "2. The .Show method triggered the form's Activate event." & vbCr _ & "3. By separating the events, the form designer was able to perform " _ & "actions on the form at run time before it is displayed" _ & "to the user." & vbCr _ & "4. By explicity calling the Unload statement, the form's QueryClose " _ & "and Terminate events were both executed." & vbCr + vbCr _ & "The instance of the form is dead. It is no longer in memory." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo4() p_strReport = "" 'Load and show DI instance of form. With frmBasic .lblNotes.Caption = "For this Demo, click the Unload command" p_strReport = p_strReport & vbCr & "Caption changed at run time." .Show End With p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The explicit Load statement was not used." & vbCr _ & "2. The form's Initialize event was triggered when " _ & "by the ""With frmBasic"" statement." & vbCr _ & "This is because, with VBA, an implicit Load statement is called " _ & "whenever the form designer attempts to modify the form with _ & "code at run time." & vbCr + vbCr _ & "The use of an explicit Load statement is not required with " _ & "userforms using VBA." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo5() Dim oFrm As frmBasic p_strReport = "" 'Load and show ED instance of form. Set oFrm = New frmBasic oFrm.Show p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The form's Initialize event was executed with the Set oFrm _ & "statement which created the instance of the form object." & vbCr _ & "2. The form's Activate event was executed with the .Show method." & vbCr _ & "3. The form's Terminate event (but not the QueryClose event) " _ & "will execute after you dismiss this message and let the procedure " _ & "run to competition! This is because the variable oFrm will " _ & "lose scope when the procedure is completed. " _ & "When the variable loses scope, the instance of the form will " _ & "be destroyed." & vbCr + vbCr _ & "When using a explicit declared instance of a form, an Unload " _ & "statement is not required to terminate and remove the instance " _ & "of the form from memory." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo6() Dim oFrm As frmBasic p_strReport = "" 'Load and show ED instance of form. Set oFrm = New frmBasic oFrm.Show Cleanup: Set oFrm = Nothing p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The form's Terminate event (but not the QueryClose event) was " _ & "executed before the procedure ran to completion." & vbCr _ & "This is because variable and the the instance of the form was " _ & "destroyed with the Set oFrm = Nothing statement." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo7() Dim oFrm As frmBasic p_strReport = "" Set oFrm = New frmBasic With oFrm .lblNotes.Caption = "For this Demo, click the Unload command." p_strReport = p_strReport & vbCr & "Caption changed at run time." .Show End With p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The form's QueryClose and Terminate events were executed " _ & "immediately in succession with the Unload statement." & vbCr + vbCr _ & "If you want to ensure that a form's QueryClose event is executed, " _ & "then you must include an explicit Unload statement either " _ & "in a form procedure or the the procedure that calls the form." MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub Sub Demo8() Dim oFrm As frmBasic p_strReport = "" Set oFrm = New frmBasic oFrm.Show Unload oFrm p_strReport = p_strReport & vbCr + vbCr & "Notice in this Demo, that:" & vbCr _ & "1. The form's QueryClose and Terminate events were executed " _ & "immediately in succession with the Unload statement in the calling " _ & "procedure." & vbCr + vbCr _ & "For reasons I'll demonstrate later, when using an Onload statement " _ & "in modal forms, it is best to put it in the calling procedure" MsgBox VBA.Right(p_strReport, Len(p_strReport) - 1) End Sub
When you employ a modeless form in your project, the behaviors are in ways, significantly different than those demonstrated for a modal form. To experiment with calling methods and modeless forms perform these steps:
Option Explicit Private Sub cmdHide_Click() Debug.Print "Hide command/method executed." Me.Hide End Sub Private Sub cmdUnload_Click() Debug.Print "Unload command/Explcit Unload statement executed." Unload Me End Sub Private Sub UserForm_Activate() Debug.Print Me.Name & " activated" End Sub Private Sub UserForm_Deactivate() Debug.Print Me.Name & " deactivated." End Sub Private Sub UserForm_Initialize() Debug.Print Me.Name & " initialized." End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) Debug.Print Me.Name & " queryclosed, mode " & CloseMode End Sub Private Sub UserForm_Terminate() p_strReport = p_strReport & vbCr & Me.Name & " terminated." Debug.Print Me.Name & " terminated." End Sub
Option Explicit 'These demonstrations are provided to help you to see and understand how default 'instance (DI) modeless userforms and explicitly declared (ED) instances 'of a modeless userform interact with your code. 'Step through each demo using the F8 key. Sub Demo1() 'Load and show DI instance of form. frmBasic_Modeless.Show vbModeless 'Notice that the form's Activate event was not triggered. 'However, if this procedure is ran from outside the code pane (e.g., Macros>Run>Demo1 _ or from the immediate window, the Activate event does fire. End Sub Sub Demo2() 'Show DI instance of form. Run this demo immediately 'after hiding the demo form in Demo 1. frmBasic_Modeless.Show vbModeless 'Notice that when the hidden form is shown the Activate event is executed. End Sub Sub Demo3() 'Load default DI instance of form in memory. Run this code 'immediately after running Demo 2. Load frmBasic_Modeless 'Show DI instance of form. With frmBasic_Modeless .lblNotes.Caption = "For this Demo, click the Unload command." p_strReport = p_strReport & vbCr & "Caption changed at run time." .Show End With 'Notice again that since the hidden form is already loaded 'in memory that the ""With frmBasic_Modeless" statement does" 'not trigger the form's Initialize event. End Sub Sub Demo4() 'Load and show DI instance of form. With frmBasic_Modeless .lblNotes.Caption = "For this Demo, click the Unload command" .Show vbModeless End With 'Notice that the form's Initialize event is triggered as the form is loaded into memory. 'Again the form's Activate event is not triggered. End Sub Sub Demo5() Dim oFrm As frmBasic_Modeless 'Load and show ED instance of form. Set oFrm = New frmBasic_Modeless oFrm.Show vbModeless 'Notice, like in Demo 1, only the form's Initialize event is triggered when 'the form is loaded and shown. 'Even though this procedure will run to completion, the variable oFrm remains 'in scope and the form remains loaded in memory until the form is either hidden 'using the .Hide method and the procedure containing the .Hide method runs to 'completion (triggering the form's Terminate event) or explicitly _ 'closed/unloaded triggering the form's QueryClose and Terminate events. End Sub Sub Demo6() Dim oFrm As frmBasic_Modeless Set oFrm = New frmBasic_Modeless With oFrm .lblNotes.Caption = "For this Demo, click the Unload command." .Show vbModeless End With 'Notice, the execution of the explicit Unload statement triggered 'the form's QueryClose and Terminate events. End Sub Sub Demo7() Dim oFrm As frmBasic p_strReport = "" Set oFrm = New frmBasic oFrm.Show vbModeless 'Obviously in modeless forms the Unload statement is self defeating in the 'calling procedure. Unload oFrm End Sub
My take away from the demonstrations we've conducted is this:
Note: The Load statement is probably a carryover from VB where there is a clear distinction between creating the form as a class object and creating it as a graphic object.
Sub CalloFrm() Dim oFrm As frmBasic Set oFrm = New frmBasic oFrm.Show Cleanup: Set oFrm = Nothing End Sub
I use this method not because it is best, but because it is what I am comfortable with and I also like to explicitly kill (set to nothing) any object variables before completing a procedure.
You might wander what is the difference between the UserForm's Initialize and Activate events, or which one you should use in your code.
For simple modal of forms, it probably doesn't matter whether you use 'the Activate or Initialize event, to preload data in the form, although I generally use Initialize. If you have a form that gets hidden and re-activated, and you want code to run when it's activated, then you must use the Activate event.
In the demonstrations conducted up to this point you have been repeatedly exposed to userforms with an uncontrolled, or out of your control, lifetime. That is about to change!
As a form designer, you have the choice to let the application and your user control the form's lifetime (uncontrolled), or you can take control.
Go back and review the demonstration of rule #3 for using default instance forms. When you, the form user, clicked the form's "X" close button, the form was unloaded and died. You, the form designer, lost control of the form's lifetime and your goal of displaying the form's textbox content was foiled!
The obstacles in gaining complete control of your form's life time is the form's "X" close button and the form user. You can tame the "X" close button with code. While you can't control the form user, you can write your code so that whatever the form user might do, it will serve your purpose.
The form's "X" close button is a windows component much like the "X" button on the browser you are using now to read this tips page. If you click it, the form dies, the page you are viewing dies, the document you are typing in dies, whatever.
There are three things that we can do to tame the "X" button on a userform:
Clicking a form's "X" close button triggers the form's QueryClose event. The QueryClose event is key to intercepting and repurposing "X" close. The event includes two parameters that allow the form designer take control of a form's lifetime:
To demonstrate a basic example of code to intercept the "X" close button and take complete control of a default instance of a userform, perform the following:
Private Sub cmdOK_Click() Me.Hide End Sub Private Sub cmdCancel_Click() 'Set form's .Tag property value to indicate 'a cancel action has executed. Me.Tag = "Cancel" 'The user's only goal in clicking either the 'form's "X" close button or the "Cancel" button 'is to make the form go away." Me.Hide End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'Was the form's "X" close button used 'to trigger the QueryClose event? If CloseMode = vbFormControlMenu Then 'Don't close the form and lose control. Cancel = True 'Take control and repurpose the user's 'action to serve your goals. cmdCancel_Click Else 'Let the form close as expected. End If End Sub
Sub CallDefaultInstance() UserForm2.Show 'Form's built-in .Tag property value 'is used as a logic router. Select Case UserForm2.Tag Case Is = "Cancel" GoTo lbl_Exit Case Else MsgBox UserForm2.TextBox1.Text End Select lbl_Exit: 'Since this is a DI form, you must explicitly 'close (unload) it to remove it from memory. Unload UserForm2 Exit Sub End Sub
Again, if you use a modeless default instance of a userform the process is a little different. To demonstrate:
Private Sub cmdOK_Click() 'Remember, in a modeless form the calling procedure 'continues running after the form loaded in memory and shown. 'If could be off doing other things or it may have run to completion. 'This means that you can: MsgBox Me.TextBox1.Text 'or call another external procedure in the standard module. ReportTextBox1Content Me.TextBox1 Unload Me End Sub Private Sub cmdCancel_Click() 'As the form designer, do you mean cancel, hide the default instance of the 'modeless form and keep it loaded in memory? 'Me.Hide '... or do you mean cancel and kill the form? Unload Me 'Either way, you are in control. End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 'Was the form's "X" close button used 'to trigger the QueryClose event? If CloseMode = vbFormControlMenu Then 'Don't close the form and lose control. Cancel = True 'Take control and repurpose the user's 'action to serve your goals. cmdCancel_Click Else 'Let the form close as expected. End If End Sub
Sub CallDefaultInstanceModeless() UserForm3.Show vbModeless End Sub Sub ReportTextBox1Content(ByRef oCtr As MSForms.TextBox) MsgBox oCtr.Text End Sub
Rather than intercept "X," another option is to disable or hide the "X" close button altogether. This can be accomplished by adding a module to your project that contain calls to Windows API functions that control the menu layout and display on a target window.
Add a new standard module to the document VBA project and name it mod_FormControl. In the code pane paste the following code:
Option Explicit
Private Const MF_BYPOSITION = &H400
Private Const MF_REMOVE = &H1000
Private Const WS_SYSMENU = &H80000
Private Const GWL_STYLE = (-16)
Private hwnd As Long
Private lStyle As Long
#If VBA7 And Win64 Then
Private Declare PtrSafe Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongLong
Private Declare PtrSafe Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" (ByVal hwnd As Long, _
ByVal nIndex As Long) As LongLong
Private Declare PtrSafe Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As LongLong
Private Declare PtrSafe Function DrawMenuBar Lib "user32" _
(ByVal hwnd As Long) As LongLong
Private Declare PtrSafe Function GetMenuItemCount Lib "user32" _
(ByVal hMenu As Long) As LongLong
Private Declare PtrSafe Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, _
ByVal bRevert As Long) As LongLong
Private Declare PtrSafe Function RemoveMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As LongLong
#Else
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long
Private Declare Function DrawMenuBar Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function GetMenuItemCount Lib "user32" _
(ByVal hMenu As Long) As Long
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, ByVal bRevert As Long) As Long
Private Declare Function RemoveMenu Lib "user32" _
(ByVal hMenu As Long, ByVal nPosition As Long, _
ByVal wFlags As Long) As Long
#End If
Sub HideXCloseButton(oForm As Object)
'All userforms have class name "ThunderDFrame" that
'is unrelated to the form name.
hwnd = FindWindow("ThunderDFrame", oForm.Caption)
lStyle = GetWindowLong(hwnd, GWL_STYLE)
SetWindowLong hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU
End Sub
Sub DisableXCloseButton(oForm As Object)
Dim hMenu As Long
Dim menuItemCount As Long
'Obtain the window handle to the userform.
hwnd = FindWindow("ThunderDFrame", oForm.Caption)
'Obtain the handle to the form's system menu.
hMenu = GetSystemMenu(hwnd, 0)
If hMenu Then
'Obtain the number of items in the menu.
menuItemCount = GetMenuItemCount(hMenu)
'Remove the system menu Close menu item.
'The menu item is 0-based, so the last
'item on the menu is menuItemCount - 1.
Call RemoveMenu(hMenu, menuItemCount - 1, MF_REMOVE Or MF_BYPOSITION)
'Remove the system menu separator line.
Call RemoveMenu(hMenu, menuItemCount - 2, MF_REMOVE Or MF_BYPOSITION)
'Force a redraw of the menu. This
'refreshes the titlebar, dimming the X.
Call DrawMenuBar(hwnd)
End If
End Sub
Now, when you want to hide or disable the "X" close button on a userform, all you need to do is use one of the two following lines of code in the forms Initialize or Activate events:
I'll demonstrate this in the next topic on Userform Custom Properties. In preparation for that topic:
Sub CallEDInstanceModeless() Dim oFrm As UserForm4 Set oFrm = New UserForm4 oFrm.Show vbModeless End Sub Sub ReportTextBox1ContentII(ByRef oCtr As MSForms.TextBox) MsgBox oCtr.Text End Sub Sub CallEDInstanceModal_I() Dim oFrm As UserForm5 Set oFrm = New UserForm5 oFrm.Show 'The form's custom frmUserCanceled 'property is used as a logic router. If oFrm.frmUserCanceled = True Then GoTo lbl_Exit Else MsgBox oFrm.TextBox1.Text End If lbl_Exit: 'Since this is a ED instance of the form it will 'be removed from memory when the variable loses scope. Set oFrm = Nothing Exit Sub End Sub Sub CallEDInstanceModal_II() Dim oFrm As UserForm6 Set oFrm = New UserForm6 oFrm.Show 'The form's custom frmUserCanceled 'property is used as a logic router. If oFrm.frmUserCanceled = True Then GoTo lbl_Exit Else MsgBox oFrm.TextBox1.Text End If lbl_Exit: 'Since this is a ED instance of the form it will 'be removed from memory when the variable loses scope. Set oFrm = Nothing Exit Sub End Sub
In the modal default instance demonstration, we used the form's built-in .Tag property as a logic router in the form's calling procedure. If the .Tag value was "Cancel" we did one thing. If not, we did some other thing. I've seen this method used by many people and it works well.
Userforms are a special class. Special like the ThisDocument class with lots of built-in predefined properties, methods and events. However, like any ordinary class, a userform can have custom properties, methods and events as well.
They can serve as a simple class with publically declared variables used as data holders, or a a complex call with private variables used as data holders. Like any ordinary complex class, complex userform classes require the use of property get, let and set procedures to communicate data to or from the class.
If I got your interest aroused, I'm going to have to let you down and first take care of the explicit declared modeless demonstration with the "X" close button hidden. After that, we'll move on to a demonstration with userform custom properties. To demonstrate the ED modeless cancel process perform the following:
Private Sub cmdOK_Click() ReportTextBox1ContentII Me.TextBox1 Unload Me End Sub Private Sub cmdCancel_Click() Unload Me End Sub Private Sub UserForm_Initialize() mod_FormControl.HideXCloseButton Me 'or 'mod_FormControl.DisableXCloseButton Me End Sub
 |
 |
In the next demonstration I am going to use a simple userform property "frmUserCanceled" as a logic router in the calling procedure. To set up the demonstration, perform the following:
'Public property variable Public frmUserCanceled As Boolean Private Sub cmdOK_Click() Me.Hide End Sub Private Sub cmdCancel_Click() 'Set form's custom property to indicate 'a cancel action has executed. Me.frmUserCanceled = True Me.Hide End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True cmdCancel_Click End If End Sub
For the last demonstration of controlling a form's lifetime, I am going to use a complex userform property "frmUserCanceled" as a logic router in the calling procedure. To set up the demonstration, perform the following:
'Private module level data variable Private mCancel As Boolean Private Sub cmdCancel_Click() mCancel = True Me.Hide End Sub Private Sub cmdOK_Click() Me.Hide End Sub Public Property Get frmUserCanceled() As Boolean frmUserCanceled = mCancel End Property Private Sub UserForm_Initialize() 'Initialize the private data variable. mCancel = False End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True cmdCancel_Click End Sub
This concludes the topic on controlling a form's scope or lifetime. It took me nearly a quarter of my adult lifetime to sort this out. So, I hope that if you have invested the time and made it to here, then you will have a better understanding of these principles and will have gained it in less time than it took me!
If I were a purist, or a full time paid professional, or perhaps not so lazy, I would make more effort to encapsulate my userform code and controls.
Often, I've seen and used code like the following in a standard module userform calling procedure:
Seems simple enough (it is), all I want to do is insert the value that my form user has entered in the the form's txtName textbox at the end of the document.
The problem is, that in doing so, I have exposed one of the userform's controls "txtName" to an external procedure. That in itself is not really a big deal, but suppose that I had exposed this control to a dozen or a hundred such external procedures and then, say due to some new government regulation, I had to change the name of my form control to "txtPersonalIdentifier."
Are you beginning to see the problem? As the developer, and by not encapsulating my userform controls (i.e., keeping them in house), I would have to go off and find all of the external procedures that depends on "txtName" to function properly and change them.
Remember, a userform is a class. The following userform and code samples represent my attempt to illustrate how encapsulation using custom properties (and methods and events if needed) of the userform class can simplify your coding and save you time in the long run.
I'm not going to layout step by step instructions how to create these examples. They are provided in the tips page demo pack that you can download using the link at the end of the page.
I have the following calling procedures in a standard code module in the project:
Sub CallDemoEncapsulateI()
Dim oFrm As UserForm7
Dim lngProduct As Long
Set oFrm = New UserForm7
oFrm.Show
If oFrm.frmUserCanceled = True Then
GoTo lbl_Exit
Else
'This demonstrates an exposed form control. If you later
'decide (or are compelled) to use a listbox, spinbutton
'or other form control for user input, you would
'have to fix this exposed control and similar exposed
'controls in every project that uses the form.
lngProduct = (5 * oFrm.txtMultiplier)
MsgBox lngProduct
End If
lbl_Exit:
Set oFrm = Nothing
Exit Sub
End Sub
Sub CallDemoEncapsulateII()
Dim oFrm As UserForm8
Dim lngProduct As Long
Set oFrm = New UserForm8
oFrm.Show
If oFrm.frmUserCanceled = True Then
GoTo lbl_Exit
Else
'Encapsulated custom form property.
lngProduct = (5 * oFrm.Multiplier)
MsgBox lngProduct
End If
lbl_Exit:
Set oFrm = Nothing
Exit Sub
End Sub
Sub CallDemoEncapsulateIII()
Dim oFrm As UserForm9
Dim lngProduct As Long
Set oFrm = New UserForm9
oFrm.Show
If oFrm.frmUserCanceled = True Then
GoTo lbl_Exit
Else
'Encapsulated custom form property.
MsgBox "I would most likely buy a " & oFrm.SurveyAnswer
' Compare to grossly exposed form controls:
' Select Case True
' Case oFrm.optChev
' MsgBox "I would most likely buy a Chevrolet"
' Case oFrm.optFord
' MsgBox "I would most likely buy a Ford"
' Case oFrm.optHonda
' MsgBox "I would most likely buy a Honda"
' Case oFrm.optToy
' MsgBox "I would most likely buy a Toyota"
' End Select
' Imagine the work involved it you need to add or remove
' makes of car or changed your form design to a list or
' combobox and these controls were exposed in multiple
' projects.
End If
lbl_Exit:
Set oFrm = Nothing
Exit Sub
End Sub
The userforms as presented to the user:
The userform code:
Private mCancel As Boolean
Private Sub cmdCancel_Click()
mCancel = True
Me.Hide
End Sub
Private Sub cmdOK_Click()
Me.Hide
End Sub
Public Property Get frmUserCanceled() As Boolean
frmUserCanceled = mCancel
End Property
Private Sub txtMultiplier_Change()
If Not IsNumeric(txtMultiplier.Text) Then
Beep
txtMultiplier.Text = ""
Exit Sub
End If
End Sub
Private Sub UserForm_Initialize()
mCancel = False
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
cmdCancel_Click
End If
End Sub
Private mCancel As Boolean
Private mMultiplier As Long
Private Sub cmdCancel_Click()
mCancel = True
Me.Hide
End Sub
Private Sub cmdOK_Click()
Me.Hide
End Sub
Public Property Get frmUserCanceled() As Boolean
frmUserCanceled = mCancel
End Property
Public Property Get Multiplier() As Long
Multiplier = mMultiplier
End Property
Private Sub lstMultiplier_Click()
mMultiplier = CLng(Me.lstMultiplier.Value)
End Sub
Private Sub UserForm_Initialize()
Dim lngIndex As Long
mCancel = False
For lngIndex = 1 To 10
Me.lstMultiplier.AddItem lngIndex
Next lngIndex
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
cmdCancel_Click
End If
End Sub
Private mCancel As Boolean
Private Sub cmdCancel_Click()
mCancel = True
Me.Hide
End Sub
Private Sub cmdOK_Click()
Me.Hide
End Sub
Public Property Get frmUserCanceled() As Boolean
frmUserCanceled = mCancel
End Property
Public Property Get SurveyAnswer() As String
Select Case True
Case optChev
SurveyAnswer = "Chevrolet"
Case optFord
SurveyAnswer = "Ford"
Case optHonda
SurveyAnswer = "Honda"
Case optToy
SurveyAnswer = "Toyota"
Case Else
SurveyAnswer = "a car not listed in the survey"
End Select
End Property
Private Sub UserForm_Initialize()
mCancel = False
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
cmdCancel_Click
End If
End Sub
The purpose (function) of a userform is to present information in a graphical interface to the user, get information back from the user, or both. That is all, nothing more, and nothing less.
Again, if I were a purist, and if I always practiced what I know and preach here, then if I had a command button or other control that does anything more than validate some data entered by the user (see the txtMultiplier_Change event in UserForm7 code above) and hides or unloads the form, I am guilty of function bloat!
If you have properly designed your form and employed encapsulation principles discussed earlier, then you should be able to perform any processes involving the data provided by the user either in the calling procedure or some other external procedure.
This section has is just getting started and will be a continuous work in progress. It is the general dumping ground for some advance tips, tricks and solutions to problems, difficult at the time, that I have encountered.
Do you need to protect your form user's from inquisitive onlookers? You can use the .PasswordChar property of a textbox to mask the user's text input.
Note: The PasswordChar property can also be set when you design the form using the VBE Properties Window.
UserForms are an object definition and objects can be stored in a collection. You can use a collection of secondary userforms when your form user needs to repeatedly enter similar data (e.g., list bank account information, list properties or stocks held, list names and ages of children, etc.. For a full discussion and examples see: Repeating Item List
As best I can tell, the WebBrowser control that is part of the userform toolkit has been relegated to the junk pile due to aggressive security updates in Microsoft's Internet Explorer application. Attempts to add the control to a userform can result in an error message:
What I have done, is create an code my own pseudo browser control using the click event of standard command button. You could, if you wished, code practically any event to browse to a targeted web address. To create you own browser control, follow these simple steps:
Public Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, _
ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Public Sub NewShell(cmdLine As String, lngWindowHndl As Long)
ShellExecute lngWindowHndl, "open", cmdLine, "", "", 1
lbl_Exit:
Exit Sub
End Sub
Private Sub cmdBrowseToPage_Click() Dim strWebAddress As String strWebAddress = "http://gregmaxey.com/word_tips.html" Call mod_BrowseToPage.NewShell(strWebAddress, 3) Me.Hide lbl_Exit: Exit Sub End Sub
When the user clicks the control, the targeted webpage is displayed using their default browser.
For a long time I mistakenly thought that a list and combobox was limited to 10 columns. That is only true for when a list or combobox is populated from an unbound data source as the following will demonstrate:
Private Sub cmdClose_Click() Me.Hide End Sub Private Sub ListBox1_Click() 'Return value of last column of the selected row. MsgBox Me.ListBox1.Column(34) End Sub Private Sub ListBox2_Click() 'Return value of last column of the selected row. MsgBox Me.ListBox2.Column(Me.ListBox2.ColumnCount - 1) & vbCr + vbCr _ & "With an unbounded data source you are limited to 10 columns (0-9)." End Sub Private Sub UserForm_Initialize() Dim arrDemo(34, 34) As String Dim i As Long, j As Long For i = 0 To 34 For j = 0 To 34 arrDemo(i, j) = "R " & i + 1 & " C " & j + 1 Next j Next i 'Set a breakpoint on the following line and then step through using F8 key. Debug.Print "arrDemo represents a bound data source containing " & UBound(arrDemo) + 1 & " columns." 'Using a bound data source, populate a listbox with 35 columns (0-34). With Me.ListBox1 .ColumnCount = UBound(arrDemo) + 1 'Let VBA determine and set column width (minimum is 72 points) .ColumnWidths = -1 .List = arrDemo End With 'Now attempt to create a 35 column listbox using unbound data. 'Continue stepping through the code until the error is raised. With Me.ListBox2 .ColumnCount = 35 On Error GoTo Err_Handler For i = 0 To 34 With Me.ListBox2 .AddItem For j = 0 To 34 .List(i, j) = "R" & i + 1 & " C" & j + 1 Next j End With Err_ReEntry: Next i End With lbl_Exit: Exit Sub Err_Handler: Me.ListBox2.ColumnCount = j 'Run the code to completion. Resume Err_ReEntry End Sub
I don't know if there is a column limit when populating a list or combobox using a bounded data source, but I know that it isn't 10 and it is more than 35. Whatever it is, I doubt that you will encounter it in your project.
For more on populating userform list and comboboxes, see my: Populate UserForm Listbox or Combobox
I would like to acknowledge the help and support of a few folks that have helped me to reach my current level of understanding of the topics presented here. First and foremost is Jonathan West. He saved me from the brink of insanity when I first discovered userforms. Others who have provided help along the way are Peter Hewitt, Doug Robbins and Jason Frost Simpson. Thank you all and any other person who has helped and I failed to mention.
That is it! I hope you have found this tips page helpful and informative. You can download a Word document containing all of the userforms and code demonstrated here: UserForm Advanced Tips Demo Pack
The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
This page was last updated on
May 08, 2022
Website
Privacy Statement
Back to top 
Back to
Word Tips