Using numerous MsgBox statements to display messages
Using Debug.Print statements to display messages
Using the Application.StatusBar to display messages
Each of these has disadvantages. For example,
MsgBox alerts require the user to click the OK button to dismiss the
message. Debug.Print statements don't show up on in the Excel window, so you
have to flip back VBA to see the messages, and you can't programmatically
clear the Immediate window. Application.StatusBar only displays one
message at a time, so one a subsequent message is displayed, the previous
message is lost forever.
The Alerter program addresses and fixes all of these shortcoming, and does
even more. Alert is an ActiveX DLL that can be used in any Office
program, versions 97 and later. Quite simply, Alerter displays a
modeless window in your Excel program. Because it is entirely
modeless, it remains visible in Excel as you work on the worksheet and as
your VBA code executes.
Alerter displays the following window in your Excel application window.
This window will float above your Excel window.
Installation Instructions
Download the DLL File in to a folder such as
C:\Windows\System
or C:\Alerter
.
On the Windows Start button, choose Run, and enter the following, including
the quotes as shown, and click OK.
RegSvr32
"C:\Alerter\Alert.dll"
Of course, change the folder path name to the location to which you downloaded
the DLL file.
Usage Instructions
In your VBA Project in the VBA Editor, go to the Tools menu, and then References. In the list, scroll down to the "Alert" entry and place a check next to it. This sets a reference from your VBA Project to the Alert DLL.
Then, declare and initialize the Alerter object. For example,
Public Alerter As Alert.Alerter
Sub InitAlerter()
If Alerter Is Nothing Then
Set Alerter = New Alert.Alerter
End If
With Alerter
.SetParentWindow -1
.Caption = "Alerts For Testing"
.EnableEvents = True
.FormVisible = True
.MaxListCount = 100
.MaxPriority = 1000
.MinPriority = 100
.ShowCounter = True
End With
End Sub
This will set up and initialize the Alerter object. To write messages
to the Alerter window, use a line of code like the following:
Alerter.DoAlert AlertText:="This
is a test.", Priority:=-1
The Priority argument is optional. See the Properties section below
for more details.
You may right-click on the Alerts list to copy a
message to the clipboard or to clear the list, or hide the window.
The Alerter Object
| Properties | Description |
| Caption | This sets or returns the caption of the Alerter form. String. Default is "Alert Messages" |
| Count | This returns the number of messages in the Alerter list. Read Only. Long. |
| EnableEvents | This sets or returns whether the Alerter should raise events to its container. Boolean. Default is True. |
| FormVisible | This sets or returns whether the Alerter window is visible. Boolean. Default is True. |
| HWnd | This returns the Windows Handle of the Alerter window. Read Only. Long. |
| LastMessage | This returns the last message in the Alerter list. Read Only. String. |
| Left | This returns the Left position of the Alerter window. Read Only. Single. |
| MaxListCount | This returns or sets the maximum number of messages to display in the list. When MaxListCount is reached, the earliest (top) messages are deleted to make room for later messages. Long. Default is 0, indicating no maximum. |
| MaxPriority | This sets or returns the maximum priority of messages to be displayed. If a message is sent with a priority greater than MaxPriority, it will not be displayed. The values you use for MaxPriorty, MinPriorty, and the Priority are entirely up to you. Alerter does not have any "default" settings. They only restrictions are that your priorities must be greater than equal to zero, and less than 9999999. Beyond that, priorities are entirely up to you. Long. Default is 9999999. |
| MinPriority | This sets or returns the minimum priority of messages to be displayed. If a message is sent with a priority less than MinPriority, it will not be displayed. This must be greater than or equal to 0. Long. Default is 0. |
| ReturnFocus | This sets or returns whether focus should return to the host application. Boolean. Default is True. |
| ShowCounter | Indicates whether the message counter ID should be displayed in the message text. This is relevant only on a message by message basis. It does not apply to existing messages. Boolean. Default is False. |
| ShowPriority | Indicates whether the message priority ID should be displayed in the message text. This is relevant only on a message by message basis. It does not apply to existing messages. Boolean. Default is False. |
| Top | This returns the Top position of the Alerter window. Read Only. Single. |
| Methods | |
| ClearList | Clears the Alerter list box. Message texts are permanently lost. |
| DeleteSelectedMessages | Deletes the selected messages. |
| DoAlert | Sends a message to the Alerter list. This takes two arguments. First, AlertText (required String) is the text to display. Second, Priority (optional Long). This indicates the priority of the message. See AlertText and AlertText for more details. If omitted, a value of -1 is used. Depending on the values of the ShowCounter and ShowPriority properties, the counter and priority values may be displayed in the list box. |
| ResetCounter | Resets the counter to zero. |
| SetLocation | Positions the location of the upper left corner of the Alerter window. This takes two arguments: Top and Left, both required Singles. The current values of the position are available through the Top and Left properties of the object. |
| SetParentWindow | Sets the parent window of the Alerter window.
If numeric and less than or equal to 0, the foreground window
(typically the Excel application window) is used. If numeric
and greater than zero, this must be the Windows handle of the parent
window. If text, this is the Window Class name of the new
parent window (e.g., "XLMain"). Typically, you should use
call this method with a value of -1, immediately following the
construction of the object. E.g,
Set Alerter = New Alert.Alert |
| Events | |
| AfterAlert | Raised after an alert has been posted to the Alerts list. |
| AfterClear | Raised after the Alerts list has been cleared. |
| AfterPriorityChange | Raised after either the MaxPriority or the MinPriority is changed. |
| BeforeAlert | Raised before an alert is posted to the Alerts list. Cancelable. |
| BeforeClear | Raised before the Alerts list is cleared. Cancelable. |
| BeforeCounterReset | Raised before the counter is reset to zero. Cancelable. |
| BeforeHide | Raised before the Alerter window is hidden. Cancelable. |
| BeforePriorityChange | Raised before either the MaxPriority or the MinPriority is changed. Cancelable. |
| BeforeShow | Raised before the Alerter window is shown. Cancelable. |
You can download the Alert.DLL file here.
You can download an example workbook
here.
If you are interested in the VB6 source code, send me an email message, explaining just why you want it ("just because" isn't good enough), and I might send you the source.
NOTE: There was a bug
in versions prior to 13:20PM (USA Central Time) 12-November-2002, that would
result in an "Invalid Procedure Call" error. Please download the new
version.
NOTE: Alerter would not run on some installations, because of incompatible
versions of the CommonDialog control. This problem was fixed on
15-November-2002. If you had this problem, please download the latest
version.