Highlighting rows in excel on mouse over?

Topic

New Reply

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 :rolleyes:

http://windowssecrets.com/forums/showthread//168376-code-to-highlight-Excel-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

Reply | Quote

Replies

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

44493-z-sheriff

Reply | Quote

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 !

Reply | Quote

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

Reply | Quote

Hi Gunslinger

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

zeddy

Reply | Quote

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

Reply | Quote

whoopsie…..

the sub was highlighted yellow as well, but i lost that colouring before/when i grabbed the screenshot

Reply | Quote

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

Reply | Quote

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

44503-Picture000344504-Stocks-Shares-v2

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 :blush::blush:

If you have any other pointers that would be appreciated 🙂

Reply | Quote

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.

Reply | Quote

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 :p
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

Reply | Quote

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

Reply | Quote

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 🙁

Reply | Quote

Hi Gunslinger

Be true to your teeth, or they will be false to you.

..as long as you have more teeth than fingers I wouldn’t worry too much.

zeddy

Reply | Quote

lol, i should officially be worried then !!

anyway, ….. 99% there, this code is kicking my a** :^_^:

here’s what i have so far, and it does seem to work ok (mostly) except if i go anywhere near the vba editor, with the highlighting on, or on at any point in the session, to be honest, it’s very very flakey, and unstable

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 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 Long, lpdwProcessId As Long) As Long
Private Declare PtrSafe Function UnhookWinEvent Lib "user32.dll" (ByRef hWinEventHook As LongPtr) As LongPtr


Dim 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 StopAllEventHooks()
    If pRunningHandles Is Nothing Then Exit Sub  ' no need to run unhook event, if not focused away from excel
    Dim vHook As Variant, lHook As Long
    For Each vHook In pRunningHandles
        lHook = vHook
        UnhookWinEvent (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 Long, ByVal LEvent As Long, _
                            ByVal hWnd As Long, ByVal idObject As Long, ByVal idChild As Long, _
                            ByVal idEventThread As Long, ByVal dwmsEventTime As Long) As Long
    
    On Error Resume Next
    Dim thePID As Long
    
    If LEvent = EVENT_SYSTEM_FOREGROUND Then
        GetWindowThreadProcessId hWnd, thePID
        If thePID = GetCurrentProcessId Then
            startTimer
            StopAllEventHooks
        End If
    End If
    
    On Error GoTo 0

End Function

i’m calling the stopallevents function from the before workbook close event too.

I have found myself wondering if the vba editor thing is somehow connected to it maybe having the same handle as the main excel app, not sure, it’s odd, because for a while, i had it working the other way around, i could go in and out of the vba editor no problems, but, on focusing to say browser would crash it on exitigng excel, now, the vba editor can crash it when returning to excel, it could also have something to do with the pRunningHandles collection not technically being empty, but having an invalid reference or something odd that is causing it to crash at that point

i’ll try and post a new workbook later, i’m just working on something else in it.

Also, the method used for highlighting the cells/rows, works well, but, because it replies on cell value, it triggers the change event, and thus the file saved flag, and there is no way i can see to stop that, without the conditional formatting not triggering, unless i try and do it all from within the VBA, including setting the background colour, anyone got any thoughts ?

Reply | Quote

scratch my last, it seems that minimising excel also causes it to crash, damit, i though i was getting somewhere

Reply | Quote

Hi Gunslinger

I have a method that will ‘detect’ whether any ‘real changes’ have been made to a workbook.
The detection can be set to operate on specific ranges.
This would be compatible with the ‘cell-row-highlighter’ discussed above.
If you delete a particular cell entry, e.g. a cell that has the text value “zeddy”, and then re-type the cell contents, then as far as Excel is concerned, you have ‘changed’ the workbook, whereas, we would say ‘nothing is different’.
So, unless anything really changed within your specified ‘detection range’, you won’t be prompted to ‘save changes’ on exit from the workbook.

zeddy

Reply | Quote

sounds interesting

Reply | Quote

Code:

Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long

hWnd should be a LongPtr because it’s a window handle. And the same in the WinEventFunc declaration.

Reply | Quote

ah, i’d seen one of those but i think i missed the second, i’m still working on some other aspects within the work book, once able i’ll clean out and re-post so you can see it in context.

