VBA – Mouse hovering over 99 command buttons — how to loop this

command linemicrosoft excelmousevba

I wrote the simple code below, but it is soooo inefficient! How can I make the following code more efficient? Objective of code: There are 99 command buttons. The objective is — if mouse-hovers over each command button, then the command button changes color to red; otherwise, command button stays color yellow per initial state. No clicking, just hovering. In other words, how can I put the 99 "MouseMove" subs into a loop? Thank you in advance. Btw: Names of command buttons are: CommandButton101, CommandButton102, CommandButton103, etc… CommandButton199.

Sub CommandButton101_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 101
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton102_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 102
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton103_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 103
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton104_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 104
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton105_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 105
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton106_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 106
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton107_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 107
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton108_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 108
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton109_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 109
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton110_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 110
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton111_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 111
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton112_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 112
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton113_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 113
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub


Sub CommandButton114_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     Dim i As Integer:    i = 114
           With Controls("CommandButton" & i)
            '--------------------------------------------------------------------------------
             If .Caption = vbNullString Then
                .Font.Bold = True
                .Enabled = False
                .BackColor = VBA.RGB(200, 200, 200)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
             If Controls("CommandButton" & i).BackColor = RGB(255, 51, 153) = True Then     '''<<< Red backcolor.
                Controls("CommandButton" & i).BackColor = RGB(240, 230, 140)                '''<<< Normal yellow backcolor.
                DoEvents:      Sleep 338 * hoverSpeed
             Else
                Controls("CommandButton" & i).BackColor = RGB(255, 51, 153)
                DoEvents:      Sleep 338 * hoverSpeed
                Exit Sub
             End If
            '--------------------------------------------------------------------------------
          End With
End Sub

(Etc… for 99 times for 99 command buttons.)

Using comments from Chip & Spikey below, here I wrote function and provide several call examples, not tested yet… Chip/Spikey — the code looks ok…??

     All codes in Userform1:  Is my code function correct per our discussion?  Thank you, guys!  Latest code below, still getting an error type mismatch... please help?




Private Function hoverButton(eachButton As MSForms.CommandButton)
     With eachButton
         If .Caption = vbNullString Then
             .Font.Bold = True
             .Enabled = False
             .BackColor = VBA.RGB(200, 200, 200)                          '''<<< Gray backcolor, unused .
             DoEvents:    Sleep 338 * hoverSpeed
             Exit Function
         End If
        '--------------------------------------------------------------------------------
         If (eachButton.BackColor = RGB(255, 51, 153)) = True Then        '''<<< Red backcolor.
             eachButton.BackColor = RGB(240, 230, 140)                    '''<<< Yellow backcolor.
             DoEvents:    Sleep 338 * hoverSpeed
             Exit Function
         Else
             eachButton.BackColor = RGB(255, 51, 153)
             DoEvents:    Sleep 338 * hoverSpeed
             Exit Function
         End If
        '--------------------------------------------------------------------------------
     End With
End Function



Sub CommandButton101_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton101)
End Sub
Sub CommandButton102_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton102)
End Sub
Sub CommandButton103_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton103)
End Sub
Sub CommandButton104_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton104)
End Sub
Sub CommandButton105_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton105)
End Sub
Sub CommandButton106_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton106)
End Sub
Sub CommandButton107_MouseMove(ByVal eachButton As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
     hoverButton (Me.CommandButton107)
End Sub

.
.
.

Best Answer

hoverButton (Me.CommandButton101)

Note that weird whitespace: that's the VBE giving you a hint about what's going on.

The expression (Me.CommandButton101) is being evaluated as a value, and the result of this evaluation is going to be passed to the hoverButton procedure.

Me.CommandButton101 is an object - a MSForms.CommandButton object, and MSForms.CommandButton defines a hidden Value As Boolean property as the class' default member.

This is important, because it's how VBA is able to evaluate the expression you're giving it. In other words, what you're doing is this:

hoverButton Me.CommandButton101.Value

So you're passing a Boolean value to hoverButton.

Private Function hoverButton(eachButton As MSForms.CommandButton)

But hoverButton wants a MSForms.CommandButton - hence the type mismatch. Note that eachButton should probably be named currentButton (or just button), that this parameter should probably be passed ByVal, and that a Function normally returns something - if you're not returning anything, consider making it a Sub procedure.

Remove the parentheses, and you'll be passing the button object reference itself rather than let-coercing it through an implicit default member call:

hoverButton Me.CommandButton1

Or, use the obsolete explicit Call statement:

Call hoverButton(Me.CommandButton1)

Notice the whitespace is gone: that's the VBE signaling that the parentheses are delimiting the argument list. When there's a space between a procedure/function and the opening parenthesis, that's the VBE signaling that the parentheses are enclosing the first argument into a value expression - if the object involved has no default member, doing that would throw error 438 "member not found".

Best Answer

Related Solutions

Excel – How to trigger an Excel macro by hovering a mouse over a cell

Related Question