Ok, so let us start. We will set up the UserForm and then add in the VBA Code. 1st Open Excel and push ALT+F11 to enter the VBA Editor. Go to the File Menu and choose Insert–>UserForm. Name the new UserForm MsgBoxCountdown and set its Top property to 132 and its Width house to 242. Insert just one Label to the UserForm and title it lbTrialMsg (established its Caption house to ” This Information only appears in the Trial Version of XXXX” without having the rates) – this will be our Demo or Nag Message to the consumer. We will prefix this Caption with a passed in bespoke Concept. Increase a single even further Label to the UserForm and title it lbCountDown (set its Caption house to “This Trial Dialog can be shut in” without the prices) – this will be our Countdown Concept to the user. Now incorporate an Picture Management and load in an picture resembling a Dilemma Mark – you can quickly build or undertake an picture for this purpose. Eventually include a Command Button and name it btnOK, established its Caption to Okay – this will be the button that will be disabled till the timer interval has elapsed and the user is allowed to push it.
Suggestion: Why not incorporate a Body Manage just in excess of half the Peak and the total Width of the UserForm to hold the lbCountDown Label and btnOK Button and then set the BackColour of the UserForm by itself to &H80000005& – this divides the UserForm providing it a genuinely satisfying aesthetic good quality
You need to now have a great searching UserForm and be in a placement to increase in the VBA Code. Double-click on your Ok Button to enter the VBA Code Editor for the UserForm Module. Amend the Subroutine produced to the next Code – you can paste over it if most popular:
‘=================================
‘ btnOK_Click on, closes the UserForm
‘=================================
Personal Sub btnOK_Click on()
Unload Me
Conclusion Sub
Now increase the next Code at the prime of the Module – these are the Windows API’S we will be working with to re-design the Dialog Window and the Interval Variable that we have set to 5 (seconds) ahead of we empower the Okay Button:
Solution Explicit
Personal Declare Purpose FindWindow Lib “consumer32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Very long
Personal Declare Purpose GetWindowLong Lib “person32” Alias “GetWindowLongA” (ByVal hwnd As Extensive, ByVal nIndex As Extended) As Extensive
Private Declare Function SetWindowLong Lib “person32” Alias “SetWindowLongA” (ByVal hwnd As Prolonged, ByVal nIndex As Prolonged, ByVal dwNewLong As Long) As Lengthy
Const WS_SYSMENU = &H80000
Const GWL_Type = (-16)
‘===============================================================
‘ Interval, established this to the time prior to the Alright Button is enabled
‘===============================================================
Non-public Const Interval = 5
Alright, following we will add the UserForm QueryClose Event Handler. This can be used to merely entice a Near try on the Crimson Cross if you do not want to get rid of it when we get to the UserForm Activate Function Handler in a minute. So increase the next Code:
‘==============================================================================
‘ UserForm_QueryClose, workaround if you really don’t want to conceal the Crimson Shut Cross
‘==============================================================================
Non-public Sub UserForm_QueryClose(Terminate As Integer, CloseMode As Integer)
On Mistake GoTo QueryCloseErrorHandler
Software.EnableCancelKey = xlErrorHandler
If CloseMode = Then
Terminate = Genuine
MsgBox “Oops, the X in this Dialog has been disabled, please use the Ok Button on the form”, vbCritical, “Kiosk 4.1”
Stop If
Exit Sub
QueryCloseErrorHandler:
Resume Following
End Sub
Just about there, now we have to have to add in the UserForm Activate Occasion Handler Code. This is the do the job-horse of the VBA Code and I will go through it in a moment. Anyhow, incorporate in the subsequent Code (you can please you whether you include in the feedback):
‘============================================================================
‘ UserForm_Activate, peculiar tiny error handling program, it goes…
‘ – type the userform to get rid of the purple cross
‘ – add a restart level for hack assaults
‘ – set up an error handler & tell excel to use it
‘ – disable the Okay Button
‘ – commence the countdown
‘ – on hack (CTR+Split) goto restart – that will start off the total procedure above
‘ – if time up permit Alright Button, Okay will Unload the UserForm
‘ – in the interim DoEvents will let you to continue to transfer the Dialog
‘============================================================================
Non-public Sub UserForm_Activate()
On Error Resume Upcoming
Dim hwnd, lStyle As Long
hwnd = FindWindow(“ThunderDFrame”, Me.Caption)
lStyle = GetWindowLong(hwnd, GWL_Style)
SetWindowLong hwnd, GWL_Type, 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 = Bogus
Dim t As Single
t = Timer
Do
DoEvents
If err.Variety = 18 Then GoTo restart
If Round(t + Interval – Timer, ) > Then
If err.Range = 18 Then GoTo restart
Me.lbCountDown.Caption = “This Trial Dialog can be shut in ” & Spherical(t + Interval – Timer, )
Else
If err.Variety = 18 Then GoTo restart
Me.lbCountDown.Caption = “”
Close If
Loop When t + Interval > Timer
Me.btnOK.Enabled = True
Exit Sub
TrialErrorHandler:
Resume Subsequent
Close Sub
The to start with section of the Code tells Excel that if it hits an mistake to skip more than regardless of what mistake has happened. Not typically good exercise but we do not want the Excel Debug box to ever be produced available to a user. We then style the Dialog Window to remove the crimson cross Near Button utilizing the Windows API calls. NB: For Workplace 2000 and later on we use the course identify ThunderDFrame (for Office 97, it isThunderXFrame). The lbTrialMsg Caption is then established to incude the UserForm Tag message that we established ahead of we present the UserForm in addition the message we set previously. In other text we can contact this UserForm from anywhere in our Software passing in a Crucial Relevant Concept that is prefixed on to our pre-established just one. We then increase a Goto place referred to as restart: This will be wherever we jump to when a consumer presses the CTRL+Break Keystroke mix. This also sets up Excel to use a further Goto place for our Mistake Handling and then tells Excel that we wish to only use that level for all mistakes regardless of what they might be. Upcoming we disable the Ok Button. We set the variable ‘t’ to the latest Timer time and start off looping until our interval has expired – the interval variable was established to 5 (seconds) previously. In the interim we use DoEvents to let the Dialog to be moved about and the lbCountDown Caption to be up to date with our Countdown Message. When the interval expires we then apparent the lbCountDown Caption. At the exact time we continue to lure the CTRL+Split Keystroke blend and then empower the Ok Button as the circulation moves out of the Do loop. We then exit the Subroutine. Applied in conjunction with some VBA Module protection you have a useful tiny Countdown Concept / Nag Dialog. If you do not want to re-type the Dialog Window, very simple eradicated the API’S and permit the UserForm QueryClose Party Handler to trap the Pink Close Cross.
Ok, finally, double-simply click the ThisWorkbook Module and enter the pursuing:
Selection Explicit
‘=================================================================
‘ DemonstrateMsgBoxCountdown, run this to see the Countdown Dialog
‘=================================================================
Public Sub DemonstrateMsgBoxCountdown()
MsgBoxCountdown.Tag = “(YOU CLICKED A Aspect):”
MsgBoxCountdown.Clearly show
Conclusion Sub
Alright, now opt for Debug–>Compile VBAProject to compile your Code and test for any problems. Simply click everywhere in the Code you entered previously mentioned and press F5 to Operate the Sub/UserForm. You should really see your UserForm appear. Try pressing Ctrl+Break and watch the Timer interval raise again up to 5 seconds. That is it. Really feel free to download the MsgBox Countdown case in point Workbook from the hyperlinks down below. I hope you appreciated this report on Generating a UserForm with Countdown, Facts Icon and Timer Enabled Alright Button in VBA for Excel. Mark Kubiszyn.
COMMENTS