I’m struggling to debug where it’s stepping away from excel, as in which module is triggering the start/stop code and the event hook, because i seem to have degenerated to minimising excel as causing it to crash as well. I was doing some reading last night on the 32-64 bit conversion requirements…. and i woke up with a headache this morning ! lol …. i’m going to cave at some point shortly, and drag out my old laptop that has 2010 32 bit on it, and just see if i can get the code working on that, then try to convert it, because i think i have some errors in there not just related to the 32 vs 64 faff

Reply | Quote

Hi Gunslinger

Looking forward to a re-post, when you are ready and able. No rush. Chew it over slowly.

zeddy

Reply | Quote

it’ll be later this evening, i’m actually using it right now, whilst the markets are open i’ve got a couple of shares that i’m watching the share price on closely, and the volume of shares vs the share price is important, because i need to average down on them :wacko:

Reply | Quote

ok, so this has caused me a few formatting issues and hiccups, along with some mathematical conundrums !! hence the slow posting.

and then, after i cleared out my test data, and added a few entries, it seems i’ve got another bug, it’s inserting a column (at K) and dropping the data in the wrong column during the price import stage, grrrr, it then works fine after that, worksheet is unlocked during the import process.

along with that, the formulas are not fully tested, so don’t trust the values 100%

and…. i still have the highlighting issue to deal with,

Reply | Quote

ok, so the cell/row insertion was easily fixed

Code:

.RefreshStyle = xlOverwriteCells

addition to the QueryTables import code

Reply | Quote

Hi Gunslinger

..just before I leave, I simplified your Checkbox_Macros module. Please have a look!

..I selected all your checkboxes on the sheet, then assigned the same macro to ALL of the checkboxes.

I hope you like the method.
..oh, and I hope it works too.

zeddy

Reply | Quote

Hi Gunslinger

..just before I leave, I simplified your Checkbox_Macros module. Please have a look!

..I selected all your checkboxes on the sheet, then assigned the same macro to ALL of the checkboxes.

I hope you like the method.
..oh, and I hope it works too.

zeddy

Zeddy,

Thats a KEEPER! And I did! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Gunslinger

..hope to look at this when I get back.
I’m off the grid for a few days.

zeddy

Reply | Quote

hmmm, i wondered about that option, of using one macro to try and cover all, but, the time to copy paste and modify, was less than finding the solution to make it singular lol

will take a look, cheers

EDIT:
hmmmm, strange, you can now select the check boxes, when there is no value in the cell to the left of them, interesting, that’s not supposed to happen

EDIT 2:
yeah, you broke the change event triggering off the changes created by the check boxes being ticked, i suspect some sort of loss of linked cells perhaps – scratch that, it’s that stupid inserted row that’s killed the cell linking, and i thought i’d used the absolute reference for the linked cell, damit!

Reply | Quote

Hi Gunslinger

..you are perfectly correct.
But now that you know how to, it can save lots of time in the future.
..and if you added another 20 rows, it wouldn’t mean more pasting more code again etc. etc.

zeddy

Reply | Quote

yeah i originally set it up to allow for extra rows, but the addition of the check boxes blew that out, think i’ll just add extra sheets lol!

still have some niggles in the formulas equating slightly differently due to round up/down variations.

thanks for the code adjustment, i’ve included it in my current file, along with multi cell select bypass for the worksheet change event !

still cant see the wood for the trees so to speak in relation to that event hook killing

Reply | Quote

zeddy….sweet!

Code:

Sub clickCheckbox()

zShape = Application.Caller         ‘name of shape that was clicked
‘zAddr = ActiveSheet.Shapes(zShape).TopLeftCell.Address  ‘cell underneath clicked shape
zRow = ActiveSheet.Shapes(zShape).TopLeftCell.Row  ‘row number of clicked shape

Run “Sheet1.Worksheet_Change”, Range(“O” & zRow)

End Sub

Reply | Quote

Zeddy,

Here’s a slight modification that will allow the routine to work on multiple sheets.

Code:

Option Explicit

Sub clickCheckbox()

   Dim zShape  As String
   Dim zAddr   As String
   Dim zSht    As String

   zShape = Application.Caller  '*** Name of shape that was clicked ***
   zAddr = ActiveSheet.Shapes(zShape).TopLeftCell.Address(, , xlA1) 'Cell underneath clicked shape
   zSht = ActiveSheet.Name
   Run zSht & ".WorkSheet_Change", Range(zAddr)
   
End Sub  'clickCheckbox

BTW: I don’t see the reason for getting just the row number and hard coding the column when you’ll most likely be using an Intersect in the WorkSheet_Change event anyway?

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

