Change the colour of a shape on a worksheet on mouseover

[450nick]

Hi Chaps,

I would like to make some smart looking buttons in a frozen pane to control a filter range, and as part of that I'd like them to change colour on mouseover and when active. I know how to do this in a userform but how would I do this with a worksheet object?

 

[ImranMalek]

Hi,
You can use MouseMove event of command button for that

Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Sheets("Sheet1").Range("A1").Value = 10 Then
   CommandButton1.BackColor = RGB(255, 0, 0)
   Else
   CommandButton1.BackColor = RGB(0, 255, 0)
End If
End Sub

Hope it helps.

 

[450nick]

Hi, thanks for the code, I tried taking out the if statement to see if I can just get the box to change colour on mouseover and it doesn't seem to do anything - how to I make the subroutine activate?

 

[ImranMalek]

Hi,
Make sure that your button name is CommandButton1 for the code to work if not just go to VBA and select your button from top left you'll be able to see the events on top right corner you will have to select mouse move event from there and put the code from If to Endif in that event.
Hope it will work out.

 

[450nick]

I tried substituting the CB1 for my button name - I think the issue is that it isn't showing up in the dropdown in the VBA editor - any idea why?? I'm using a shape - not a proper button. Although there is also a button on the sheet and that doesn't show up either!

 

[RoryA]

Shapes and Form controls don't have events - only ActiveX objects do.

 

[450nick]

Ah that'll do it...!