Help with code with menu.

[Sebastiandiaz]

Hello, I am making a small project. I am making a simple menu, however, I would like this menu to change color when the mouse is placed over it. I think there is a MouseMove command that can be used to make this possible (ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single).
Is there a way to do this?
Thank you so much in advance.

 

[p45cal]

try this in the userform code-module:

Private Sub Cities_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
For Each btn In Me.Controls
  If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
Next btn
End Sub
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
For Each btn In Me.Controls
  If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
Next btn
End Sub
Private Sub City1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City1.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City1 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City2.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City2 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City3.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City3 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City4.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City4 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City5.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City5 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City6_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City6.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City6 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City7_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City7.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City7 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City8_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City8.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City8 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub
Private Sub City9_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
City9.BackColor = 12648447
For Each btn In Me.Controls
  If Not btn Is City9 Then
    If TypeName(btn) = "CommandButton" Then If btn.BackColor <> -2147483633 Then btn.BackColor = -2147483633
  End If
Next btn
End Sub

 

[Sebastiandiaz]

For God's sake! It worked! Thank you so much, you are simply the best.

 

[Sebastiandiaz]

I was wondering if this can be done if the CommandButton is placed on the sheet and not in a Form. Can it be possible?

 

[p45cal]

Yes you can but it's flaky and slower.
The user form had a frame containing the buttons and all this was on a userform. Both the frame and the userform have their own mouseover event which can be used to return all buttons colour to normal when the mouse isn't on any of them. This is not the case with a sheet, there is no mouseover event for a sheet and programming an activex frame embedded on a sheet is a nightmare. So instead, to bring all buttons back to normal I've put a routine in the sheet's selection_change event, which means to lose colours from all the buttons you have to select a cell.
See attached.

 

[Sebastiandiaz]

It was so helpful. Thank you so much!