[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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
0
Chris Bottomley
Asked:
Chris Bottomley
  • 11
  • 4
  • 2
1 Solution
 
patrickabCommented:
Chris,

What's the Worksheet_Change sub like?

Patrick
0
 
Chris BottomleyAuthor 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Chris BottomleyAuthor 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 BottomleyAuthor 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 BottomleyAuthor 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 BottomleyAuthor 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 BottomleyAuthor Commented:
Haven't but here goes!

Chris
0
 
Chris BottomleyAuthor 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 BottomleyAuthor 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 BottomleyAuthor 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 BottomleyAuthor 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
 
Chris BottomleyAuthor 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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 11
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now