Solved

Excel event handler

Posted on 2011-02-21
17
424 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Chris Bottomley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
  • 2
17 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34941978
Chris,

What's the Worksheet_Change sub like?

Patrick
0
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34942014
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34942107
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34942122
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34942123
Are there UDFs in the workbook?
0
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34942281
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
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34942288
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34942291
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
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34959712
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34960207
Without seeing the workbook, I would suspect corruption. Have you tried recreating that sheet?
0
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34960238
Haven't but here goes!

Chris
0
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34977475
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
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34982471
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34983641
Does the same behaviour still occur if you censor the data?
0
 
LVL 59

Author Comment

by:Chris Bottomley
ID: 34983682
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 0 total points
ID: 35025214
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
 
LVL 59

Author Closing Comment

by:Chris Bottomley
ID: 35174546
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question