Excel Workbook_SheetActivate problem

[asrisk]

Hello All.

I'm having an odd problem with SheetActivate which is (sort of) not firing for a specific worksheet, and wondered if anyone could assist.

My code is:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Debug.Print "activating sheet: " & Sh.Name

If Sh.Name = "Timesheet" Then
    Debug.Print "turn off scrollbars, enable fullscreen"
    ActiveWindow.DisplayHorizontalScrollBar = False
    ActiveWindow.DisplayVerticalScrollBar = False
    Application.DisplayFullScreen = True
Else
    Debug.Print "turn on scrollbars, disable fullscreen"
    ActiveWindow.DisplayHorizontalScrollBar = True
    ActiveWindow.DisplayVerticalScrollBar = True
    Application.DisplayFullScreen = False
End If
'MsgBox "Welcome to " & Sh.Name

End Sub

The aim is to go fullscreen and remove scrollbars for one sheet, and revert to normal display for all others.

The first odd thing is that the code does not work when Timesheet is activated. The expression [bold]sh.Name = "Timesheet"[/bold] evaluates correctly to TRUE, and the debug.print line works, but the scrollbars are not removed, and the fullscreen does not activate. Oddly, if I place a breakpoint on any of those four lines, execution does not break.

The second odd thing occurs if I remove the comment marker before the MsgBox line. Selecting any sheet except timesheet, the Msgbox appears *after* the selected sheet has been displayed. Selecting Timesheet, the Msgbox appears *before* the worksheet is displayed.

The Timesheet sheet contains some complex formulae, including some dynamic ranges, charts and linked pictures, and events trapping RightClick, DoubleClick and SelectionChange but I can't see how this would affect sheet activation. None of those events are running on sheet activation. For completeness, there is no other code running in Workbook_SheetDeactivate, or the individual sheets Activate and Deactivate events.

If anyone can cast light on this mysterious oddity, I would be very grateful!

Cheers in advance,
Andy.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]

 

[Loomah]

hi
i don't know if this has anything to do with your complex formulas but i can't recreate the problem.

just as a thought, what happens if you split the routine into the activate and deactivate events of the single sheet?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[asrisk]

Hi loomah, thanks for the reply.

As it turns out, I've now fixed the problem. I had a conditional format based on a volatile User-defined Function which was running when the sheet activated. This was obviously confusing Excel. Oh well, another nice idea out the window!

Thanks for your suggestions.

Andy.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]

 

[CBasicAsslember]

It would be advisable to have the following in the event routine:

Application.EnableEvents = False
<your code>
Application.EnableEvents = True

 

[asrisk]

Thanks CBasicAsslember, I wasn't aware of that property. Looks like it could be handy.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]