Monday, January 2, 2017

Excel VBA leave command button event

Imagine that you have a command button that you want it to be highlighted with certain color when mouse pointer hovers (or moves above) it and restores its color when the mouse pointer leaves it.

In Excel VBA it is easy to detect if mouse pointer is inside the command button using the event handler  CommandButton1_MouseMove, but the opposite is not easy.

In Excel VBA there is no built-in event handler that handles the mouse pointer leave (exit) event of command button. For this reason I figured out this easy and tricky way to do this. The following method can work also with other control types.

The idea simply is to create an invisible padding area for the command button, so if the mouse pointer moved in this padding area then this means that the mouse pointer has leaved the button.

To do this we need to create a label and place a command button on top of it. The label will have larger size (width and height) than the command button so that there is padding around the button in all directions (see image below). It is preferred to have equal padding in all directions. For the label, clear the caption and set back color style to transparent so it will not be visible at all. Then, align middles and centers of the command button and the label. Optionally, you can group both the command button and the label so you can move them together easily without losing padding.

Now, when the mouse pointer moves inside the command button area this will simulate the enter event and when the mouse pointer moves inside the label area this will simulate the exit or leave event.



It is worthy to say that the larger the padding you use, the faster mouse moves you can handle.

Finally, it is time of coding, the following is a the shortest and simplest code ever:


Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'Highlight button when enter command button
CommandButton1.BackColor = &HFF00&
End Sub
 
Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'Restore button color when leave command button
CommandButton1.BackColor = &H8000000F
End Sub


In case you have large number of command buttons and you want to apply the same effect on them, you may use only one label that will be moved, resized, and brought on the back of the button after mouse pointer enters the button.

This post solve the following problems found in the links below:

Mouse Over Objects. Detect When Mouse Leaves
http://www.ozgrid.com/forum/showthread.php?t=62478

MouseMove - What is the reverse event?
http://stackoverflow.com/questions/12200618/mousemove-what-is-the-reverse-event

Mouse over command button VBA code
http://www.mrexcel.com/forum/excel-questions/552646-mouse-over-command-button-visual-basic-applications-code.html

how to detect when the mouse leaves a control on a userform
http://www.vbaexpress.com/forum/archive/index.php/t-21367.html