Worksheet Doesn't Activate

[BrianBarrett]

Excel 2003 VBA

This is very strange asI am having 2 issues that may or may not be connected or indictative of an underlying issue:

1) I have a number of sheets each with code, some the same, some different, that are attached to the activate event. They tend to turn off application.enableevents, do some changes, and turn it back on.

I have done some changes to one sheet, say sheet 1, and when I click on its tab, it appears but the activate event does not happen (I have put stop/debug buttons on the code). All other sheets do activate ok. I assume the application.enableevents code is universal across the whole workbook.

Strange is that to see if it is a sheet I am moving from that is responsible I have put a worksheet_deactivate event on another [for test purposes] sheet, say sheet 2. So now when I move from sheet 3 to sheet 2, the activation event for sheet 2 kicks in. When I move from sheet 2 to sheet 3, the sheet 2 deactivate event kicks in then the sheet 3 activation event. When I move from sheet 2 to sheet 1, however, neither the sheet 2 deactivate event nor the sheet 1 activation event happens.


2) Other issue. One process the sheet 1 activation process calls, though I have to manually call it now, is a formatting routine. The code is along the lines of:
for each singleCell in partRowOfCells
singleCell.entirecolumn.autofit
next

partRowOfCells is around 8 cells. When we are going around the loop, at about cell number 3, and on the autofit stage, the procedure just drops out. And I mean no line in debug is highlighted, no error is trapped. The procedure just ends. I believe the thread also ends, so we do not drop back to any calling routine.

This only happens on sheet 1 and not on the other sheets.

 

[BrianBarrett]

Ok - I do now feel this issue is sorted and I believe I understand, sort of(!), what has been going on.

Part of the changes I had put on recently was a UDF function to say if a cell contained a formula (basically doing a rng.hasformula) command and giving back the answer as a boolean.

This UDF, however, and depending on how far through the process it was, was contained in both conditional formatting and potentially in the cell formulas over, say, 400 cells.

I am guessing that this formula has been activing as volatile, although all the comments tend to imply udfs are normally involatile. And that as a sheet is activated, all the cells attempt to update and basically blow the mind of excel and whilst the cells update, the on activate trigger and (for th previous sheet) the deactivate event appear to get lost.

And this is what was happening with the autofit. Of course changing the size of the cell is recognised as a change and therefore many volatile commands were firing. Hence why the procedure just, basically, gave up.

Now I have tried making the command non-volatile. This works well with the sheet activate event, but at some stage the number of changes to actual cells containing the formula, do appear to blow the mind of excel.

I had, or believed I had, tried taking the UDF out before, but the unpredicatable nature of this error made it difficult to tie down changing 1 thing, and actually fixing the error!

I think I will need to take the udf out, but at least the strange events that just seemed weird have been sorted.

Many thanks

 

[RoryA]

The UDF isn't volatile, unless you make it so, but CF is volatile, so if you use the UDF in CF, it's effectively a volatile UDF.