How to display a Message Box After Loading a Form in Excel Using VBA

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.

BidSmart Estimator workbook form warning Message after opening.
BidSmart Estimator “Adjust Formatting” form warning Message

Why?

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 Application…

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.