Solved

Excel event handler

Posted on 2011-02-21
17
418 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

777 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