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.

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried cleaning up your code?

http://www.appspro.com/Utilities/CodeCleaner.htm

 

[BrianBarrett]

Hi

I have not tried that and will not be able to do that until this evening. My gut feeling is that there is something 'screwed-up' with the worksheet but I don't know what. the code I am having problems with, especially the autofit part, works perfectly well on other sheets. There also appears nothing 'controversial' in the code or particularly complex

 

[Norie]

Can you post the code?

PS Why are you looping for the autofit?

Is the range partRowOfCells non-contiguous?

 

[BrianBarrett]

Hi

I will post the code later when I am able. The looping is because I also test for whether a column is hidden or not. That testing code has been removed and I still get the error. When I try and perform the autofit in 1 go, the code still drops out of the procedure but you cannot see what column is causing the problem.

 

[BrianBarrett]

The autofit code is as detailed below. Note that:
- the 'set fitcell = nothing' has been added to try anything to get it working and the error happens without it
- the hidden test can also be removed and the error happens
- the same code works fine on other sheets that also have an autofit range
- I have tried selecting the ranges so I can see what the selected cell is and the ranges/cells are as expected.


I debug this area by stepping through on a line by line basis and the procedure just drops out when you execute the autofit line.

    On Error Resume Next
    Set areaRange = ws.Range("Autofit")
    errorFound = Not (Err.Number = 0)
    Err.Clear
    On Error GoTo errSort
    
    If Not (errorFound) Then
        For Each fitCell In areaRange
            If ws.Cells(2, fitCell.column).EntireColumn.Hidden = False Then
                fitCell.EntireColumn.AutoFit
            End If
            
            Set fitCell = Nothing
        Next
    End If

 

[Norie]

Do you have On Error elsewhere in the code?

If you comment it out while debugging do you get any error messages?

 

[BrianBarrett]

Hi Norie

Not fully sure what you mean. All on errors resume next are matched with a reinstatement of 'on error goto errSort'. And, of course, the last thing we do before going into the autofit code is to do a 'on error goto errsort'.

Additionally I have also tried a line of code before the autofit line of errorNumber=err.number. I can then see if there is any inherent error that does not appear to be trapped before we execute the autofit line.

Finally, the errSort area has a debug stop so I would know if that was invoked.

But I suppose on it's own this autofit part of the issue is very much like an error not being trapped at procedure level and attempted to go to the calling part.

 

[Norie]

What I mean is comment out every On Error... while debugging.

On Error can actually hide errors.

What does the code with tha label errSort do anyway?

PS Why do you clear the error before On Error Goto errsort?

I know that will still work but I don't see why you would clear the error.

 

[BrianBarrett]

Hi norie

the on error resume next/on error goto errSort pairs are there for code where an error is possible such as a range name doesn't exist on the worksheet. If I remove them the errors will cause the program to go to errSort when it should just conclude the range doesn't exist and move on.

The errSort is very simple and simply does an msgbox saying there is an error, number xxx, description yyy and ends the routine. In normal running an error not tested in the main part of the code on the error resume/on error goto errSort, will be catastrophic.

Oh - and the err.clear statement is there in case an error does happen and from that I deduce, for example, that the hamed range doesn't exist. Strictly speaking I should test the error number and if the error number is not consistent with 'named range not there' I should do an err.raise rather than just ignore the error. The code does, therefore, assume when an error does happen that it will be the errror code consistent with the expected error situation.

So is it still a good idea to comment out the error handling code?