i think he probably did that because my check boxes are in a vertical row, and the macros i originally had, were tied to the row number, and why i numbered the check boxes to correspond to those rows, as such the column ID is immaterial/surplus to requirements in this instance, plus, the change event is triggered and associated with the max purchase value cell in the corresponding row, and this is required to then trigger other sections within the worksheet change event code

anyone found anything in regard to the eventhook killing, i can’t find the issue, i can generate slightly different behavior, but i can’t get ti to sever the hooks correctly before closing the workbook, causing an excel crash

Reply | Quote

latest version of the workbook, with some tidying of the code and slip streaming of formatting etc, i have got other worksheets i want to add to this, and potentially create a calculator on a user form i can load with the work book, but need to get this constant crashing caused by the Event Hook code as it’s driving me up the wall

Reply | Quote

Hi Gunslinger

I’ve made a few code updates to simplify things.
A little more testing before I post my updated file this weekend.

zeddy

Reply | Quote

Hi Gunslinger

..here’s an updated version from me.

I had a tweak at the code (see module [Get_Share_Prices] )
I can’t help myself.

Rather than ‘write formulas’ in vba code, I usually place any required formulas in (hidden) row 1, and then use vba to ‘paste’ the formula to the required range as required.
This means it is easier to fix or update such formulas on the sheet, rather than recoding in vba.
e:g

Code:

    ' Re-insert formulas removed from Profit/Loss column
    [h1].Copy                                   '[H1]=IFERROR(ROUNDUP((E1*K1)-G1-I1,2),0)
    [Profit_Loss].PasteSpecial xlFormulas       'paste formulas to named range

I was also going through the [Row_Highlighting] module to see if any changes would help.

I’m looking at the Hook code events, but wanted to post what I have so far.

zeddy

Reply | Quote

you’ve changed something in the internet check code ??

44625-Picture0001

that’s on initial opening 🙁

Reply | Quote

i’ll have a peek, but have to bear in mind the worksheet change event triggers i have at several points in the worksheet, to which i have added yesterday with the inclusion of a series of comments that get adjusted as the sheet changes take affect.

Along with a few other alterations i’ve made, it’s running quite well right now, just have one little niggle to look at (price and volume issue in the planned section, without a value in there, the others delete each other when entered !)

the main sticking point is the hook even.

thanks for your continued help with this, i have a comment formatting question related to this work book, but i’ll post that in a seperate thread.

Reply | Quote

ok ,i’ve stepped through your other changes, and integrated them, i was wondering why you changed the build url section at first – i was working on the idea of fixing the end point by named cell, and then it doesn’t matter if rows are added or not later, but i forgot named ranges expand automatically like that

as for putting the formula in the cell, and copying from there, yeah that’s one way, i usually do have stuff orphaned all over the worksheet like that, and it’s caused me headaches in the past as things have changed over time, hence why the one’s i have used, are off to the right and named

Reply | Quote

latest version, incorporating those changes, and some others (font colours and new comment on the profit/loss cells)

EDIT:
something in your changes that i’ve implemented has killed the comment updating, as i suspected messing with the change event trigger i think, or my formula coding, damit!
EDIT2:
yup, copying and pasting the formulas from the H1 cell down, was the problem, the following code that then read the value, was not seeing it, have to trigger a sheet calculate event or thec omments fail to update, or, go back to what i had that was working :rolleyes:

Reply | Quote

Reset the Formula inserting code to avoid the non-updating issue, so many different triggering events it’s difficult to avoid getting into a nasty loop, or constantly calculating the sheet, for now this seems ok, so i’ll stick with that method, i did however incorporate some of your other stuff, thanks 🙂

Changed a few things to make operation a little more user friendly, and fixed a bug or 2, so here’s v8

Reply | Quote

Hi Gunslinger

Re Post#42 Comment updating:
In your routine GetData
..the calcs were in Manual when the [H1] cell was copied to the range.
..the Insert_Value_Comments was then called below, before the calcs were set back to automatic.

So, either put the call to Insert_Value_Comments after the the calcs were set back to automatic, or,
add line to do the calcs, i.e.
[H1].Copy ‘[H1]=IFERROR(ROUNDUP((E1*K1)-G1-I1,2),0)
[Profit_Loss].PasteSpecial xlFormulas ‘paste formulas to named range, as it pastes down, numbers will increment
[Profit_Loss].Calculate ‘re-calc formulas after pasting

When you use vba to put a formula into a cell ‘one-at-a-time’ (as your previous method), the formula is ‘calculated’ as it is placed into each cell, regardless of whether calc mode is manual or not. So that is why it worked previously.

