VBA Label Font goes Bold and Underline when moving mouse over

M

markgrnh

New Member
Joined
Apr 7, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Good morning everyone,

Before I post the actual question, I think its worthwhile letting you all know that I am only just learning about VBA coding, so my knowledge is very basic. Most of what I have learnt is from recording macros and then converting it to VBA coding and then tidying it up. Plus coding that I have gathered from google searches.

Yet the one thing, I am trying to do is beyond my current knowledge base, and I have been struggling to find a simple answer from searches, that I actually understand.

In short, I have a UserForm called Dashboard and three Labels which I use as buttons. They all do what I need them to do, but what I need help with is making the text inside the Label go Bold and Underlined when I move the mouse over the label.

I have managed to get it work, in a fashion, but its not perfect, as the code seems a bit longwinded and its having to overwork which is causing a delay.

This is what the form and buttons look like:

1680864001713.png


When I move the mouse over, it does what i want it to do, but as you can see if I move too quick I get multiple labels going bold and underlined,

This is the code I am using at the moment:

Private Sub Label4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With Label4
.FontBold = True
.FontUnderline = True
End With

End Sub


And the code on the actual Dashboard userform as follows:
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With Label4
.FontBold = False
.FontUnderline = False
End With

With Label1
.FontBold = False
.FontUnderline = False
End With

With Label5
.FontBold = False
.FontUnderline = False
End With
etc etc etc

As I have multiple labels, I dont think this code is exactly efficient because everytime I move it has to check each and every label, hense the delay (well that is what I am thinking anyway)

So what i am wondering, is it possible to simplify this code so it not having to look at each label individually. I was thinking maybe if I can use the Tag option in the Properties and put the ones I treat as buttons to "Buttons" in the tag and then have a piece of code that simply says, if the item is a label and it has a tag called button, the font goes bold and underlined when the mouse moves over it but then it's just normal text when the mouse is elsewhere.

Is that possible, and if so, how would I go about coding that, as I mentioned above, I am at the edge of my knowledge already and this is requires knowledge I dont currently know.

Any help would be appreciated.

Many thanks

Mark
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
The following code uses a Class module in order to handle multiple Labels. And it avoids looping in order to check each and every Label. Note that I have assumed that your UserForm is called UserForm1. Change the name of the UserForm accordingly.

As it stands, all Lables on your UserForm will be included for this behaviour. To include only specific Labels, use the following UserForm_Initialize event instead...

VBA Code: 
Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
   
    Dim labelArray As Variant
    labelArray = Array("Label1", "Label2", "Label3") ' change and/or add additional Labels
   
    Dim lbl As clsLabel
    Dim i As Long
    For i = LBound(labelArray) To UBound(labelArray)
            Set lbl = New clsLabel
            Set lbl.Label = Me.Controls(labelArray(i))
            m_labelCollection.Add lbl
    Next i
End Sub


Now, here's the full code. First, copy and paste the following code into the code module for your UserForm (Visual Basic Editor >> Insert >> UserForm)...

VBA Code: 
Option Explicit

Dim m_currentLabel As msforms.Label
Dim m_labelCollection As Collection

Public Property Set CurrentLabel(ByVal obj As msforms.Label)
    Set m_currentLabel = obj
End Property

Public Property Get CurrentLabel() As msforms.Label
    Set CurrentLabel = m_currentLabel
End Property

Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
   
    Dim ctrl As Control
    Dim lbl As clsLabel
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Then
            Set lbl = New clsLabel
            Set lbl.Label = ctrl
            m_labelCollection.Add lbl
        End If
    Next ctrl
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not m_currentLabel Is Nothing Then
        With m_currentLabel.Font
            .Bold = False
            .Underline = False
        End With
        Set m_currentLabel = Nothing
    End If
End Sub


Secondly, insert a Class module (Visual Basic Editor >> Insert >> Class Module), and under the Properties Window (Visual Basic Editor >> View >> Properties Window) in the Project Explorer Window (Visual Basic Editor >> View >> Project Explorer Window), re-name the Class module clsLabel. Then copy and paste the following code into the code module for the Class...

VBA Code: 
Option Explicit

Dim WithEvents m_label As msforms.Label

Public Property Set Label(ByVal obj As msforms.Label)
    Set m_label = obj
End Property

Private Sub m_label_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If m_label Is UserForm1.CurrentLabel Then Exit Sub
    If Not UserForm1.CurrentLabel Is Nothing Then
        With UserForm1.CurrentLabel.Font
            .Bold = False
            .Underline = False
        End With
    End If
    With m_label.Font
        .Bold = True
        .Underline = True
    End With
    Set UserForm1.CurrentLabel = m_label
End Sub

Hope this helps!
 
Upvote 0
Solution
Domenic said:
The following code uses a Class module in order to handle multiple Labels. And it avoids looping in order to check each and every Label. Note that I have assumed that your UserForm is called UserForm1. Change the name of the UserForm accordingly.

As it stands, all Lables on your UserForm will be included for this behaviour. To include only specific Labels, use the following UserForm_Initialize event instead...

VBA Code: 
Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
  
    Dim labelArray As Variant
    labelArray = Array("Label1", "Label2", "Label3") ' change and/or add additional Labels
  
    Dim lbl As clsLabel
    Dim i As Long
    For i = LBound(labelArray) To UBound(labelArray)
            Set lbl = New clsLabel
            Set lbl.Label = Me.Controls(labelArray(i))
            m_labelCollection.Add lbl
    Next i
