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

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