Showing a VBA Message Box (MsgBox) after a form displays is easy enough to do with some simple coding. There are solutions for some Office applications that use timers, or events, that are not available in Excel. But this one is easy and clean, and it works.
My need, and the reason for the message, was to warn the user to be careful when reformatting the estimate. The notice would not be a fatal error, but rather a “tip” that might help the user avoid some extra work. It only needed to be seen when the Adjust Formatting form was called from the New Estimate Wizard. Therefore, I also required some conditional coding to open the MsgBox, or not.
The method I use in my BidSmart Estimator is to place the code to display the message box in the form’s Activate event. This works because that event is run after the form is displayed by the code in the Initialize event. Since the timing is right, this would be the place to put the MsgBox code.
The MsgBox code is placed within the form’s Activate event. It could be placed ahead of or behind any code as long as they do not conflict. I put it at the end just to be neat. After all, it is meant to run last after the form loads.
The following code segment, first checks for whether the frmNewWzard is loaded by asking the IsFormLoaded() User Defined Function (UDF). You can add any conditional code you need as a reason to show the MsgBox or not.
Private Sub UserForm_Activate() ' This should remove the visible page breaks, unless they are set to show ;-) '...and display a warning, if we are called by the frmNewWizard ' Set the Display of Page Breaks according to the hidden variable Sheet1.DisplayPageBreaks = Sheet1.Range("V13") If IsFormLoaded("frmNewWizard") = True Then MsgBox "If you REFORMAT the new estimate, make sure that you have enough lines for the line items you wish to import..." & vbCrLf & vbCrLf _ & "If in doubt, simply CLOSE the ""Adjust Formatting"" form now...there are currently sufficient lines for any import.", vbInformation + vbOKOnly, "Reformatting tip" End If End Sub
My Only Condition…the IsFormLoaded() UDF
The IsFormLoaded function takes only one argument, the name of a form. The return value is True, if the form is loaded in memory, False if it is not. The reason this UDF is sometimes needed is that a form that calls another may be hidden or otherwise not visible.
There are more complex examples of this UDF that will not only return whether the form is loaded, but also its visibility state. This version suffices for my needs, and so far is all that I have needed in my VBA coding. I must say now that I did not come up with this code, but I acquired it so long ago that I cannot remember to whom to give credit.
Public Function IsFormLoaded(FrmName As String) As Boolean ' Is the form named in the arg loaded or not...visibility does not matter Dim Frm As Object On Error GoTo errHandler IsFormLoaded = False For Each Frm In VBA.UserForms ' We use LCase$ just to ensure my spelling mistakes are not a problem If LCase$(Frm.Name) = LCase$(FrmName) Then IsFormLoaded = True Exit Function End If Next Exit Function errHandler: IsFormLoaded = False End Function
That is all there is to it…no timers or hidden “OnLoad” events to locate.