Create An Excel VBA UserForm

Create An Excel VBA UserForm

OK, so let’s begin. We will create an Excel VBA UserForm, an Excel Trial Dialog  with information icon and timer enabled OK button, and then add in the VBA Code.

  • First Open Excel and press ALT+F11 to enter the VBA Editor.
  • Go to the File Menu and choose Insert–>UserForm.  A window will open with a blank palette for the Excel VBA Userform
  • Name the new UserForm MsgBoxCountdown and set its Height property to 132 and its Width property to 242.
  • Add one Label to the Excel VBA UserForm and name it lbTrialMsg
  • Set its Caption property to This Message only appears in the Trial Version of XXXX (without the quotes) – this will be our Trial or Nag Message to the user. We will prefix this Caption with a passed in bespoke Message.
  • Add one further Label to the Excel VBA UserForm and name it lbCountDown
  • Set its Caption property to This Trial Dialog can be closed in (without quotes) – this will be our Countdown Message to the user.
  • Now add an Image Control and load in an image resembling a Question Mark – you can easily create or adopt an image for this purpose.
  • Finally add a Command Button and name it btnOK, set its Caption to OK – this will be the button that will be disabled until the timer interval has elapsed and the user is allowed to press it.

Tip: Why not add a Frame Control just over half the Height and the full Width of the Excel VBA UserForm to hold the lbCountDown Label and btnOK Button and then set the BackColor of the UserForm itself to &H80000005& – this divides the UserForm giving it a really pleasing aesthetic quality

You should now have a nice looking Excel VBA UserForm and be in a position to add in the VBA Code.

Double-click your OK Button to enter the VBA Code Editor for the UserForm Module. Amend the Subroutine generated to the following Code – you can paste over it if preferred:

' btnOK_Click, closes the UserForm
Private Sub btnOK_Click()
Unload Me
End Sub

Now add the following Code at the top of the Module – these are the Windows API’S we will be using to re-style the Dialog Window and the Interval Variable that we have set to 5 (seconds) before we enable the OK Button:

Option Explicit 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 Const WS_SYSMENU = &H80000 Const GWL_STYLE = (−16)
'=============================================================== ' Interval, set this to the time before the OK Button is enabled
Private Const Interval = 5

OK, next we will add the UserForm QueryClose Event Handler. This can be used to simply trap a Close attempt on the Red Cross if you don’t want to remove it when we get to the UserForm Activate Event Handler in a minute. So add the following Code:

UserForm_QueryClose, workaround if you don't want to hide the Red Close Cross
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo QueryCloseErrorHandler
Application.EnableCancelKey = xlErrorHandler
If CloseMode = 0 Then Cancel = True
MsgBox "Oops, the X in this Dialog has been disabled, please use the OK Button on the form", vbCritical, "Kiosk 4.1"
End If
Exit Sub
Resume Next
End Sub

Nearly there, now we need to add in the UserForm Activate Event Handler Code. This is the work-horse of the VBA Code and I will go through it in a minute. Anyhow, add in the following Code (you can please yourself whether you add in the comments):

' UserForm_Activate, strange little error handling routine, it goes...
' − style the userform to remove the red cross ' − add a restart point for hack attacks
' − set up an error handler & tell Excel to use it ' − disable the OK Button
' − start the countdown
' − on hack (CTR+Break) goto restart − that will begin the whole process over
' − if time up enable OK Button, OK will Unload the UserForm
' − in the interim DoEvents will allow you to still move the Dialog
Private Sub UserForm_Activate()
On Error Resume Next
Dim hwnd, lStyle As Long
hwnd = FindWindow("ThunderDFrame", Me.Caption)
lStyle = GetWindowLong(hwnd, GWL_STYLE)
SetWindowLong hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU
Me.lbTrialMsg.Caption = Me.Tag & Me.lbTrialMsg.Caption

restart: err.Clear
On Error GoTo TrialErrorHandler
Application.EnableCancelKey = xlErrorHandler
Me.btnOK.Enabled = False Dim t As Single
t = Timer
Do DoEvents
If err.Number = 18 Then GoTo restart
If Round(t + Interval − Timer, 0) > 0 Then
If err.Number = 18 Then GoTo restart    Me.lbCountDown.Caption = "This Trial Dialog can be closed in " & Round(t + Interval − Timer, 0)
If err.Number = 18 Then GoTo restart
Me.lbCountDown.Caption = ""
End If
While t + Interval > Timer
Me.btnOK.Enabled = True
Exit Sub
TrialErrorHandler: Resume Next
End Sub

The first part of the Code tells Excel that if it hits an error to skip over regardless of what error has occurred. Not normally good practice but we do not want the Excel Debug box to ever be made available to a user.

We then style the Dialog Window to remove the red cross Close Button using the Windows API calls. NB: For Office 2000 and later we use the class name ThunderDFrame (for Office 97, it isThunderXFrame). The lbTrialMsg Caption is then set to include the UserForm Tag message that we set before we show the UserForm plus the message we set earlier. In other words we can call this UserForm from anywhere in our Application passing in a Key Relevant Message that is prefixed onto our pre-set one.

We then add a Goto point called restart: This will be where we jump to when a user presses the CTRL+Break Keystroke combination. This also sets up Excel to use another Goto point for our Error Handling and then tells Excel that we wish to only use that point for all errors whatever they may be.

Next we disable the OK Button. We set the variable ‘t’ to the current Timer time and begin looping until our interval has expired – the interval variable was set to 5 (seconds) earlier. In the interim we use DoEvents to allow the Dialog to be moved around and the lbCountDown Caption to be updated with our Countdown Message. When the interval expires we then clear the lbCountDown Caption.

At the same time we still trap the CTRL+Break Keystroke combination and then enable the OK Button as the flow moves out of the Do loop. We then exit the Subroutine. Used in conjunction with some VBA Module protection you have a useful little Countdown Message / Nag Dialog.

If you don’t want to re-style the Dialog Window, simple remove the API’S and allow the UserForm QueryClose Event Handler to trap the Red Close Cross.

OK, finally, double-click the ThisWorkbook Module and enter the following:

Option Explicit
' DemonstrateMsgBoxCountdown, run this to see the Countdown Dialog '================================================================
Public Sub DemonstrateMsgBoxCountdown()
MsgBoxCountdown.Tag = "(YOU CLICKED A FEATURE):"
End Sub

OK, now choose Debug–>Compile VBAProject to compile your Code and check for any errors. Click anywhere in the Code you entered above and press F5 to Run the Sub/UserForm.

You should see your UserForm appear. Try pressing Ctrl+Break and watch the Timer interval increase back up to 5 seconds.

That’s it.  I hope you liked this article on Creating an Excel VBA UserForm.

Mark Kubiszyn

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.