VBA MouseMove event question

[pujo]

While playing around with Excel, I an trying to create a basic counter.
Each time the mouse moves over an ActiveX Label, I want to increase the number in a cell by one.

This seemed pretty straight forward however... When the event occurs, depending on the speed of the mouse, the number will increase by more than 1.
This is only a simple test for an idea I have and no other code after, just a simple counter on the MouseMove event.
Maybe someone can assist me figuring this out.

Thanks!

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'MouseMove Test
  Sheet10.Range("F16") = Range("F16").Value + 1 'Increment by one
    If Sheet10.Range("F16").Value > 10 Then Sheet10.Range("F16").Value = "0" 'When counter reaches 10 reset to 0.
End Sub

 

[GWteB]

You could modify the event handler so that it only does the action you want once. To do this, you will have to raise a flag (boolean type variable at module level) if the action has already been performed. The handler also has to check whether that flag is raised.

Obviously, the flag needs to be lowered prior to a next event. The tricky part is that there is no such thing as a MouseLeave event and if your current label is on a worksheet, there's also not a WorksheetMouseMove event.

You may be able to circumvent this by introducing a second label and placing it behind your current label (Z-order). The mouse move event of that second label can then lower the flag.
The event handler of your current label has to do the following:
- make the second label transparant with no borders;
- resize the second label in a way it will be much bigger than the first label so its mouse move event handler is able to respond when the mouse no longer hoovers over the first label.
The event handler of the second label has to do the following:
- lower the flag;
- resize the second label so its size doesn't exceed the size of the first label anymore;
- move the second label in a way it disappears behind the first label.

Just an approach to think about. No idea if this will work when used on a worksheet.