Hi guys, as a first post here I thought I'd try and contribute something to the community, I have been using this site quite a bit the last few months with my work and figured its time to attempt to give back!
I found a link in this forum to Egad's mouseover of a command button script - http://www.ozgrid.com/forum/showthread.php?t=11991 and figured out a way for the action to 'reset' when the mouse leaves the command button, meaning you can have buttons highlight when the mouse is hovering and revert to normal when it leaves the button. You can also do things like - display a picture, put today's date in a cell, and basically do any excel action and then have it revert. I'm thinking this could be especially useful for making excel games or having help text pop up (not a msgbox so they dont have to click anything) when a user hovers over the "?" or a particular place in an app.
I don't know where I can upload my example to, so I'll walk through it because the layout is semi-complicated.
EDIT (Wigi): SEE BELOW FOR THE ATTACHMENT
To start, I have a blank worksheet, and draw a label (label1) that covers A1:Z100 or whatever space you want to use. In label1 properties, make it transparent and remove the border, so it is invisible. Draw 4 commandbuttons and an optionbutton (for my example) and place them anywhere as long as they are on top of label1, but don't put them near the edge of label1 or it won't register when the mouse leaves the commandbutton. Now, draw 4 more labels (label2-5) and put a picture in there somewhere, named picture1. You can skip this if you want, just modify the code accordingly.
So, you've got the invisible background label1 (don't forget to send it to the very bottom (Order -> send to back)) and your commandbuttons on top of it. Now, the code: (goes in the worksheet you put the buttons and such in)
Option Explicit
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' do macro commands here:
CommandButton1.BackColor = RGB(200, 200, 200) ' grey
ActiveSheet.Shapes("Label2").Visible = True ' display label2
If ActiveSheet.Pictures("Picture1").Visible = False Then ' test if picture is showing
ActiveSheet.Pictures("Picture1").Visible = True ' if picture was not visible, make it visible
End If
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = True ' make background label visible (to detect mouseoff)
End Sub
Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' do macro commands here:
CommandButton2.BackColor = RGB(200, 200, 200) ' grey
ActiveSheet.Shapes("Label3").Visible = True ' display label3
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = True ' make background label visible (to detect mouseoff)
End Sub
Private Sub CommandButton3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' do macro commands here:
CommandButton3.BackColor = RGB(200, 200, 200) ' grey
ActiveSheet.Shapes("Label4").Visible = True ' display label4
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = True ' make background label visible (to detect mouseoff)
End Sub
Private Sub CommandButton4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' do macro commands here:
CommandButton4.BackColor = RGB(200, 200, 200) ' grey
ActiveSheet.Shapes("Label5").Visible = True ' display label5
Cells(14, 8).FormulaR1C1 = "=TODAY()" ' modify cells
Cells(13, 8).Value = "Today's Date:"
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = True ' make background label visible (to detect mouseoff)
End Sub
Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If CommandButton1.BackColor <> RGB(50, 50, 255) Then ' test if cb1 is blue or grey
CommandButton1.BackColor = RGB(50, 50, 255) ' if grey, change to blue
End If
If CommandButton2.BackColor <> RGB(50, 50, 255) Then
CommandButton2.BackColor = RGB(50, 50, 255) ' blue
End If
If CommandButton3.BackColor <> RGB(50, 50, 255) Then
CommandButton3.BackColor = RGB(50, 50, 255) ' blue
End If
If CommandButton4.BackColor <> RGB(50, 50, 255) Then
CommandButton4.BackColor = RGB(50, 50, 255) ' blue
End If
If ActiveSheet.Shapes("Label2").Visible = True Then
ActiveSheet.Shapes("Label2").Visible = False ' turn off label
End If
If ActiveSheet.Shapes("Label3").Visible = True Then
ActiveSheet.Shapes("Label3").Visible = False ' turn off label
End If
If ActiveSheet.Shapes("Label4").Visible = True Then
ActiveSheet.Shapes("Label4").Visible = False ' turn off label
End If
If ActiveSheet.Shapes("Label5").Visible = True Then
ActiveSheet.Shapes("Label5").Visible = False ' turn off label
End If
If Cells(13, 8).Value > 1 Then ' test if cells are occupied
Cells(14, 8).ClearContents ' if they are, clear them
Cells(13, 8).ClearContents
End If
If ActiveSheet.Pictures("Picture1").Visible = True Then ' test if picture is visible
ActiveSheet.Pictures("Picture1").Visible = False ' if it is, make it invisible
End If
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = False
' once mouseover has registered (meaning that the mouse left a commandbutton, perform all the code above
' and finish with this line so label1 is not clickable. Label1 should be reactivated on any mouseover
' of any button (see CommandButtonX_MouseMove subs and OptionButton1_MouseMove sub)
End Sub
Private Sub OptionButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If ActiveSheet.Pictures("Picture1").Visible = False Then ' test if picture is showing
ActiveSheet.Pictures("Picture1").Visible = True ' if it isnt, make it visible
End If
' keep this last line no matter what you change above
ActiveSheet.Shapes("Label1").Visible = True ' make background label visible (to detect mouseoff)
End SubNot that complicated once you take it apart. There are only really a few important parts: basically the last line of each sub.
Important things to keep in mind:
- for each button or object, you must have the last line that turns on label1, or you cannot 'revert' once the mouse leaves it.
- In the label1 mousemove sub you must have the last line that makes label1 invisible, or you might run into problems if you click an 'empty' spot on the worksheet or want to select a cell.
- Test to see if the action you're going to do is already done (all of the 'If then' statements above). If you skip this, especially with a picture, excel will lag because every pixel of mousemovement means it runs the sub again and without an 'if' statement it will be turning the picture to visible hundreds of times a second, or trying to.
- If you're going to put ALOT of code into one of the commandbutton mousemove subs, you should probably turn label1 on first instead of last incase the code hasnt finished by the time the user moves the mouse off the button (anyone know if this matters? will the whole mousemove sub finish running once its started? I'm thinking yes, in which case you won't need to make that line the first one)
Ill upload my example if people have trouble, not sure where I can upload too tho.
Have fun and show me some cool uses for this!