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.

 

[Norie]

While debugging yes.

 

[BrianBarrett]

Hi Andrew

I have run the code cleaner and I still have the same issue with the code. Namely that when clicking the tab for, say, worksheet 1, when on worksheet 2:
- the deactivate event for worksheet 2 is not firing
- the activate event for worksheet 1 is not firing.

But, when moving from sheet 2 to sheet 3:
- deactivate event for worksheet 2 fires
- activate event for worksheet 3 fires.

and moving from sheet 3 to sheet 2:
- activate event for sheet 2 fires
- [there is no deactivate event for sheet 3]

Plus, of course, that may be connected, the issues with the autofit commands whereby when on, and only on, sheet 1, the procedure is just ending mid execution when doing an autofit comment.

Hope you can all still help - this is really baffling me, largely becuase the error is only happening on 1 sheet.

I have also virus scanned the sheet at work and at home and it appears to be ok!

 

[BrianBarrett]

Hold fire guys - we may have some action. The event does appear to be firing after all now. I have taken out the autofit range so will now put that back in to see if it will work.

 

[Norie]

I can only think that somewhere events are not being enabled after an error.

Try adding Application.EnableEvents to your error handling where appropriate.

 

[BrianBarrett]

Cancel the last statement - it has gone back to not firing! I was doing various testing, but had not gone to test the autofit part.
I had earlier taken a copy of sheet 1, and when I clicked on that sheet, and came back to sheet 1, sheet 1 activate was no longer firing. Other sheets are firing ok.
Another issue with sheet 1 is the cell protection. I had gone into cells I have an issue with between it working, and the event going back to not firing.

The issue is that I have cells with the property of lock cell. When I protect the sheet I can still enter data into them. The options on the sheet protect are, select locked/unlocked, sort and auto filter. I believe this should stop cells from actually being modified.

All these 3 different, but happening at the same time make me wonder what is going on!

 

[Norie]

So you have worksheets that are protected?

That could be the problem.

Try unprotecting the worksheet(s) before the code and then reprotecting once the code is done.

 

[BrianBarrett]

That I already do!!

Remember the code has been working fine for many months until I put some relatively small changes onto sheet 1. This was originally had some extra vb code. The code has been removed until I get this working so the situation I am in is that sheet1 is probably simpler code wise that is was last week when it was working fine.

Locking, of course, would not explain why the activate event is not firing that I feel is at the heart of all this.

 

[Norie]

Brian

To be fair, you've only posted a small piece of code which doesn't unprotect/protect.

I'm not actually saying it's the locking that's causing the event not too be triggered.

What changes are you making to the worksheets anyway?

You might not even need to turn off events.

 

[BrianBarrett]

Yes - point taken. I will put the code from the activate event and the routine, fixBorders, where the autofit error was.
The code can clearly be done, differently, but it does exist in sync with other code so in advance I appreciate things can be done differently/better, but I don't wish to start changing the whole code of the project.

Ok, the activate command for sheet 1, aka ChildList, is:

Option Explicit
Private Sub worksheet_Activate()
    Dim dataObj As New dataObject
    
    On Error GoTo errSort
    
    
    On Error Resume Next
    dataObj.GetFromClipboard
    On Error GoTo errSort
        
    Call colourChildren
    Call FixBorders
    
    ActiveSheet.Unprotect
    
    Columns(ActiveSheet.Range("Babies").column).Font.Color = ActiveSheet.Range("Babies").Font.Color
    Columns(ActiveSheet.Range("Littles").column).Font.Color = ActiveSheet.Range("Littles").Font.Color
    Columns(ActiveSheet.Range("Bigs").column).Font.Color = ActiveSheet.Range("Bigs").Font.Color
    Columns(ActiveSheet.Range("PreSchool").column).Font.Color = ActiveSheet.Range("PreSchool").Font.Color
    
    ActiveSheet.Protect AllowFiltering:=True, AllowSorting:=True
    Call WindowZoom
    
    On Error Resume Next
    dataObj.PutInClipboard
    On Error GoTo errSort
    
    Exit Sub
    
errSort:
    MsgBox "Error when opening ChildList " & ActiveSheet.Name & ".  Number " & Err.Number & " - " & _
        Err.Description, , "Error Detection"
    
    
End Sub

and for the fixborders routine - reference in the above code, is:

Sub FixBorders()
    Dim fitCell As Range
    Dim headRow As Single
    Dim totalsRow As Single
    
    Dim currentArea As Single
    Dim areaName As String
    Dim areaRange As Range
    Dim borderWeight As Single
    Dim endFound As Boolean
    
    Dim errorFound As Boolean
    Dim eventsEnabled As Boolean
    Dim sheetLocked As Boolean
    Dim screenUpdating As Boolean
    
    Dim originalSelection As Range
    Dim ws As Worksheet
    
    
    On Error GoTo errSort
    
    
    eventsEnabled = Application.EnableEvents
    screenUpdating = Application.screenUpdating
    
    Application.EnableEvents = False
    
    
    Set ws = ActiveSheet
    sheetLocked = ws.ProtectContents
    
    
    headRow = ws.Range("Headrow").row
    totalsRow = ws.Range("Totals").row
    
    Set originalSelection = Selection
    
    
    Application.screenUpdating = False
    
    ws.Unprotect
    If ws.FilterMode = True Then
        ws.ShowAllData
    End If
    
    currentArea = 1
    
    Do
        areaName = "Area" & currentArea
        
        On Error Resume Next
        Set areaRange = Range(areaName)
        
        errorFound = Not (Err.Number = 0)
        Err.Clear
        On Error GoTo errSort
        
        If Not (errorFound) Then
            'can now do border
            ws.Cells(headRow, areaRange.column).Resize(totalsRow - headRow, areaRange.Columns.Count).Select
            
            
            If currentArea = 99 Then
                borderWeight = xlThick
            Else
                borderWeight = xlMedium
            End If
            
            With Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = borderWeight
            End With
            
            
            If currentArea = 1 Then
                borderWeight = xlThick
            Else
                borderWeight = xlMedium
            End If
            
            With Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = borderWeight
            End With
                
                
            With Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
            
            
            With Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThick
            End With
            
            
            Selection.Borders(xlInsideVertical).LineStyle = xlNone
            Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            
            Selection.Locked = True
            
            If currentArea = 99 Then
                errorFound = True
            Else
                currentArea = currentArea + 1
            End If
        Else
            'error found
            If currentArea = 1 Then
                'MsgBox "No areas for setting borders appear to have been made.", , "Border Warning"
            End If
            
            If currentArea = 99 Then
                'have reached the end - dont try anymore
            Else
                'try for thick edge one
                currentArea = 99
                errorFound = False
            End If
        End If
    Loop Until errorFound
    
    
    
    'sort out locking now
    areaName = "AreaUnlock"
        
    On Error Resume Next
    Set areaRange = Range(areaName)
        
    errorFound = Not (Err.Number = 0)
    Err.Clear
    On Error GoTo errSort
    
    If errorFound Then
        'MsgBox "Program note: No " & areaName & " - to define cells to unlock - defined", , "Program Consideration"
    Else
        ws.Cells(headRow + 1, areaRange.column).Resize(totalsRow - headRow - 1, areaRange.Columns.Count).Select
        Selection.Locked = False
    End If
    
    
    [B]'now autofit columns
    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
                On Error Resume Next
                fitCell.EntireColumn.AutoFit
                On Error GoTo errSort
            End If
            
            Set fitCell = Nothing
        Next
    End If
[/B]    
    
    If screenUpdating Then
        Application.screenUpdating = True
    End If
    
    originalSelection.Select
    
    If sheetLocked Then
        ws.Protect AllowFiltering:=True, AllowSorting:=True
    End If
        
    If eventsEnabled Then
        Application.EnableEvents = True
    End If
    
    Exit Sub
      
errSort:
    MsgBox "Error detected: Number " & Err.Number & " - " & Err.Description, , "Error Detection"
    
    
End Sub

 

[Norie]

Why are you disabling events?

You mention other code, do you mean event code?

eg SelectionChange, Change