How to determine which control is currently hover over and which control just unhook

[n07cn3]

Hello Cristian,

i am a self learn beginner VBA code. i came across your VBA-UserForm-MouseScroll and it has work great for what i needed in the past. but i have a new need. is it possible to determine what MSforms control it hook and unhook?

Private Sub m_CommandButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) SetHoveredControl m_CommandButton End Sub

i need to find out which commandbutton it is currently hover over so i can call a sub. '******************************************************************************* 'Called by MouseMove capable controls (MouseOverControl) stored in m_controls '******************************************************************************* Public Sub SetHoveredControl(ctrl As Object) Set m_lastHoveredControl = ctrl End Sub

i also need to know what control is the last hover control so i can also call a sub.

not sure if this is possible but if it is can you please tell me how.

thank you.

Calvin

[cristianbuse]

Hi Calvin,

I am not sure I understand what you intended to say. Here are my thoughts/questions:

is it possible to determine what MSforms control it hook and unhook?

The form is not actually hooked. What is actually hooked is the mouse input. It just happens to be all tied to an instance of a UserForm. You could hook the mouse without having a UserForm at all. Since the only thing hooked is the mouse, at any given moment we need to know what was the last hovered control to be able to scroll it. That is why we keep track of all the controls MouseMove events.

i need to find out which commandbutton it is currently hover over so i can call a sub.

Is this needed while scrolling or is this something that you need separately? Assuming you want to take advantage of the m_controls collection of MouseOverControl.cls, which is already built, then you could always add a new method call in the m_CommandButton_MouseMove event if you want to keep track of all buttons.

Private Sub m_CommandButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
    SetHoveredControl m_CommandButton
    MyFunction m_CommandButton
End Sub

So you would call SetHoveredControl m_CommandButton and the you would call My Function m_CommandButton where MyFunction is a function that receives a parameter of type MSForms.CommandButton and does whatever you want. Of course, you could just create a second collection of Controls that you initialize whenever you want independently of the mouse hooking. If you only want to keep track of certain buttons, then just add MouseMove events directly in your UserForm.

i also need to know what control is the last hover control so i can also call a sub.

Again, you could just expose the m_lastHoveredControl if you are hooking anyway. Something like:

Public Function GetLastHoveredControl() as Object
    Set GetLastHoveredControl = m_lastHoveredControl
End Function

Or you could create a separate method that is called from the events in MouseOverControl.cls. Or, as in the previous point, you could create a separate collection of Controls that call back your preferred method, independently of the mouse hooking.

As you can see there any many ways to do things. You would need to be more specific in order for me to give you a proper answer.

Cheers, Cristian

[n07cn3]

Hello Cristian,

Thank you for replying to my post.

Sorry for not being clear. I was hoping that i can use what you had.

For my need, I need a way to find out what specific CommandButton is the mouse is currently hover over and when it leave.

Let's say I have to sub routine call Test1 and Test2.

When the mouse is hover over CommandButton3 sub Test1 is being call and when the mouse leave CommandButton3 sub Test2 is being call.

I thought that I can use this:

Private Sub m_CommandButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) SetHoveredControl m_CommandButton If m_CommandButton.Name = "CommandButton3 " Then Call Test1 ElseIf m_CommandButton.Name <> "CommandButton3 " Then Call Test2 End If End Sub

Yesterday I keep get error (i can't remember the exact message but i think it said something like ... invalid method)

but today it seem to be working fine (i don't know what is going on)

Can you please take a look at my if then statement above and let me know if this a proper way of coding?

If there is a better way of doing this, can you also show me?

thank you.

Calvin

[cristianbuse]

Hi Calvin,

I can see what you are trying to do. However, there is still one aspect I am not sure of. Do you want the Test2 method to be called as soon as you leave the button or only when when you hover a different button.

Let's start with the second assumption because it's easier: you only call Test2 when you hover over a different button. Your code should do the trick, however I would remove the ElseIf check as it's redundant:

Private Sub m_CommandButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    SetHoveredControl m_CommandButton
    If m_CommandButton.Name = "CommandButton3 " Then
        Call Test1
    Else
        Call Test2
    End If
End Sub

The first assumption is harder: call the Test2 method when the mouse is leaving the Button. You can not just use the m_CommandButton_MouseMove event because as soon as the mouse leaves the button it doesn't necesarilly hover over another button. It could be hovering above a frame or the form itself. You can overcome this by moving your code into the MouseScroll module directly into the SetHoveredControl (which is called by all hovered controls):

Public Sub SetHoveredControl(ctrl As Object)
    Set m_lastHoveredControl = ctrl
    '
    Dim isMyButton As Boolean
    '
    On Error Resume Next 'Needed because "ctrl" could be the Form itself
    isMyButton = (ctrl.Name = "CommandButton3")
    On Error GoTo 0
    '
    If isMyButton Then
        Test1
    Else
        Test2
    End If
End Sub

This would solve the issue of calling Test1 while above the desired button (could be frame, checkbox, listbox etc. because you are using the name to identify it) and calling Test2 while hovering outside of the desired button. However, I don't think you want to call Test1 and Test2 every time the mouse moves (that could be hundreds of times per second). I would think that you only want to call those 2 function once (one per entering and one per exiting). To achieve that you need to keep track of a simple boolean state, like this:

Public Sub SetHoveredControl(ctrl As Object)
    Set m_lastHoveredControl = ctrl
    '
    Static wasAlreadyOverMyButton As Boolean
    Dim isMyButton As Boolean
    '
    On Error Resume Next 'Needed because "ctrl" could be the Form itself
    isMyButton = (ctrl.Name = "CommandButton3")
    On Error GoTo 0
    '
    If isMyButton Then
        If Not wasAlreadyOverMyButton Then
            wasAlreadyOverMyButton = True
            Test1
        End If
    Else
        If wasAlreadyOverMyButton Then
            wasAlreadyOverMyButton = False
            Test2
        End If
    End If
End Sub

If you fancy using the Xor operator, then you could replace with:

Public Sub SetHoveredControl(ctrl As Object)
    Set m_lastHoveredControl = ctrl
    '
    Static wasAlreadyOverMyButton As Boolean
    Dim isMyButton As Boolean
    '
    On Error Resume Next 'Needed because "ctrl" could be the Form itself
    isMyButton = (ctrl.Name = "CommandButton3")
    On Error GoTo 0
    '
    If isMyButton Xor wasAlreadyOverMyButton Then
        wasAlreadyOverMyButton = isMyButton
        If isMyButton Then
            Test1
        Else
            Test2
        End If
    End If
End Sub

[n07cn3]

Hello Cristian,

Your last two suggestions is perfect for what I needed.

Sorry for not being clear on my original request.

Thanks again for helping me.

Calvin

[cristianbuse]

Hi Calvin,

Glad I could help!

Regards, Cristian