| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_SheetActivate only works on some sheets
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_SheetActivate only works on some sheets
Hi JF
Post the code, as ít might be something in the code... Regards, Per On 24 Dec., 11:51, Joshua Fandango wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_SheetActivate only works on some sheets
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 On 24 Dec, 11:02, Per Jessen wrote: Hi JF Post the code, as ít might be something in the code... Regards, Per On 24 Dec., 11:51, Joshua Fandango wrote: 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- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_SheetActivate only works on some sheets
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. On 24 Dec, 11:11, Joshua Fandango wrote: 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 On 24 Dec, 11:02, Per Jessen wrote: Hi JF Post the code, as ít might be something in the code... Regards, Per On 24 Dec., 11:51, Joshua Fandango wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_SheetActivate only works on some sheets
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 On 24 Dec., 12:23, Joshua Fandango wrote: 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. On 24 Dec, 11:11, Joshua Fandango wrote: 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 On 24 Dec, 11:02, Per Jessen wrote: Hi JF Post the code, as ít might be something in the code... Regards, Per On 24 Dec., 11:51, Joshua Fandango wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Workbook_SheetActivate only works on some sheets
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. On 24 Dec, 11:31, Per Jessen wrote: 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 On 24 Dec., 12:23, Joshua Fandango wrote: 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. On 24 Dec, 11:11, Joshua Fandango wrote: 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 On 24 Dec, 11:02, Per Jessen wrote: Hi JF Post the code, as ít might be something in the code... Regards, Per On 24 Dec., 11:51, Joshua Fandango wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| function works on some sheets but not others | Excel Worksheet Functions | |||
| Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up | Excel Programming | |||
| Workbook_SheetActivate Paste Problem | Excel Programming | |||
| Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
| Hiding the sheets my macro works with... | Excel Programming | |||
Linear Mode
