Okay, so let’s get started. We will configure the user form and then add the VBA code. First open Excel and press ALT+F11 to enter the VBA Editor. Go to the File menu and select 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 trial version of XXXX” without the quotes ) – this will be our Test Message or Nag to the user. We will prefix this title with a personalized message. Add an additional label 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 an image control and load an image that resembles 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.

Tip: Why not add a frame control a little over half the height and full width of the userform to hold the lbCountDown tag and the btnOK button, and then set the background color of the userform to &H80000005& ? This breaks up the userform and gives it a really nice aesthetic. quality

You should now have a nice looking userform and be ready to add the VBA code. Double-click its 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

‘===================================

Private Subscription btnOK_Click()

download me

finish sub

Now add the following code to the top of the module – these are the Windows APIs we’ll use to change the style of the dialog window and the interval variable we’ve 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) While

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

Constant WS_SYSMENU = &H80000

Const GWL_STYLE = (-16)

‘================================================== = ===========

‘ Interval, set to the time before the OK button is enabled

‘================================================== = ===========

private const interval = 5

Ok, next we’ll add the UserForm QueryClose event handler. This can be used to simply catch a close attempt on the red cross if you don’t want to kill it when we get to the userform fire event handler in a minute. So add the following code:

‘================================================== = ===========================

‘ UserForm_QueryClose, workaround if you don’t want to hide the closing red cross

‘================================================== = ===========================

Private Sub UserForm_QueryClose (Cancel As Integer, Close Mode As Integer)

In case of error Go to Query CloseErrorHandler

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”

it will end if

ExitSub

QueryCloseErrorHandler:

SummaryNext

finish sub

Almost there, we now need to add the Userform Activation Event Handler Code. This is the workhorse of VBA Code and I’ll go over it in a minute. Anyway, add the following code (you can please it if you add it in the comments):

‘================================================== = =========================

‘ UserForm_Activate, weird little error handling routine, goes…

‘ – style the userform to remove the red cross

‘-add a restart point for hack attacks

‘ – set up an error handler and tell Excel to use it

‘ – disable OK button

‘ – the countdown begins

‘ – on hack (CTR+Break) go to restart – that will start the whole process again

‘ – if timeout, enable ok button, ok will unload userform

‘ – meanwhile, DoEvents will allow you to move the dialog

‘================================================== = =========================

Private Subuser Form_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.Delete

On error, go to TrialErrorHandler

Application.EnableCancelKey = xlErrorHandler

Me.btnOK.Enabled = False

Dim t as single

t = timer

Make

make events

If err.Number = 18 Then Go to restart

If Round(t + Interval – Timer, 0) > 0 Then

If err.Number = 18 Then Go to restart

Me.lbCountDown.Caption = “This test dialog can be closed on ” & Round(t + Interval – Timer, 0)

The rest

If err.Number = 18 Then Go to restart

Me.lbCountDown.Caption = “”

it will end if

Loop While t + Interval > Timer

Me.btnOK.Enabled = True

ExitSub

Test error handler:

SummaryNext

finish sub

The first part of the code tells Excel that if an error occurs, to ignore it, regardless of what error occurred. This is normally not a good practice, but we don’t want the Excel debug box to ever be available to a user. We then designed the dialog to remove the red cross Close button using Windows API calls. NB: For Office 2000 and later, we use the class name ThunderDFrame (for Office 97, it’s ThunderXFrame). The lbTrialMsg subtitle is then set to include the userform tag message we set before displaying the userform plus the message we set earlier. In other words, we can call this userform from anywhere in our application by passing a key relevant message that prepends our preset. Next, we add a Go To point called restart – this will be where we jump to when a user presses the CTRL+Break keystroke combination. This also configures 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 ‘t’ variable to the current timer time and start looping until our interval has expired: the interval variable was set to 5 (seconds) before. In the meantime, we use DoEvents to allow the dialog to move and the lbCountDown caption to update with our countdown message. When the interval expires, we delete the title lbCountDown. At the same time, we continue to catch 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 VBA module protection, it has a handy little countdown message/annoying dialog. 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 closing red cross.

Ok, finally double click on the ThisWorkbook module and enter the following:

explicit option

‘================================================== = =============

‘ demonstrate MsgBoxCountdown, run this to see the countdown dialog

‘================================================== = =============

Public Sub Demonstrate MsgBoxCountdown()

MsgBoxCountdown.Tag = “(CLICKS A FUNCTION):”

MsgBoxCountdown.Show

finish sub

Ok, now choose Debug–>Compile VBAProject to compile your code and check for errors. Click anywhere in the code you entered earlier and press F5 to run the Sub/UserForm. You should see your user form appear. Try pressing Ctrl+Break and notice how the timer interval increases back to 5 seconds. That’s it. Feel free to download the MsgBox Countdown sample workbook from the links below. Hope you liked this article on how to create a user form with countdown, info icon and timer-enabled OK button in VBA for Excel. Mark Kubiszyn.