change language:

Forms in Excel and Word are quite static. To improve the user experience it would be nice if the command buttons on the forms get a hover effect. This means, for example, that the background color and mouse pointer changes when the mouse is moved over them:

create button hover effect

This makes the forms more dynamic. However, this is not standard functionality of a command button.

This article presents a very flexible method where a hover effect can be added to all command buttons on a form by adding some lines of code to that form. No further changes need to be made to the form. No other controls need to be added or the like, but a few extra lines of code will suffice.

The hover effect is controlled using two class modules. In the first class module, a reference is made to the relevant form and references are automatically made from this class to all command buttons present on the form. The hover effect mainly uses the MouseMove event of the command buttons and the form.

In order to be able to apply the hover effect, two class modules must first be created (via Insert Class Module). Name the first class module clFormHoverEffect and add the following code:

'------------------------------------------------------------------------------------------------------------------------------
' Auteur    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, alle rechten voorbehouden
' Versie    : 1.0
' Datum     : 2020-11-10
' Doel      : Create Button Hover effect
'------------------------------------------------------------------------------------------------------------------------------

Private WithEvents frmHover As MSForms.UserForm
Private oDicButtons As Object, sHoveredButton As String

Private Sub Class_Initialize()
    Set oDicButtons = CreateObject("scripting.dictionary")
End Sub

Friend Property Set HoverForm(ByRef oFrm As Object)
    Set frmHover = oFrm
End Property

Friend Sub AddButtons()
    Dim ctl As Control, oHoverButton As clHoverButton
    For Each ctl In frmHover.Controls
        If TypeName(ctl) = "CommandButton" Then
            Set oHoverButton = New clHoverButton
            With oHoverButton
                Set .ParentForm = Me
                Set .HoverButton = ctl
                .Initialize
            End With
            Set oDicButtons(ctl.Name) = oHoverButton
            Set oHoverButton = Nothing
        End If
    Next
End Sub

Private Sub frmHover_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    RemoveHoverEffect
End Sub

Friend Sub ApplyHoverEffect(ByVal sCtlName As String)
    If sHoveredButton = vbNullString Or sHoveredButton <> sCtlName Then
        If sHoveredButton <> sCtlName Then RemoveHoverEffect
        frmHover.Controls(sCtlName).BackColor = &H85E8FF
        sHoveredButton = sCtlName
    End If
End Sub

Friend Sub RemoveHoverEffect()
    If sHoveredButton <> vbNullString Then
        frmHover.Controls(sHoveredButton).BackColor = oDicButtons.Item(sHoveredButton).InitialBackColor
        sHoveredButton = vbNullString
    End If
End Sub

Friend Sub TerminateButtonHover()
    oDicButtons.RemoveAll
    Set oDicButtons = Nothing
End Sub

Private Sub Class_Terminate()
    Set frmHover = Nothing
End Sub

Name the second class module clHoverButton and add the following code:

'------------------------------------------------------------------------------------------------------------------------------
' Auteur    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, alle rechten voorbehouden
' Versie    : 1.0
' Datum     : 2020-11-10
' Doel      : Create Button Hover effect
'------------------------------------------------------------------------------------------------------------------------------

Private oParentForm As clFormHoverEffect, iInitialBackColor As Long
Private WithEvents oCmdBtn As MSForms.CommandButton

Friend Property Set ParentForm(ByRef oValue As clFormHoverEffect)
    Set oParentForm = oValue
End Property

Friend Property Set HoverButton(ByRef oValue As MSForms.CommandButton)
    Set oCmdBtn = oValue
End Property

Private Property Let InitialBackColor(ByVal iValue As Long)
    iInitialBackColor = iValue
End Property

Public Property Get InitialBackColor() As Long
    InitialBackColor = iInitialBackColor
End Property

Friend Sub Initialize()
    With oCmdBtn
        InitialBackColor = .BackColor
        .MousePointer = fmMousePointerCustom
        If Len(Dir(ThisWorkbook.Path & "\HandCursor.ico")) Then
            .MouseIcon = LoadPicture(ThisWorkbook.Path & "\HandCursor.ico")
        End If
    End With
End Sub

Private Sub oCmdBtn_Click()
    oParentForm.RemoveHoverEffect
End Sub

Private Sub oCmdBtn_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not oCmdBtn.Locked Then
        If X > 2 And X < oCmdBtn.Width - 2 And Y > 2 And Y < oCmdBtn.Height - 2 Then
            oParentForm.ApplyHoverEffect oCmdBtn.Name
        Else
            oParentForm.RemoveHoverEffect
        End If
    End If
End Sub

Private Sub Class_Terminate()
    Set oCmdBtn = Nothing
    Set oParentForm = Nothing
End Sub

These two class modules control the hover effect of the command buttons. The first class is linked to the form and the second class to the command buttons of the form.

In order for this hover effect to work on a form, the following variable must be declared at the very top of the code screen of a form:

Private oHoverForm As New clFormHoverEffect

Then the following 5 lines must be added to the UserForm_Initialize event:

    Set oHoverForm = New clFormHoverEffect
    With oHoverForm
        Set .HoverForm = Me
        .AddButtons
    End With

And finally the following 2 lines must be added to the UserForm_QueryClose event:

    oHoverForm.TerminateButtonHover
    Set oHoverForm = Nothing

So the all code behind a form that is needed for the hover effect would look like this:

Private oHoverForm As New clFormHoverEffect

Private Sub UserForm_Initialize()
    Set oHoverForm = New clFormHoverEffect
    With oHoverForm
        Set .HoverForm = Me
        .AddButtons
    End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    oHoverForm.TerminateButtonHover
    Set oHoverForm = Nothing
End Sub
The hover effect is only applied to command buttons that are enabled and not locked. The mouse pointer is also changed. The latter only happens if an ico file with the name HandCursor.ico is present in the same folder as the file. Click on the button below to download an example file. In this example, the ico file is also included.
zipCreate button hover effect 1.0
 

Questions / suggestions

Hopefully, this article helped you create a button hover effect on a VBA Userform. If you have any questions about this topic or suggestions for improvement, please post a comment below.

Comments  
# David McAllister 2021-04-14 15:42
This looks like a great feature, but I have a few questions, since it does not work in my program as yet. I am quite amateur, but have several user forms with multiple buttons in my program *before trying to add the hover effect here). These forms and all processing work very well.

1. One of the additions specified here is " the following variable must be declared at the very top of the code screen of a form". I'm not clear on where the code screen of a form is located, i.e. exactly where this line should be inserted.

2. I'm also not clear where to place the 2 lines of code designated for the "UserForm_QueryClose event". My code so far has no such event, and had no class modules at all until I added the two called for in this article. Since the 2 lines include use of "Me", it would seem that these lines must belong in a class module. Let me add that in my program before these additions, the user forms closed without problems when any of their buttons were clicked; each button invoked specific further processing.

Thanks to the author or anyone else for help. I appreciate it is a burden to help complete amateurs.
Reply
# Manfred van den Noort 2021-04-16 09:07
Hi David,
You can find the code screen of a form by clicking Alt + F11. This will open the VBA editor. Next, you need to click on the respective form and at the top left of the editor you can click on a button to open the form's code screen.
You can see how to apply the code in the downloadable file.
Reply
# David McAllister 2021-04-16 10:40
Thank you very kindly for your response.
Reply
arrow_up