So I’ve put my method back in the attached updated version. Cos I like it.

Also, I’ve re-done the module [Internet_Check] to make it as simple as possible.
I’ve also had another go at simplifying the module [Row_Highlighting]

Still haven’t touched your module [System_Event_Hook_Code]

The other stuff should be as per your v8.

When the toggle highlighting rows is on, your comments are still shown as the mouse floats over them.

Hope this helps.

zeddy

Reply | Quote

When you use vba to put a formula into a cell ‘one-at-a-time’ (as your previous method), the formula is ‘calculated’ as it is placed into each cell, regardless of whether calc mode is manual or not. So that is why it worked previously.

ah now that i did not know, could explain a couple of things ! – i have since altered that formula, to accommodate something new in the way i wanted the sheet to behave, but i have gone back to my original code for that section, cause i had issues elsewhere, which kinda fell into the “dunno why it’s doing that” category, so i went back lol

I’ll have another look at your internet check code, that is one thing i’m not massively happy with i have to admit, because when there is a connection available, it can still hang for a while if i’m connected via my phone and the signal is poor, so i might look to tighten it up, and cancel out earlier, but will look see what you’ve tweaked 🙂

Still haven’t touched your module [System_Event_Hook_Code]

i’ve been playing around with that a little, gone back to calling the function from an intermediary sub routine, like it was in the original code i got it from, and it shouldn’t make any difference, but, sometimes it seems to, like earlier, i had it running ok, if i switched out to notepad, and then closed, but if i went to the vba editor, it failed, but, that was not a consistant result – i’m about at the end of my abilities with it, i might strip it back to 32 bit only, and put it onto my spare laptop which has 32 bit office on it, and see what happens.

yup, the comments should be modal all the time, but that’s more an excel thing than anything else, just the way it is

Reply | Quote

Hi gunslinger

..I’ve tweaked a few more things, but I need to test a little more so you don’t break it so quickly.
Will post another update after testing.

zeddy

Reply | Quote

ok, no problem, the highlighting unhook has me beat, it’s the only bit i can’t work out properly, i’ve added a couple of things, and a few rows to the worksheet then sweared heavily at it as it screwed up the conditional formatting – adding new entries for the copied rows, not expanding the given “applies to” entries/ranges, probably something to do with the cell designation default in conditional formatting of $x$x which has always been a pain in the ass at times

anyway, all is working well, minus the highlighting, and the fact yahoo finance data can be pretty inaccurate at times !

will await your latest to see if you’ve cracked it

Reply | Quote

Hi Gunslinger

Still looking at that unhook issue. The book I looked at for windows API Calls was so thrilling that when you put the book down you just can’t pick it up again.

It would help to know what your screen layout is. I assume you are using two monitors.
Is the workbook open in ‘full-screen-mode’ on the left-monitor???

As for the conditional formatting, you need to use INDIRECT in the cell addressing to deal with insertion of new rows etc etc etc so this can be fixed.

zeddy

Reply | Quote

Morning,

Laptop, 17″ screen Res 1920×1080

Single monitor 99% of the time, occasionally hook to external, but not often.

I do however use FileBox eXtender (http://www.hyperionics.com/files/) so i can pin, or roll windows up. Although it does have a bit of a bug, if you display folder in list, sort and group by date created, then occasionally it crashes explorer when your scrolling left or right, or resizing the window width wise !…. so i might swap to display fusion pro, which does mostely the same functions (albeit intended for dual monitors) minus the handy favourites menu which i use

INDIRECT huh, hmmmmm, i’ll have to research that

Reply | Quote

found something else that goes pair shaped when you insert rows…. the objects associated with them, lol, all my tick box names went pear shaped, and their linked cells were a mess, so, no more inserting rows !!

Anyway, made quite a few changes, so thought best to upload a new copy of the file, in case anyone looks to fix things i’ve already tweaked, quite a number of things now changed, and the worksheet change events are getting harder to keep track of and avoid duplication/re-triggering, i’ve also tightened down the calculations so less round up vs down conversion errors, and i’ve also got creative with some of the cell formatting, switching it around with conditional formatting, almost beginning to wish i’d left the share prices in pence !!

I did play with selective locking of the sheet, adding a parameter to the .protect call, allowing the vba code permission to alter locked cells, but not the user interface, but it failed spectacularly, so i went back to the other method of unlocking/locking each time

Highlighting is still broken

Reply | Quote