Page 1 of 4 123 ... LastLast
Results 1 to 15 of 50
  1. #1
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Highlighting rows in excel on mouse over?

    Well it looks like i can't post on an old thread that i have been reading, and need some help with the workbooks give in it, that, so i'll have to link to it instead

    http://windowssecrets.com/forums/sho...row-upon-hover

    i was looking for something with this function that i could integrate into my workbook, and Zeddys' solutions look promising, however, i'm having some trouble

    the first issue is easily fixed -

    i'm using office 2010 64 bit, so i had to modify the function declarations to include "ptrsafe"


    second issue is not so easy to fix, or at least for me, as i'm learning about this code/method by looking at it, and seeing how it works -

    sheet 1, when i press right click, i get a Compile Error: Type Mismatch error, on this line in the startTimer, with the "AddressOf timerProcedure" section highlighted

    zTimerID = SetTimer(0, 0, 0.01, AddressOf timerProcedure)



    i'll continue to see if i can find a fix, in the mean time the highlight on selection version will do,

    appreciate any help offered


    thanks


    forgot to say, this happens in both versions of the workbooks from that link

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Howdy there Gunslinger

    ..welcome to the Lounge as a new poster.
    Our Sheriffs on this site will have a look at your issue.
    I'm sure someone here will beat me to the draw.

    zeddy

    z-sheriff.png

  3. #3
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    Thanks Zeddy,

    I got it, it was a LongPtr reference i was missing, in the declare functions..... oddly enough i did find reference to a M$ hotfix that was supposed to also solve the 32bit vs 64bit problem, although i was slightly skeptical of it, although i downloaded it i never installed it, and have lost the link to the ms page.

    anyway, i'm just adjusting and tidying up the code and i'll attach the workbook so others can see the solution and what i used it for, right now i have a bigger problem, i seem to have "broken" excel !!.... when you get a script/code error, the vba editor pops up with the incorrect part highlighted, and you get the popup error dialog, well, i don't get the dialog, and can't see it hiding behind anything, but, the systme know's its there, so i can' then click on anything else, just get that annoying "ding" !


    What i have regarding the highlighting works very well, just need to tidy some of the code like i said, only thing i don't like is it triggers the worksheet saved event constantly, which in turn means i ALWAYS get the "do you want to save your work" dialog on closing, and i can't disable that, because i might actually fail to save after some genuine changes and i'd lose them !

  4. #4
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts

    nearly there.....

    ok, i'm 95% of the way to where i want to be, i've got the highlighting working, stopping and restarting when i switch between sheets, and when i switch between workbooks, and i've got it to stop the timer code when the workbook looses focus, say to the vba editor, or my browser, and i have an over-ride function within the worksheet via a check box.

    What i'm stuck on is restarting it when focus returns to the excel workbook, or more specifically, when it becomes foremost.

    I've been trying to modify some code i found elsewhere, to fit with what i want/need, but, 1. i'm using 64 bit office which adds that extra annoyance value, and 2. this code is over my knowledge/abilities, it's possible i'm missing something really obvious, but right now it crashes excel in the unhook stage, i can't tell if it's restarting my timer or not, because excel bombs out so fast it's hard to be sure of anything !

    so i'm hoping one of you guys might be able to see what i'm missing

    Code:
    Option Explicit
    
    Private Const EVENT_SYSTEM_FOREGROUND = &H3&
    Private Const WINEVENT_OUTOFCONTEXT = 0
    
    
    Private Declare PtrSafe Function SetWinEventHook Lib "user32.dll" (ByVal eventMin As LongPtr, ByVal eventMax As LongPtr, _
                                                                       ByVal hmodWinEventProc As LongPtr, ByVal pfnWinEventProc As LongPtr, _
                                                                       ByVal idProcess As LongPtr, ByVal idThread As LongPtr, _
                                                                       ByVal dwFlags As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As LongPtr
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As LongPtr, lpdwProcessId As LongPtr) As LongPtr
    
    
    Private pRunningHandles As Collection
    
    
    Public Function StartEventHook() As LongPtr
        If pRunningHandles Is Nothing Then Set pRunningHandles = New Collection
        StartEventHook = SetWinEventHook(EVENT_SYSTEM_FOREGROUND, EVENT_SYSTEM_FOREGROUND, 0&, AddressOf WinEventFunc, 0, 0, WINEVENT_OUTOFCONTEXT)
        pRunningHandles.Add StartEventHook
    End Function
    '
    
    
    Public Sub StopEventHook(lHook As LongPtr)
        Dim LRet As LongPtr
        If lHook = 0 Then Exit Sub
        LRet = UnhookWinEvent(lHook)
    End Sub
    '
    
    
    Public Sub StopAllEventHooks()
        Dim vHook As Variant, lHook As LongPtr
        For Each vHook In pRunningHandles
            lHook = vHook
            StopEventHook lHook
        Next vHook
    End Sub
    '
    
    
    'This function is a callback passed to the win32 api, an error or break will crash EXCEL.
    Public Function WinEventFunc(ByVal HookHandle As LongPtr, ByVal LEvent As LongPtr, _
                                ByVal hWnd As LongPtr, ByVal idObject As LongPtr, ByVal idChild As LongPtr, _
                                ByVal idEventThread As LongPtr, ByVal dwmsEventTime As LongPtr) As LongPtr
        
        On Error Resume Next
        Dim thePID As LongPtr
        
        If LEvent = EVENT_SYSTEM_FOREGROUND Then
            GetWindowThreadProcessId hWnd, thePID
            If thePID = GetCurrentProcessId Then
                Application.OnTime Now, "startTimer"
                StopAllEventHooks
            End If
        End If
        
        On Error GoTo 0
    
    End Function


    I am calling the StartEventHook function from the code i'm using to kill the highlighting timer, and am calling it as is, passing no value with the call

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Gunslinger

    ..I have to dash out, will look at this when I get back.

    zeddy

  6. The Following User Says Thank You to zeddy For This Useful Post:

    Gunslinger (2016-05-17)

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Gunslinger

    In the attached file, I've modified the code so that it (hopefully) works with 32-bit and 64-bit Excel.
    I've tweaked the code so that when mouse-tracking is ON, changing worksheet or workbook will stop the routine, and restart the mouse-tracking on return to the workbook and sheet.
    (I haven't looked at what happens when moving the mouse outside of the Excel Application, but you may have already tackled this.)

    As far as not showing cell error values as the mouse-tracker-row passes over cells that have been formatted to 'white font', I added a conditional formatting rule to deal with this.

    Conditional formatting:
    Instead of using AND() to combine conditions in a cell-formula rule, you can combine cell-formula-rules for conditional formatting like this:
    =(condition1)*(condition2)*(condition3)
    So this rule..
    =ISERROR(A1)*(ROW(A1)=$A$1)
    ..says if the cell is an error value (e.g. #N/A or #VALUE or #DIV/0 etc) AND the current mousepointer row is the same as that as shown in tracker cell $A$1, THEN apply the specified format (which is yellow background and yellow font).

    Hope this helps.

    zeddy
    Attached Files Attached Files

  8. #7
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    ah, thank you i'll take a peek, i'll post what i have later on, need to remove some data from it first, and comment out the call for the code that fails, so you can see it.

    I actually had a number of those things covered i think, it'll be interesting to see our different approaches

    (oh, and it's not an N/A error from the worksheet, that's what the http call returns in the csv file that it then opens, it'll be easier to see when you have the workbook, rather than my naff descriptions lol

    edit:
    weird, ISERROR in conditional formatting isn't working for me, it's ignoring a #DIV/0 error ... grrrr
    Last edited by Gunslinger; 2016-05-17 at 17:23.

  9. #8
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    ok, here's my workbook, couple of things to note

    worksheet is locked, no password
    part of the code is disabled, by commenting out the "starteventhook" call in the timerprocedure, located in the row_highlighting module, this is because the exit code from this when you switch back to the workbook from another app crashes excel badly !

    to test, put a share code in the far left column and hit the current share price heading (it's a button)
    share name you put in manually, same for current holding and cost (incl fees)

    to turn on highlighting, check the highlighting box

    to test the highlighting, open the vba editor, and make it small height wise, you only need to see the title bar of it, if you can pin it "on top" do so.

    then you can open other workbooks, or worksheets, and you will see the in the title bar the timer code has stopped running, also it hides the ribbon in my workbook, but restores for others.

    Then, while doing the same test, go to another app, and you will see i've managed to trap the lost focus event too, and that stops the timer code running, it's how to restart it when i switch back that i'm having trouble with

    if you don't stop the timer, excel sucks up a good amount of processor percentage (or on my laptop it does!)


    oh, also you'll see i have a #div/0 error showing, in cell I26 if you remove the share cost/holding data, and yet in the conditional formatting i have code that see's the error, but the ;;; trick is failing



    EDIT, i posted a picture earlier, your code failed with an error as soon as i right clicked to start the timer "type mismatch" on the settimer, in the start timer sub - but for some reason the post wasn't allowed, so i'll try and attach it here


    EDIT 2.... ok, so it seems im not allowed to post attachments, really helpful.... pm me (if that even works) and i'll upload it somewhere for you to download

    EDIT 3.... got it, adblock really likes this site LOL.... workbook attached, and the image is the screen grab of your code failing

    Picture0003.pngStocks & Shares v2.xlsm
    Last edited by Gunslinger; 2016-05-17 at 17:51.

  10. #9
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    ok, so i've stumbled into something by chance......

    in my call back function, in the event hook module......

    Code:
        If LEvent = EVENT_SYSTEM_FOREGROUND Then
            GetWindowThreadProcessId hWnd, thePID
            If thePID = GetCurrentProcessId Then
                'Application.OnTime Now, "startTimer"
                startTimer
                'StopAllEventHooks
            End If
        End If

    i've commented out the troublesome StopAllEventHooks while testing, and i've found that the Appplication.Ontime line is actually causing excel to crash out, but, if i do as i have above the code works perfectly, however, it orphans the eventhook and doesn't stop it ... and when i re-enable the stop call, it crashes excel

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your StopAllEventHooks declares lHook as Long, but your collection contains LongPtr types.

    BTW, to make API calls 64bit compatible, you do not simply change all Longs to LongPtr.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #11
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    thanks rory, i'll take a look at that

    as i said, i'm a little out of my depth here, especially with the 64bit thing, so i'm trawling info from various places, and searches, i was worried about the cross declaration not matching, i'll double check, but i think i might have picked that one up already, unfortunately, i'm not 100% sure which need to be LongPtr, and which dont, so yeah, i was trying the "apply to all, then filter back" approach

    If you have any other pointers that would be appreciated

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just noticed you also seem to be missing the UnhookWinEvent declaration.

    I don't have 64bit here to verify, but I think the correct declarations for 64bit only are:
    Code:
    Private Declare PtrSafe Function SetWinEventHook Lib "user32.dll" (ByVal eventMin As Long, ByVal eventMax As Long, _
                                                                       ByVal hmodWinEventProc As LongPtr, ByVal pfnWinEventProc As LongPtr, _
                                                                       ByVal idProcess As Long, ByVal idThread As Long, _
                                                                       ByVal dwFlags As Long) As LongPtr
    Private Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As LongPtr, lpdwProcessId As Long) As Long
    Private Declare Function UnhookWinEvent Lib "user32.dll" (ByRef hWinEventHook As LongPtr) As Long
    If you need compatibility with 32 and 64 bit, you'll need some conditional compilation.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. The Following User Says Thank You to rory For This Useful Post:

    Gunslinger (2016-05-18)

  15. #13
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    you know, you might be right about that unhook event declaration, i'm at the dentists right now, so posting from my phone, which makes things difficult to display/read correctly

    i'll have a look over ur modified code and impliment it later if i'm feeling well enough, thanls for taking the time to go through it, appreciate it as i know how difficult it is to produce something for use on something/version u dont have

    edit:
    yes i will need cross compatibility, but was concentrating on getting it to work on one platform firsr
    in theory, if its working in 64 bit, it should be easier to apply said conditioning to use on 32 bit, than the other way around
    Last edited by Gunslinger; 2016-05-18 at 06:13.

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Gunslinger

    I liked your posted file.
    Here's something to get your teeth into (when you're ready):
    You can use =IFERROR() to simplify (and speedup) your formulas:
    Instead of
    =IF(ISERROR(bigformula, dothis, bigformula)
    use..
    =IFERROR(bigformula, otherwise)

    When you use the first method, Excel effectively has to calculate the bigformula twice (once to see if it's an error, and then all over again if it's not an error).

    Here's some updated formulas:
    Code:
    column [F]:
    =IF(ISERROR(G6/E6),0,ROUNDUP(G6/E6,4))
    =IFERROR(ROUNDUP(G6/E6,4),0)
    
    column [F]:
    =IF(ISERROR(ROUNDUP((E6*K6)-G6,2)),0,ROUNDUP((E6*K6)-G6,2))
    =IFERROR(ROUNDUP((E6*K6)-G6,2),0)
    
    column [W]:
    =IF(ISERROR(V6/U6),0,ROUNDUP(V6/U6,4))
    =IFERROR(ROUNDUP(V6/U6,4),0)
    
    column [Y]:
    =IF(ISERROR((V6+R6)/U6),IF(ISERROR((G6+I6)/E6),0,ROUNDUP((G6+I6)/E6,4)),ROUNDUP((V6+R6)/U6,4))
    =IFERROR(ROUNDUP((V6+R6)/U6,4), IFERROR(ROUNDUP((G6+I6)/E6,4),0))
    zeddy

  17. #15
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    that's quite funny, given i now have 3 less teeth to get into things !!!

    i like the look of that condensed formula code, ill take a closer look at that.


    Rory, it looks like some of your code adjustments worked, but others didn't, i think i might have some orphaned longptr references, once i've got those cleared up, we'll see how things go, at the moment, all looks good till i close the work good then it crashes, so the eventhandles are not being killed by the looks of it
    Last edited by Gunslinger; 2016-05-18 at 09:17. Reason: typos

Page 1 of 4 123 ... 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
  •