OK lets start. We will configure the UserForm and then add the VBA code. First, open Excel and press ALT + F11 to enter the VBA Editor. Go to the File menu and choose Insert -> UserForm. Name the new UserForm MsgBoxCountdown and set its Height property to 132 and its Width property to 242. Add a label to the UserForm and name it lbTrialMsg (set its Caption property to “This message only appears in the test version of XXXX” without the quotes): this will be our test or complaint message for the user. We will prefix this title with a bespoke approved message. Add one more tag to the user form and name it lbCountDown (set its Caption property to “This test dialog can be closed” without the quotes) – this will be our countdown message to the user. Now add a picture control and upload an image that looks like 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 can press it.
Hint: Why not add a frame control a little more than half the height and full width of the user form to keep the lbCountDown tag and btnOK button and then set the background color of the user form to & H80000005 &? nice aesthetic quality
You should now have an attractive user form and be in a position to add the VBA code. Double click the OK button to enter the VBA code editor for the UserForm module. Modify the generated subroutine to the following code; you can paste it if you prefer:
‘==================================
‘btnOK_Click, close the UserForm
‘==================================
Sub private btnOK_Click ()
Download me
End Sub
Now add the following code to the top of the module – these are the Windows APIs that we will use to change the style of the dialog window and the interval variable that we have set to 5 (seconds) before enabling the OK button:
Explicit option
Private declaration function FindWindow Lib “user32″ Alias ”FindWindowA” (ByVal lpClassName as String, ByVal lpWindowName as String)
Private declaration function GetWindowLong Lib “user32″ Alias ”GetWindowLongA” (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private declaration 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 constant interval = 5
Okay, next we’ll add the UserForm QueryClose event handler. This can simply be used to catch a close attempt on the Red Cross if you don’t want to remove it when we get to the UserForm trigger event handler in a minute. So add the following code:
‘============================================== = = ==============================
‘UserForm_QueryClose, workaround if you don’t want to hide the Red 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, use the OK button on the form”, vbCritical, “Kiosk 4.1”
It will end if
Exit Sub
QueryCloseErrorHandler:
Summary Next
End Sub
Almost there, now we need to add the UserForm trigger event handler code. This is the VBA Code workhorse and I’ll go over it in a minute. Anyway, add the following Code (you may be pleased if you add in the comments):
‘============================================== = = ==========================
‘UserForm_Activate, weird error handling routine, goes …
‘: style the user form to remove the red cross
‘- add a reset point for hacking attacks
‘- set up an error handler and tell Excel to use it
‘- disable OK button
‘- start the countdown
‘- in hack (CTR + Break) go to restart – it will start the whole process all over again
‘- if time runs out, enable OK button, OK will download user form
‘- meanwhile, DoEvents will let you move the dialog
‘============================================== = = ==========================
Private Sub UserForm_Activate ()
In case of 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
In case of error, go to TrialErrorHandler
Application.EnableCancelKey = xlErrorHandler
Me.btnOK.Enabled = False
Dim t as unique
t = timer
Do
DoEvents
If err.Number = 18, go to reboot
If Round (t + Interval – Timer, 0)> 0 Then
If err.Number = 18, go to reboot
Me.lbCountDown.Caption = “This test dialog can be closed in” & Round (t + Interval – Timer, 0)
The rest
If err.Number = 18, go to reboot
Me.lbCountDown.Caption = “”
It will end if
Loop while t + Interval> Timer
Me.btnOK.Enabled = True
Exit Sub
TrialErrorHandler:
Summary Next
End Sub
The first part of the Code tells Excel that if it encounters an error, it should ignore it regardless of the error that occurred. This is not normally good practice, but we don’t want the Excel debugging box to be available to a user. Next, we style the dialog window to remove the close button from the red cross using the Windows API calls. NB: For Office 2000 and later we use the class name ThunderDFrame (for Office 97, it is ThunderXFrame). The title lbTrialMsg is configured to include the UserForm tag message that we configured before displaying the UserForm plus the message that we configured earlier. In other words, we can call this userform from anywhere in our application by passing a relevant key message that precedes our preset. Then we add a Goto point called restart – this is where we jump when a user presses the CTRL + Break keys combination. This also sets Excel to use another Goto point for our error handling and then tells Excel that we only want to use that point for all errors, whatever they are. Next, we disable the OK button. We set the variable ‘t’ to the current time of the timer and start looping until our interval has expired; the interval variable was set to 5 (seconds) earlier. In the meantime, we use DoEvents to allow the dialog to move and the lbCountDown title to update with our countdown message. When the interval expires, we delete the title lbCountDown. At the same time, we keep catching the CTRL + Break Keystroke combination and then enable the OK button as the flow exits the Do loop. Then we exit the subroutine. Used in conjunction with some protection from the VBA module, it has a handy little Countdown / Nag Message dialog box. If you don’t want to change the style of the dialog window, just remove the APIs and let the UserForm QueryClose event handler catch the Red Cross.
OK, finally, double click on the ThisWorkbook module and enter the following:
Explicit option
‘============================================== = = ===============
‘DemonstrateMsgBoxCountdown, run this to see the Countdown dialog
‘============================================== = = ===============
Public Sub DemonstrateMsgBoxCountdown ()
MsgBoxCountdown.Tag = “(YOU HAVE CLICKED ON A FUNCTION):”
MsgBoxCountdown.Show
End Sub
Okay, now choose Debug -> Compile VBAProject to compile your Code and check for 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 + Pause and notice how the timer interval increases back to 5 seconds. That is all. Feel free to download the sample MsgBox Countdown workbook from the links below. Hope you liked this article on creating user form with countdown, info icon and timer OK button enabled in VBA for Excel. Mark Kubiszyn.