Create button hover effect in VBA userform

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 change when the mouse is moved over them:

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-2023 worksheetsvba.com, alle rechten voorbehouden
' Versie    : 1.1
' Datum     : 2023-10-06
' Doel      : Create Button Hover effect
'------------------------------------------------------------------------------------------------------------------------------

Private WithEvents frmHover As MSForms.UserForm
Private oColButtons As Collection, sHoveredButton As String

Private Sub Class_Initialize()
    Set oColButtons = New Collection
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
            oColButtons.Add oHoverButton, ctl.Name
            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()
    Dim i As Integer
    For i = 1 To oColButtons.Count
        oColButtons.Remove 1
    Next
    Set oColButtons = 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-2023 worksheetsvba.com, alle rechten voorbehouden
' Versie    : 1.1
' Datum     : 2023-10-06
' 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

Create button hover effect 1.1

Download

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.