Excel Trial Dialog, Create An Excel VBA UserForm With Info Icon And Timer Enabled OK Button

E

OK, so let’s start. We’ll arrange the UserForm after which add within the VBA Code. First Open Excel and press ALT+F11 to enter the VBA Editor. Go to the File Menu and select Insert–>UserForm. Identify the brand new UserForm MsgBoxCountdown and set its Top property to 132 and its Width property to 242. Add one Label to the UserForm and identify it lbTrialMsg (set its Caption property to ” This Message solely seems within the Trial Model of XXXX” with out the quotes) – this shall be our Trial or Nag Message to the person. We’ll prefix this Caption with a handed in bespoke Message. Add one additional Label to the UserForm and identify it lbCountDown (set its Caption property to “This Trial Dialog might be closed in” with out the quotes) – this shall be our Countdown Message to the person. Now add an Picture Management and cargo in a picture resembling a Query Mark – you may simply create or undertake a picture for this goal. Lastly add a Command Button and identify it btnOK, set its Caption to OK – this would be the button that shall be disabled till the timer interval has elapsed and the person is allowed to press it.

Tip: Why not add a Body Management simply over half the Top and the complete Width of the UserForm to carry the lbCountDown Label and btnOK Button after which set the BackColour of the UserForm itself to &H80000005& – this divides the UserForm giving it a very pleasing aesthetic high quality

You must now have a pleasant trying UserForm and be ready so as to add within the VBA Code. Double-click your OK Button to enter the VBA Code Editor for the UserForm Module. Amend the Subroutine generated to the next Code – you may paste over it if most popular:

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

‘ btnOK_Click, closes the UserForm

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

Non-public Sub btnOK_Click()

Unload Me

Finish Sub

Now add the next Code on the high of the Module – these are the Home windows API’S we shall be utilizing to re-style the Dialog Window and the Interval Variable that we’ve set to five (seconds) earlier than we allow the OK Button:

Possibility Specific

Non-public Declare Operate FindWindow Lib “user32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Lengthy

Non-public Declare Operate GetWindowLong Lib “user32” Alias “GetWindowLongA” (ByVal hwnd As Lengthy, ByVal nIndex As Lengthy) As Lengthy

Non-public Declare Operate SetWindowLong Lib “user32” Alias “SetWindowLongA” (ByVal hwnd As Lengthy, ByVal nIndex As Lengthy, ByVal dwNewLong As Lengthy) As Lengthy

Const WS_SYSMENU = &H80000

Const GWL_STYLE = (-16)

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

‘ Interval, set this to the time earlier than the OK Button is enabled

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

Non-public Const Interval = 5

OK, subsequent we’ll add the UserForm QueryClose Occasion Handler. This can be utilized to easily lure a Shut try on the Pink Cross should you do not need to take away it after we get to the UserForm Activate Occasion Handler in a minute. So add the next Code:

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

‘ UserForm_QueryClose, workaround should you do not need to conceal the Pink Shut Cross

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

Non-public Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

On Error GoTo QueryCloseErrorHandler

Software.EnableCancelKey = xlErrorHandler

If CloseMode = 0 Then

Cancel = True

MsgBox “Oops, the X on this Dialog has been disabled, please use the OK Button on the shape”, vbCritical, “Kiosk 4.1”

Finish If

Exit Sub

QueryCloseErrorHandler:

Resume Subsequent

Finish Sub

Almost there, now we have to add within the UserForm Activate Occasion Handler Code. That is the work-horse of the VBA Code and I’ll undergo it in a minute. Anyhow, add within the following Code (you may please your self whether or not you add within the feedback):

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

‘ UserForm_Activate, unusual little error dealing with routine, it goes…

‘ – fashion the userform to take away the purple cross

‘ – add a restart level for hack assaults

‘ – arrange an error handler & inform excel to make use of it

‘ – disable the OK Button

‘ – begin the countdown

‘ – on hack (CTR+Break) goto restart – that can start the entire course of over

‘ – if time up allow OK Button, OK will Unload the UserForm

‘ – within the interim DoEvents will help you nonetheless transfer the Dialog

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

Non-public Sub UserForm_Activate()

On Error Resume Subsequent

Dim hwnd, lStyle As Lengthy

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

Software.EnableCancelKey = xlErrorHandler

Me.btnOK.Enabled = False

Dim t As Single

t = Timer

Do

DoEvents

If err.Quantity = 18 Then GoTo restart

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

If err.Quantity = 18 Then GoTo restart

Me.lbCountDown.Caption = “This Trial Dialog might be closed in ” & Spherical(t + Interval – Timer, 0)

Else

If err.Quantity = 18 Then GoTo restart

Me.lbCountDown.Caption = “”

Finish If

Loop Whereas t + Interval > Timer

Me.btnOK.Enabled = True

Exit Sub

TrialErrorHandler:

Resume Subsequent

Finish Sub

The primary a part of the Code tells Excel that if it hits an error to skip over no matter what error has occurred. Not usually good apply however we are not looking for the Excel Debug field to ever be made obtainable to a person. We then fashion the Dialog Window to take away the purple cross Shut Button utilizing the Home windows API calls. NB: For Workplace 2000 and later we use the category identify ThunderDFrame (for Workplace 97, it isThunderXFrame). The lbTrialMsg Caption is then set to incude the UserForm Tag message that we set earlier than we present the UserForm plus the message we set earlier. In different phrases we will name this UserForm from anyplace in our Software passing in a Key Related Message that’s prefixed onto our pre-set one. We then add a Goto level known as restart: This shall be the place we soar to when a person presses the CTRL+Break Keystroke mixture. This additionally units up Excel to make use of one other Goto level for our Error Dealing with after which tells Excel that we want to solely use that time for all errors no matter they might be. Subsequent we disable the OK Button. We set the variable ‘t’ to the present Timer time and start looping till our interval has expired – the interval variable was set to five (seconds) earlier. Within the interim we use DoEvents to permit the Dialog to be moved round and the lbCountDown Caption to be up to date with our Countdown Message. When the interval expires we then clear the lbCountDown Caption. On the similar time we nonetheless lure the CTRL+Break Keystroke mixture after which allow the OK Button because the stream strikes out of the Do loop. We then exit the Subroutine. Used together with some VBA Module safety you may have a helpful little Countdown Message / Nag Dialog. If you happen to do not need to re-style the Dialog Window, easy eliminated the API’S and permit the UserForm QueryClose Occasion Handler to lure the Pink Shut Cross.

OK, lastly, double-click the ThisWorkbook Module and enter the next:

Possibility Specific

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

‘ DemonstrateMsgBoxCountdown, run this to see the Countdown Dialog

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

Public Sub DemonstrateMsgBoxCountdown()

MsgBoxCountdown.Tag = “(YOU CLICKED A FEATURE):”

MsgBoxCountdown.Present

Finish Sub

OK, now select Debug–>Compile VBAProject to compile your Code and examine for any errors. Click on anyplace within the Code you entered above and press F5 to Run the Sub/UserForm. You must see your UserForm seem. Strive urgent Ctrl+Break and watch the Timer interval enhance again as much as 5 seconds. That is it. Be at liberty to obtain the MsgBox Countdown instance Workbook from the hyperlinks beneath. I hope you preferred this text on Making a UserForm with Countdown, Info Icon and Timer Enabled OK Button in VBA for Excel. Mark Kubiszyn.

Take Control of Your Domain Names

Recent Posts

Categories

Advertisement