Unwanted "Flashing" of screen

[jack]

I'm using the following code to hide the "WelcomePage" (dim'ed as a const).
However, I'm unable to remove the momentary "flash" of the "WelcomePage"
before it is hidden.
Using Application.ScreenUpdating doesn't seem to help.
Any suggested changes to the code to remove the unwanted "flash" of that
sheet?
Thanks,
Jack

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
Application.ScreenUpdating = False ' added
For Each ws In ThisWorkbook.Worksheets
'If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
Application.ScreenUpdating = True ' added
End Sub

 

[jack]

I am hesitant to open files with macros posted to the user group.

When closing the workbook all sheets are hidden and the warning screen is
unhidden, and that is the situation when the workbook is reopened.
The following is the the entire code from ThisWorkbook which includes the
Sub ShowAllSheets()

Does this help explain the situation that I am experiencing?
Jack


Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Evaluate if workbook is saved and emulate default propmts
With ThisWorkbook
If Not .Saved Then
Select Case MsgBox("Do you want to save the changes you made to
'" & .Name & "'?", _
vbYesNoCancel + vbExclamation)
Case Is = vbYes
'Call customized save routine
Call CustomSave
Case Is = vbNo
'Do not save
Case Is = vbCancel
'Set up procedure to cancel close
Cancel = True
End Select
End If

'If Cancel was clicked, turn events back on and cancel close,
'otherwise close the workbook without saving further changes
If Not Cancel = True Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub CustomSave(Optional SaveAs As Boolean)
Dim ws As Worksheet, aWs As Worksheet, newFname As String
'Turn off screen flashing
Application.ScreenUpdating = False

'Record active worksheet
Set aWs = ActiveSheet

'Hide all sheets
Call HideAllSheets

'Save workbook directly or prompt for saveas filename
If SaveAs = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If Not newFname = "False" Then ThisWorkbook.SaveAs newFname
Else
ThisWorkbook.Save
End If

'Restore file to where user was
Call ShowAllSheets
aWs.Activate

'Restore screen updates
Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
'Hide all worksheets except the macro welcome page
Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page
Dim ws As Worksheet
Application.ScreenUpdating = False ' added
For Each ws In ThisWorkbook.Worksheets
'If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
ws.Visible = xlSheetVisible
Next ws
Worksheets(WelcomePage).Visible = xlSheetVeryHidden
Application.ScreenUpdating = True ' added
End Sub

 

[Jon Peltier]

I've used a similar approach to make sure users activate macros, and I think
the warning page generally appears briefly no matter how I try to tweak the
code.

In any case, Jim's code in a regular module (Module1) is:

'------------------------------
Option Explicit

Sub ShowAllExceptWelcome()
Dim WS As Worksheet
Application.ScreenUpdating = False
For Each WS In Worksheets
WS.Visible = True
Next
Welcome.Visible = xlSheetVeryHidden
End Sub

Sub Reset()
Dim WS As Worksheet
Application.ScreenUpdating = False
Welcome.Visible = xlSheetVisible
For Each WS In Worksheets
If WS.Name <> Welcome.Name Then WS.Visible = False
Next
End Sub

'------------------------------

I added the following code to the ThisWorkbook module:

'------------------------------
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Reset
Me.Save
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.ScreenUpdating = False
Application.EnableEvents = False
Reset
Me.Save
ShowAllExceptWelcome
Cancel = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
ShowAllExceptWelcome
End Sub
'------------------------------

You see the Welcome sheet (both the sheet name and the sheet codename are
"Welcome") for an instant as the file is being closed and again when the
file is reopened.

- Jon

 

[jack]

Jon,
Thanks for the feed back and the additional code. I was rapidly concluding
that there wasn't a way to eliminate the brief appearance of the warning
page. One more item for my learning curve.
Thanks again
Jack