Solved

Excel event handler

Posted on 2011-02-21
17
415 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
Comment Utility
Chris,

What's the Worksheet_Change sub like?

Patrick
0
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
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
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
Are there UDFs in the workbook?
0
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
Without seeing the workbook, I would suspect corruption. Have you tried recreating that sheet?
0
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
Haven't but here goes!

Chris
0
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
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
Comment Utility
Does the same behaviour still occur if you censor the data?
0
 
LVL 59

Author Comment

by:Chris Bottomley
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now