Beliebt

VBA - Maus schwebt ├╝ber 99 Befehlsschaltfl├Ąchen - wie wird diese Schleife ausgef├╝hrt? - Microsoft-Excel, Kommandozeile, VBA, Maus

Ich habe den einfachen Code unten geschrieben, aber es ist sooooineffizient! Wie kann ich den folgenden Code effizienter gestalten? Ziel des Codes: Es gibt 99 Befehlsschaltfl├Ąchen. Das Ziel ist: Wenn Sie mit der Maus ├╝ber die einzelnen Befehlsschaltfl├Ąchen fahren, ├Ąndert die Befehlsschaltfl├Ąche ihre Farbe in Rot. Andernfalls bleibt die Befehlsschaltfl├Ąche in ihrem urspr├╝nglichen Zustand gelb. Kein Klicken, nur Schweben. Mit anderen Worten, wie kann ich die 99 "MouseMove" -Untersuchungen in eine Schleife einf├╝gen? Danke im Voraus. ├ťbrigens: Die Namen der Befehlsschaltfl├Ąchen lauten: CommandButton101, CommandButton102, CommandButton103 usw. 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 ... f├╝r 99 mal f├╝r 99 Befehlstasten.)

Unter Verwendung der folgenden Kommentare von Chip & Spikey habe ich die Funktion geschrieben und einige noch nicht getestete Aufrufbeispiele bereitgestellt ... Chip / Spikey - der Code sieht in Ordnung aus ... ??

     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

. . .

Bildbeschreibung hier eingeben

Antworten:

3 f├╝r Antwort Ôäľ 1
hoverButton (Me.CommandButton101)

Beachten Sie das seltsame Leerzeichen: Das ist die VBE, die Ihnen einen Hinweis darauf gibt, was los ist.

Der Ausdruck (Me.CommandButton101) wird als Wert ausgewertet und das Ergebnis dieser Auswertung wird an die weitergeleitet hoverButton Verfahren.

Me.CommandButton101 ist ein Objekt - a MSForms.CommandButton Objekt und MSForms.CommandButton definiert ein verstecktes Value As Boolean Eigentum als die Klasse " Standardmitglied.

Dies ist wichtig, da VBA auf diese Weise den Ausdruck bewerten kann, den Sie ihm geben. Mit anderen Worten, was Sie tun, ist Folgendes:

hoverButton Me.CommandButton101.Value

Du kommst also an einem vorbei Boolean Wert auf hoverButton.

Private Function hoverButton(eachButton As MSForms.CommandButton)

Aber hoverButton will ein MSForms.CommandButton - daher die Typenkonflikt. Beachten Sie, dass eachButton sollte wohl benannt werden currentButton (oder nur button), dass dieser Parameter wahrscheinlich ├╝bergeben werden sollte ByValund dass a Function normalerweise gibt etwas zur├╝ck - Wenn Sie nichts zur├╝cksenden, machen Sie es zu einem Sub Verfahren.

Entfernen Sie die Klammern, und Sie ├╝bergeben die Schaltfl├Ąchenobjektreferenz selbst, anstatt sie durch einen impliziten Standardmemberaufruf zu erzwingen:

hoverButton Me.CommandButton1

Oder verwenden Sie die explizit ├╝berholt Call Erkl├Ąrung:

Call hoverButton(Me.CommandButton1)

Beachten Sie, dass das Leerzeichen weg ist: Dies ist die VBE, die signalisiert, dass die Klammern die Argumentliste begrenzen. Wenn zwischen einer Prozedur / Funktion und der ├Âffnenden Klammer ein Leerzeichen steht, ist dies die VBE, die signalisiert, dass die Klammern gesetzt sind Einschlie├čen des ersten Arguments in einen Werteausdruck - Wenn das betroffene Objekt kein Standardmitglied hat, w├╝rde dies den Fehler 438 "Mitglied nicht gefunden" ausl├Âsen.