Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Mouse Over Objects. Detect When Mouse Leaves

  1. #1
    Join Date
    9th January 2007
    Location
    Idaho
    Posts
    115

    Mouse Over Objects. Detect When Mouse Leaves

    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)


    VB:
     
     
    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 Sub 
    
    
    Not 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!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Wigi; January 10th, 2007 at 04:21.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,709

    Re: Mouseover Objects And Detect When Mouse Leaves Them

    Hi Andrew

    Thanks for that and 1st post too!

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,049

    Re: Mouse Over Objects. Detect When Mouse Leaves

    Hi thanks for sharing. There is a file size restriction, but you might be able to zip the file & attach it here.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,039

    Re: Mouse Over Objects. Detect When Mouse Leaves

    Hello Andrew

    Thank you for your efforts. Much appreciated.

    Welcome to Ozgrid, nice first post! Have fun.

    Wigi
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,971

    Re: Mouse Over Objects. Detect When Mouse Leaves

    Andrew,

    Good! Nice submission.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on


    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

  6. #6
    Join Date
    9th January 2007
    Location
    Idaho
    Posts
    115

    Re: Mouse Over Objects. Detect When Mouse Leaves

    Thanks guys.

    I'm attempting to attach the file for anyone that needs it.. and yeah its going to have to be zipped to be under 45kb tho. Maybe a mod could edit my original post to include the link?
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,039

    Re: Mouse Over Objects. Detect When Mouse Leaves

    Quote Originally Posted by AndrewJ
    Maybe a mod could edit my original post to include the link?
    Done. I put the file there as well.

    Keep up the good work Andrew.

    Wigi
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  8. #8
    Join Date
    9th January 2007
    Location
    Idaho
    Posts
    115

    Re: Mouse Over Objects. Detect When Mouse Leaves

    ty sir!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Mouse-overs
    By Egad in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 2
    Last Post: July 20th, 2006, 12:17
  2. Detect Mouse Click in Cell
    By PaulBi in forum EXCEL HELP
    Replies: 1
    Last Post: May 24th, 2006, 19:28
  3. Mouse simulation
    By epapare in forum EXCEL HELP
    Replies: 1
    Last Post: September 6th, 2005, 10:44

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno