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 change 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-2024 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 = oColButtons.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-2024 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
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.
zip-4Create button hover effect 1.1
 

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  
# Valentin 2023-10-03 06:58
When I click the button in the example I downloaded, an error is displayed: "Error '429' occurred at runtime. The ActiveX component cannot create the object."
This happens when you run:
SubButtonHover()
fmHover.Show
End Sub
Is it necessary to include additional "References" in "Tools/References"?
I'm using Office for Mac, although this shouldn't be a limitation, because I've done all kinds of VBA macros, without problems.
Thanks in advance
Reply
# Manfred van den Noort 2023-10-03 18:50
I don't have Excel for Mac.
As far as I know, ActiveXControls are not supported on the Mac.
However, the demo does not contain any ActiveX components.
To troubleshoot the problem further, you can set a breakpoint on the first line of the UserForm_Initialize event.
You can then go through the code step by step and see on which line the error occurs.
Reply
# Valentin 2023-10-04 07:46
Crashed in:
Set oDicButtons = CreateObject("scripting.dictionary")

I think scripting.dictionary is not supported on Mac. Are they really necessary?
In web page:
https://stackoverflow.com/questions/19869266/vba-excel-dictionary-on-mac
explain a way to make a Dictionary, but I do not know if it is a valid way.
Reply
# Manfred van den Noort 2023-10-04 17:26
When I have some time, I will research this further and try to create a Mac compatible solution
Reply
# Valentin 2023-10-04 17:52
Thanks...!
Reply
# Manfred van den Noort 2023-10-06 09:45
I made a new version that I think is now compatible with the Mac.
The Dictionary has now been replaced by a Collection.
Reply
# Valentin 2023-10-06 09:50
Thank you, I will test it this weeked..!
Reply
# Valentin 2023-10-07 17:20
Hello,
Now program doesn't crash anymore, but nothing happens, it doesn't change color.
Reply
# Manfred van den Noort 2023-10-09 19:14
As I said, I don't have Excel for Mac,
but maybe you can try to disable/remove the following code lines and check if the color then changes:
.MousePointer = fmMousePointerCustom
If Len(Dir(ThisWorkbook.Path & "\HandCursor.ico")) Then
.MouseIcon = LoadPicture(ThisWorkbook.Path & "\HandCursor.ico")
End If
Reply
# Jean-Paul 2023-03-10 11:34
Bonjour,
Désolé je parle français
J'ai trouvé votre code et vos explications très intéressantes. Cela me fais progresser.
J'ai modifié la structure des classes pour les adapter à un control Label.
J'aimerais maintenant ajouter d'autres contrôles pour la mise en surbrillance (TextBox, ComboBox, CheckBox).
A votre avis faut-il passer par plusieurs classe clHoverButton, ou bien modifier la classe actuelle pour qu'elle différencie chaque contrôle.
Je vous remercie d'avance pour le temps que vous me consacrerais.
Reply
# Manfred van den Noort 2023-03-12 08:34
For other readers. This is the translation of above French text with Google Translate:
'Good morning,
sorry I speak French
I found your code and your explanations very interesting. It makes me progress.
I modified the structure of the classes to adapt them to a Label control.
Now I would like to add more controls for highlighting (TextBox, ComboBox, CheckBox).
In your opinion, should we go through several clHoverButton classes, or modify the current class so that it differentiates each control.
Thank you in advance for the time you devote to me.'

My reaction is:
Both all controls with one class or a separate class for each control are possible.
I haven't looked into it further, but at first glance I think I'd go for a one-class approach.
I think the connection from the form is easier then.
Reply
# Jean-Paul 2023-03-13 19:02
Merci, pour votre retour.
Je vais me pencher sur la modification de la classe.
Reply
# Andy 2023-02-06 15:25
Hello and good day,

this is exact what i am searching for because your code is also working on Multipage.
But one question.
Is it also working with rounded buttons style for a more modern version of the UserForm ?

Greets Andy
Reply
# Manfred van den Noort 2023-02-06 17:15
I don't know what style you mean exactly, but the hover affect does not change the button style. Only the BackColor is changed.
Reply
# Andy 2023-02-07 06:42
Good day,

for example.
I have one let me Say i have Buttons as Pictures.
1. Button Grey with rounded corners
2. Button Blue with rounded corners.

When i hover the Grey button it should change to Blue.
It is similar to your code but my buttons are pictures what will change when i hover.

I hope my explanation is now more clear.

Thanks
Reply
# Manfred van den Noort 2023-02-07 17:18
Now I understand what you mean.
The current code does not work with image controls.
Only real command buttons will get a hover effect.
With images, instead of changing the BackColor, you have to change the picture.
I think that is possible, but then the code needs to be modified in several places.
Reply
# Andy 2023-02-08 07:25
Hello and good day,

thanks a lot for your explanation.
Wish you a great day.

Andy
Reply
# Justin Oakley 2022-01-15 17:02
Thanke for publishing this. Whilst I admit that I don't fully understand all of it, yet, I incorporated it into a form that I have created with enables a user to view all of the Office Icons and it worked perfect first time. What is so good about it is that I can easily enhance it to include other control types.
Reply
# 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