Excel

Notice the lotus links in mine :-) I don't use Lotus, it's there as a just in case.

Why the system tray? well why not, we could have minimized it to the taskbar, have a look here to do this. Minimzing it here keeps it out of the taskbar clutter and is useful for keeping it readily available @ a click (actually a double click, but you can change this......) or giving right click options (See below)

In order to minmize the Userform to the system tray takes a bit of work, more so then minmizing to the taskbar, as I found when trying to do this. Remember, when programing using API's or anything for that matter....save, save and save! The number of crashes I endured through to get this to work!

Here are the keys to making this work in Excel;

Key1 - Every Window has a Windows function that processes messages sent by an Application or Operating system. This Windows function has 4 parameters that it accepts, the Windows handle, message number and 2 X 32bit parametres that can vary depending on the message. It is by using the function that we are able to subclass a Window.

Key2 - Getting the address of the function via the Addressof operator.

Key3 - Getting the correct message ID or number to test for our mouseover.

Key4 - Handling the minimizing via some Event.

Key5 - Making sure we handle other messages.

Key6 - Ensuring we Unsubclass when required.

Key7 - Handling click events and there menus.

Key8 - Make sure you handle the WorkBook close, remember it is Excel and NOT a VB Application.

Subclass the Window and use a call to intercept the windows msg that we setup to run our routine. As I mentioned earlier a window receives this message through its WindowProc function. The WM_LBUTTONDBLCLK message is posted when the user double-clicks the left mouse button while the cursor is in the client area of a window, in this case we are looking for the system tray window, which I found through trial and error is, actually the userforms window handle WHEN minimized. If the mouse is not captured, the message is posted to the window beneath the cursor. Otherwise, the message is posted to the window that has captured the mouse.

If the user doubleclicks the SystemTray icon then we MUST release the hook on the window. In other words un-subclass it by returning it's original WNDPROC address that we captured in the original call to SetWindowLong (Windows Form Minimized) This is important when showing the userform. The call to do this involves the Adressof Operator available in VBA6 Versions ie Xl2000+

Minimizing the form and handling the Event is done by the Userform resize event.