End Sub


Now, here's the full code. First, copy and paste the following code into the code module for your UserForm (Visual Basic Editor >> Insert >> UserForm)...

VBA Code: 
Option Explicit

Dim m_currentLabel As msforms.Label
Dim m_labelCollection As Collection

Public Property Set CurrentLabel(ByVal obj As msforms.Label)
    Set m_currentLabel = obj
End Property

Public Property Get CurrentLabel() As msforms.Label
    Set CurrentLabel = m_currentLabel
End Property

Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
  
    Dim ctrl As Control
    Dim lbl As clsLabel
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Then
            Set lbl = New clsLabel
            Set lbl.Label = ctrl
            m_labelCollection.Add lbl
        End If
    Next ctrl
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not m_currentLabel Is Nothing Then
        With m_currentLabel.Font
            .Bold = False
            .Underline = False
        End With
        Set m_currentLabel = Nothing
    End If
End Sub


Secondly, insert a Class module (Visual Basic Editor >> Insert >> Class Module), and under the Properties Window (Visual Basic Editor >> View >> Properties Window) in the Project Explorer Window (Visual Basic Editor >> View >> Project Explorer Window), re-name the Class module clsLabel. Then copy and paste the following code into the code module for the Class...

VBA Code: 
Option Explicit

Dim WithEvents m_label As msforms.Label

Public Property Set Label(ByVal obj As msforms.Label)
    Set m_label = obj
End Property

Private Sub m_label_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If m_label Is UserForm1.CurrentLabel Then Exit Sub
    If Not UserForm1.CurrentLabel Is Nothing Then
        With UserForm1.CurrentLabel.Font
            .Bold = False
            .Underline = False
        End With
    End If
    With m_label.Font
        .Bold = True
        .Underline = True
    End With
    Set UserForm1.CurrentLabel = m_label
End Sub

Hope this helps!
Click to expand...
Thank you for your reply, I followed your steps, but I got the following error, as soon as I moved over a label, any ideas?

1680889640893.png
 
Upvote 0
Domenic said:
The following code uses a Class module in order to handle multiple Labels. And it avoids looping in order to check each and every Label. Note that I have assumed that your UserForm is called UserForm1. Change the name of the UserForm accordingly.

As it stands, all Lables on your UserForm will be included for this behaviour. To include only specific Labels, use the following UserForm_Initialize event instead...

VBA Code: 
Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
  
    Dim labelArray As Variant
    labelArray = Array("Label1", "Label2", "Label3") ' change and/or add additional Labels
  
    Dim lbl As clsLabel
    Dim i As Long
    For i = LBound(labelArray) To UBound(labelArray)
            Set lbl = New clsLabel
            Set lbl.Label = Me.Controls(labelArray(i))
            m_labelCollection.Add lbl
    Next i
End Sub


Now, here's the full code. First, copy and paste the following code into the code module for your UserForm (Visual Basic Editor >> Insert >> UserForm)...

VBA Code: 
Option Explicit

Dim m_currentLabel As msforms.Label
Dim m_labelCollection As Collection

Public Property Set CurrentLabel(ByVal obj As msforms.Label)
    Set m_currentLabel = obj
End Property

Public Property Get CurrentLabel() As msforms.Label
    Set CurrentLabel = m_currentLabel
End Property

Private Sub UserForm_Initialize()
    Set m_labelCollection = New Collection
  
    Dim ctrl As Control
    Dim lbl As clsLabel
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "Label" Then
            Set lbl = New clsLabel
            Set lbl.Label = ctrl
            m_labelCollection.Add lbl
        End If
    Next ctrl
End Sub

Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not m_currentLabel Is Nothing Then
        With m_currentLabel.Font
            .Bold = False
            .Underline = False
        End With
        Set m_currentLabel = Nothing
    End If
End Sub


Secondly, insert a Class module (Visual Basic Editor >> Insert >> Class Module), and under the Properties Window (Visual Basic Editor >> View >> Properties Window) in the Project Explorer Window (Visual Basic Editor >> View >> Project Explorer Window), re-name the Class module clsLabel. Then copy and paste the following code into the code module for the Class...

VBA Code: 
Option Explicit

Dim WithEvents m_label As msforms.Label

Public Property Set Label(ByVal obj As msforms.Label)
    Set m_label = obj
End Property

Private Sub m_label_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If m_label Is UserForm1.CurrentLabel Then Exit Sub
    If Not UserForm1.CurrentLabel Is Nothing Then
        With UserForm1.CurrentLabel.Font
            .Bold = False
            .Underline = False
        End With
    End If
    With m_label.Font
        .Bold = True
        .Underline = True
    End With
    Set UserForm1.CurrentLabel = m_label
End Sub

Hope this helps!
Click to expand...
Sorry ignore that reply, my Userform is called Dashboard as I have another Userform1, doh..... it now works when I renamed it :) So thank you so much, you are a super star and I never would have worked that out myself, appreciate it.
 
Upvote 0
That's great, I'm glad to hear that you've got it working. (y)

And thanks for the feedback!

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
453
AndyEd
A
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
578
RoryA
RoryA
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
C
  • Question
A Bookmark to return to the same row when exiting and opening the application
Replies
8
Views
187
Edgar_
Edgar_
A
  • Solved
Add buttons automatically based on table values. Each button press adds button label to cell L2 on dashboard
2
Replies
13
Views
665
Domenic
D

Forum statistics

Threads
1,215,523
Messages
6,125,317
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top