Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Userform – Advanced Notes and Tips
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

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.

Userform Default Instance vs. Userform Explicit Declaration

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:

VBA Script:
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
VBA Script:
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:

VBA Script:
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:

VBA Script:
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:

  1. You shouldn't (can't in fact) use the default instance if you want to have more than one instance of your form visible at a time.
  2. For code included within the form, you should always use the Me keyword to refer to the current instance when acting on controls or properties of the form. This way, it will work whether you use the default instance or explicitly declared variable instances.
  3. You remain cognizant of how the default instance is auto-initiated whenever it is used.

To illustrate rule 3:

userform_advanced_notes_1
VBA Script:
Sub CallDefaultInstance()
  UserForm1.Show
  MsgBox UserForm1.TextBox1.Text
End Sub
VBA Script:
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.

Understanding Call Methods

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.

Modal Forms

The easiest way to do this is with a series of demonstrations and experiments.  To set up the demonstrations, perform these steps:

userform_advanced_notes_2
VBA Script:
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

VBA Script:
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 QuerryClose 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 QuerryClose " _
              & "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 QuerryClose 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 QuerryClose 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 QuerryClose and Terminate events were executed " _
              & "immediately in succession with the Unload statement." & vbCr + vbCr _
              & "If you want to ensure that a form's QuerryClose 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 QuerryClose 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

Modeless Forms

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:

VBA Script (userform):
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
VBA Script (standard module):
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 QuerryClose 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 QuerryClose 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:

Site Note IconNote:  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.

VBA Script:
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.

Initialize vs. Activate

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.

Controlling a Forms Scope or Lifetime

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.

"X" Close Button

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:

Intercept "X"

Clicking a form's "X" close button triggers the form's QuerryClose event.  The QuerryClose 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:

userform_advanced_notes_3
VBA Script (userform):
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 QuerryClose 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
VBA Script (standard module):
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:

VBA Script (userform):
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 QuerryClose 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

VBA Script (standard module):
Sub CallDefaultInstanceModeless()
  UserForm3.Show vbModeless
End Sub

Sub ReportTextBox1Content(ByRef oCtr As MSForms.TextBox)
  MsgBox oCtr.Text
End Sub

Hide/Disable ""X"

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:

VBA Script:
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:

VBA Script:
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

Userform Custom Properties

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 user 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:

VBA Script:
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
userform_advanced_notes_4 userform_advanced_notes_6

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:

VBA Script:
'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:

VBA Script:
'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!

Encapsulation

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:

VBA Script:
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:

userform_advanced_notes_6

userform_advanced_notes_7

userform_advanced_notes_8

The userform code:

VBA Script (UserForm7):
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
VBA Script (UserForm 8):
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


VBA Script (UserForm9:
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

Avoiding Function Bloat

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.

Miscellaneous Advanced Tips and Tricks

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.

Anti-Espionage Text

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.

userform_advanced_notes_9

Site Note IconNote: The PasswordChar property can also be set when you design the form using the VBE Properties Window.

Collect UserForms

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    

Code a Command Button or Other Control to GoTo a Targeted Web Address

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: 

userform_advanced_notes_10

Shown when attempting to add WebBrowser control to a userform.

userform_advanced_notes_11

Standard command button control coded to serve as a WebBrowser.

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: 

VBA Script:
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
VBA Script:
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.

List and ComboBox Column Limits

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:

userform_advanced_notes_12

Example userform with listboxes populated from a bound and unbound data source.
VBA Script:
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

Conclusion

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

Share

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo