Workbook_SheetActivate only works on some sheets

[Joshua Fandango]

Hi guys,

The Workbook_SheetActivate event is only triggering on some sheets in
a workbook - nowhere in the project are events disable so has anyone
an idea what could be preventing it working on all sheets?

I've had a good search aroung G Groups etc. but can't find anything
similar.

The 2 sheets out of 5 that it doesn't trigger on are different from
the others as they are protected and have hidden rows & columns but
unprotecting/unhiding has made no difference.

Any thoughts greatfully received.

Merry Christmas,
JF

 

[Per Jessen]

Hi JF

Post the code, as ít might be something in the code...

Regards,
Per

 

[Joshua Fandango]

Hi Per,

I've just tested & I can't trigger anything in the Worksheet_Activate
event of the 2 worksheets either - any others are fine.
There are currently no events in any sheet modules.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Prevent paste of incorrect NHS Number to Activity sheet
Dim GetData As New DataObject
With Application
If .CutCopyMode = 1 Then
GetData.GetFromClipboard
If ActiveSheet.Name = "Activity" And ChkNHSNum2(Left
(GetData.GetText, 10)) <> "Valid NHS Number" Then
'For some reason 10 character string = len 12 - maybe 2k for
the clipboard?
.CutCopyMode = 0
End If
End If
End With
End Sub

Cheers,
JF

 

[Joshua Fandango]

A further update...

I put an MsgBox at the start of the Workbook_SheetActivate sub and it
does show when any sheet is activated, but if I add a breakpoint to
this line (or any other) the MsgBox shows and the code doesn't
interrupt on the same 2 sheets - but breaks as expected on any other
sheet.

 

[Per Jessen]

I think it's because the 2 sheets are protected. To verify that remove
the protection for test purpose and see what happens.

Regards,
Per

 

[Joshua Fandango]

Hi Per,

Already tried that as in original post.

I have got an alternative working in the Workbook_SheetSelectionChange
event, it's not quite what I'm after but it might have to do.

The whole purpose of this is to copy a value from one sheet, and if
the format of the copied value meets my requirement allow the value to
be pasted, otherwise clear the clipboard. A paste event would be nice,
but it's beyond my comprehension if it is even possible.