Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    L.A.
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    code to highlight Excel row upon hover

    Is VisualBasic able to code for highlighting a row when mouse cursor is hovering over that row?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Calvin,

    I can't find an event to accomplish that in either the Workbook or Sheet models. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    calvin2a (2015-04-25)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,682
    Thanks
    121
    Thanked 665 Times in 606 Posts
    RG,

    I am sure you can do something with the code here to make it work

    http://windowssecrets.com/forums/sho...-for-textboxes

    Maud

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Maud,

    Nice piece of code but unfortunately the Mouse events are Methods of the Textbox class short of putting a textbox at the top of each column I don't see how to use it to accomplish the OP's task. Am I missing something?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,682
    Thanks
    121
    Thanked 665 Times in 606 Posts
    "With a little help from my friends" Beatles, Sgt. Pepper's Lonely Hearts Club Band, June, 1967
    As RG pointed out, a mouseover for a textbox has no practical application in Calvin's needs. I initially was working with a hyperlink formula to point to the macro but that would mean that every cell in the range would need to have the formula inserted making that an impractical approach as well. So I turned to x-y coordinates and am almost there.

    I need some input from you (all members) to get around an obstacle I am running into to highlight the rows when hovered over. The following code will highlight the rows moving the mouse over the cells for about 5 seconds but then stops highlighting even though the code is still running in the background. To end the code, just move the cursor outside the range of data.

    If someone can come up with a workaround to get pass the 5 second time limit, they will get a well deserved press on their "Thanks" button from me (best I can offer).

    Maud

    testrollover.png

    This code was adapted from Any Pope's code to find the xy coordinates over at Ozgrid

    Code:
    Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Type POINTAPI
        x As Long
        y As Long
    End Type
     
    Sub CurosrXY_Pixels()
        Dim lngStatus As Long
        Dim typWhere As POINTAPI
        Dim rng As Range
        Set rng = Range("A1:E19")
        Range("H3") = 0
        Range("I3") = 0
        Do While Range("H3") <= 357 And Range("I3") <= 566
            lngStatus = GetCursorPos(typWhere)
            Range("H3") = typWhere.x
            Range("I3") = typWhere.y
            Row = Int(Range("I3") / 20) - 8
            Range("G3") = Row
            rng.Interior.Color = vbWhite
            Range("A" & Row & ":E" & Row).Interior.Color = vbRed
        Loop
    End Sub
    Attached Files Attached Files

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Maud,

    I can't seem to get the code to do anything except on occasion flip between rows 3 & 4. It seems to me you'd need a loop to almost continuously monitor the cursor position.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,682
    Thanks
    121
    Thanked 665 Times in 606 Posts
    RG,

    There is a loop in the code. Strange that I can go up and down the entire range and the highlighting of the rows will follow for about 5 seconds. Are you keeping your cursor with in the data range? I am wondering if resolution comes into play here. Try staying closer to column A when sliding the cursor up and down or expanding the pixel ranges in Do While Range("H3") <= 357 And Range("I3") <= 566. Or are you timing out less than 5 seconds?

    Here is a video of what it looks like, however, the cursor does not get recorded. After about 5 seconds, it stops highlighting and I move the cursor out of range to end the code. The Start button returns to normal state

    Last edited by Maudibe; 2015-04-26 at 12:08.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Maud,

    The start button reverts to normal immediately on my Desktop Haswell i-7! However, I copied it over to my Laptop an older Allendale i-5 and it works as shown in your video across the whole range of table cells FOR ABOUT 2 MINUTES!.

    I did a second test after widening all the table cells and it quit as soon as I moved out of the predefined size but still within the table.

    Third test where I just zoomed the screen with the same results as the 2nd test.

    Doesn't look like it will be a reliable method since it is obviously dependent on processor speed, screen resolution, and cell width. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,682
    Thanks
    121
    Thanked 665 Times in 606 Posts
    RG,

    Thanks for the follow-up. I too have a core I7 but first generation oc to 4.2GHz. Maybe not as fast as yours. Using your "beast rig", could you try doubling the x-y-pixels in the line Do While Range("H3") <= 357 And Range("I3") <= 566 while keeping the cell width at 8.43(64 pixels) and the cell height at 15.0(20 pixels). My resolution is at 1960 x 1080. What is yours? Any thoughts on the timeout issue? contemplating a pause function.

    Update: adding the pause seemed to cure the time out. but still need to work on resolution issue. RG- I await your test results when you get around to it

    Code:
    Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Type POINTAPI
        x As Long
        y As Long
    End Type
     
    Sub CurosrXY_Pixels()
        Dim lngStatus As Long
        Dim typWhere As POINTAPI
        Dim rng As Range
        Set rng = Range("A1:E19")
        Range("H3") = 0
        Range("I3") = 0
        Do While Range("H3") <= 357 And Range("I3") <= 566
            lngStatus = GetCursorPos(typWhere)
            Range("H3") = typWhere.x
            Range("I3") = typWhere.y
            Row = Int(Range("I3") / 20) - 8
            Range("G3") = Row
            rng.Interior.Color = vbWhite
            Range("A" & Row & ":E" & Row).Interior.Color = vbRed
            Pause
        Loop
    End Sub
    
    Private Sub Pause()
    '----------------------------------------
    'CHANGE PAUSETIME TO ADJUST SPEED
    Dim PauseTime, Start
        PauseTime = 0.05
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
    End Sub
    Last edited by Maudibe; 2015-04-26 at 13:09.

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Maud,

    Set up as you instructed...No change!

    My screen resolution is 1920X1080. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,936
    Thanks
    139
    Thanked 510 Times in 486 Posts
    Hi RG/Maud

    ..here's my version.
    Turn the row highlighter ON/OFF with a right-mouse-click

    zeddy
    Attached Files Attached Files

  13. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Maudibe (2015-04-26),RetiredGeek (2015-04-26)

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,482
    Thanks
    376
    Thanked 1,471 Times in 1,339 Posts
    Zeddy,

    Nice job!

    Also a nice explanation despite the MS "confuse the user" speak!

    Would have been even nicer if you'd mentioned the Conditional Formatting! I spent a lot of time trying to figure out where the color was set in the code. Of course I could have missed it in the MS speak.

    Almost forgot it's easy to modify to do Columns vs Rows.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,936
    Thanks
    139
    Thanked 510 Times in 486 Posts
    Hi RG

    Ooops! I originally described the conditional formatting (range ([A1:R39] )in a text box on the sheet, but I deleted it!
    Top marks for tracking that down so quickly!

    zeddy

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,936
    Thanks
    139
    Thanked 510 Times in 486 Posts
    Hi RG

    ..I re-checked my posted file.
    Did you see that note in cell [C9]

    zeddy

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,682
    Thanks
    121
    Thanked 665 Times in 606 Posts
    Zeddy,

    OUTSTANDING!!!!!

Page 1 of 2 12 LastLast

Posting Permissions

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