Excel event handler

Firstly, an apology I have a file packed with proprietary data both in the sheets and also in the (extensive) code so cannot supply it and attempts to reproduce a sanitised equivalent with the issue have failed.

There are certain cells with text, (i.e. NOT a formula) that when I edit them the change event handler is not triggered.  Other cells the handler is triggered before and after but everytime I try to edit a 'dodgy' cell the handler does not trigger at all.

Note:

1. The suspect cells have string contents
2. The change event continues to be triggered on other cells.
3. There are a significant number of cells across 2 columns looks like all cells in the subject columns
4. For the normal event handler the VBE displays activity
5. For the suspect cells the VBE displays running for a longer period than the normal event handler.
6. The suspect cells do not enter the change handler at all ... first instruction as stop (and/or breakpoint) is not triggered by these cells.
7. Sometimes!, after editing a suspect cell the cell that has the focus will flash circa 0.5s black highlight and off again ... don't know if it's related.

Anyone have useful clues?

Chris
LVL 59
Chris BottomleySoftware Quality Lead EngineerAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerAuthor Commented:
Hey ho!

The end result was I had accidently applied a conditional format to a full column and for some reason this cause the whole sheet to take forever to calculate and then the cursor movement to freeze (on another column).though the cell data in the edit bar reflected the actual location.

Removing the full column conditional formatting has returned full functionality both the reorted issue with change event not firing and also teh extra symptom on screen freeze!  SInce the issue was not use of a UDF.  Incidentally the column with CF's that extended across the whole column worked fine ... it was the other columns where the CF's stopped at row 500 that triggered the issue - and these did not refer to the other columns and nor did they use UDF's.

The issue was fully repeatable and required the workbook to be exited to resume normal functionality.  The key to resolving the issue was breaking on the calculate event for the sheet which pointed me towards the range of triggers for the CF in the columns going on forever.

With this in mind the solution was to restrict the CF to a smaller range and then all was well.

Chris
0
 
patrickabCommented:
Chris,

What's the Worksheet_Change sub like?

Patrick
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Shed loads of range checks to do column specific moding ... essentially along the lines of:

set rng = ...
with rng
   do something with this cell, other cells on the same sheet and still more cells on another sheet
end with

...


set rng = ...
with rng
   do something with this cell, other cells on the same sheet and still more cells on another sheet
end with

Prefaced at various points with enableevents true and false BUT as implied in teh initial post this is not the issue enableevents is true before and after running whatever the cell edited.

Chris
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
patrickabCommented:
Chris,

That's very odd that defining the ranges for the sub to be triggered by doesn't make any difference. Not come across that myself.

Patrick
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Also note that the same routine an instant later with no more ado will always process a different cell quite happily using the column specific range block ... it's just these specific columns that dont want to work!

Chris
0
 
Rory ArchibaldCommented:
Are there UDFs in the workbook?
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Indeed there are and one of the functions for a column that works is to use the same functions as for one that fails.  And I do mean the same the only difference being the cell in the column that is affected.  The row and formula entry in the code is identical otherwise as being a range variable defined the same.

For what its worth it is of the form


strtemp1 = sheets("different sheet").cells(cel.row, thisworkbook.names"fred").referstorange.column).address(false, false)

strtemp2 = cells(cel.row, thisworkbook.names"fred").referstorange.column).address(false, false)


sheets("different sheet").range(strtemp1).formula = "=getred(& strtemp2 & ")"

If I have typoed anything silly therein I would stress the implementation is correct within the context since it works for one column but not the other and the original is a cut and paste that I have just refreshed as a test even though teh sub isn't entered at all and at run time the code block is indeterminate until the sub is actually entered.

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
BTH

getred itslef calls another function. (coding for getred/amber/green is identical but this function tests for string presence before processing for specific characters from the reference cell.

Also immediately after calculating the formula, it is copied as text by the macro before terminating.

Chris
0
 
Rory ArchibaldCommented:
Ignoring the fact that you should avoid hardcoding ranges or sheetnames in UDFs, is there any Conditional Formatting in use that relies on UDFs?
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Apologies I missed the notif ... no there are no UDF's used in conditional formatting.

Of the two columns that display the issue, one uses the same CF structure as one that does not display the issue ... and I have cut and paste it to see if that was a factor ... with no indication of any change.

Chris
0
 
Rory ArchibaldCommented:
Without seeing the workbook, I would suspect corruption. Have you tried recreating that sheet?
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Haven't but here goes!

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Still working on it ... the document has live data being edited multiple times a day so given the complexities I keep having to go back ... still open therefore

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
Nope that didn't change anything ... I copied the data to a new sheet and then reset all the named regions to the new sheet and copied edited version of the change macro ... sheet name reflection.  No change to the problem with the change handler i'm afraid.

Chris
0
 
Rory ArchibaldCommented:
Does the same behaviour still occur if you censor the data?
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
I would assume yes but there is a lot of interdependant data so the problem with obscuring the data for upload is making consistent changes of all the different data such that I can guarantee that the file is sanitised.

If I were to try and supply just the one censored sheet I think it will be non viable due to the lookups implemented in VBA .... but i'll have a go next week.

Chris
0
 
Chris BottomleySoftware Quality Lead EngineerAuthor Commented:
My thanks to the experts for their input.

The solution whilst difficult for me to accept was repeatable and therefore the only correct action is to record this as the solution.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.