☰

VBA - 鼠标悬停在 99 个命令按钮上 - 如何循环？

tag-icon • tag-icon microsoft-excel command-line vba mouse
VBA - 鼠标悬停在 99 个命令按钮上 - 如何循环？

我写了下面的简单代码，但它效率太低了！我怎样才能使下面的代码更高效？代码目标：有 99 个命令按钮。目标是——如果鼠标悬停在每个命令按钮上，则命令按钮颜色变为红色；否则，命令按钮保持初始状态下的黄色。无需点击，只需悬停。换句话说，我怎样才能将 99 个“MouseMove”子程序放入循环中？提前谢谢您。顺便说一句：命令按钮的名称是：CommandButton101、CommandButton102、CommandButton103 等...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

（等等...99 次对应 99 个命令按钮。）

使用下面 Chip 和 Spikey 的评论，在这里我编写了函数并提供了几个调用示例，尚未测试...Chip/Spikey——代码看起来不错……??

     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

。 。 。

在此处输入图片描述

答案1

hoverButton (Me.CommandButton101)

请注意那个奇怪的空格：这是 VBE 给你提示正在发生的事情。

该表达式(Me.CommandButton101)被评估为一个值，并且该评估的结果将被传递给过程hoverButton。

Me.CommandButton101是一个对象 - 一个MSForms.CommandButton对象，并将MSForms.CommandButton隐藏Value As Boolean属性定义为类'默认成员。

这很重要，因为这是 VBA 能够评估您给出的表达式的方式。换句话说，您要做的就是：

hoverButton Me.CommandButton101.Value

因此，您正在将一个Boolean值传递给hoverButton。

Private Function hoverButton(eachButton As MSForms.CommandButton)

但hoverButton想要一个MSForms.CommandButton- 因此类型不匹配。请注意，eachButton可能应该命名currentButton（或只是button），该参数可能应该传递ByVal，并且Function通常返回一些东西- 如果您不返回任何东西，请考虑将其作为一个Sub程序。

删除括号，您将传递按钮对象引用本身，而不是通过隐式默认成员调用强制执行它：

hoverButton Me.CommandButton1

或者，使用过时的明确Call声明：

Call hoverButton(Me.CommandButton1)

请注意，空格消失了：这是 VBE 信号，表示括号正在分隔参数列表。当过程/函数和左括号之间有空格时，这是 VBE 信号，表示括号将第一个参数括在值表达式中- 如果所涉及的对象没有默认成员，那么这样做会引发错误 438“未找到成员”。

相关内容