Solved: Force users to enable macros in a workbook

[jiddings]

I'm using the code from the page "Force users to enable macros in a workbook". I am aware that the page states that "If you reopen the workbook with macros enabled, you should see your warning screen for a second, then you should see all of your other sheets and the warning screen will dissappear."
I have been trying to eliminate the "warning screen for a second" without success.

Is it not possible to eliminate the "warning screen for a second"??

I have had some user comments about it as a bit disconcerting and can't figure it out.
Jack

[rbrhodes]

Hi Jack,

Either post a link to the "page" or post the code - I can't find it!

[zoom38]

http://vbaexpress.com/kb/getarticle.php?kb_id=578

[mikerickson]

Removing the test in the UnHide routine would speed things up and activating a sheet other than Prompt might show it sooner.

[VBA]Private Sub UnhideSheets()
'
Dim Sheet As Object
'
For Each Sheet In Sheets
'If Not Sheet.Name = "Prompt" Then
Sheet.Visible = xlSheetVisible
'End If
Sheets(1).Activate
Next
'
Sheets("Prompt").Visible = xlSheetVeryHidden
'
Application.Goto Worksheets(1).[A1], True '< Optional
'
Set Sheet = Nothing
ActiveWorkbook.Saved = True

End Sub [/VBA]

[jiddings]

The code is in vbaexpress.com/kb/getarticle.php?kb_id=379
Code Submitted by: Ken Puls
I tried to post link on original posting. However, as this was my first post the system won't allow links until post count is greater than 5.
Thanks for the other two postings, but as you see from the above, I am using different code.
Thanks,
Jack

[mikerickson]

Other than the names, your ShowAllSheets is the same as UnhideSheets from the other link.
The same modification is applicable.

[jiddings]

I followed mirkerickson's suggestion and it hasn't helped. Here's the code I'm using and I still get, as mentioned in my first post, the "warning screen for a second". Any other adjustments to the code to eliminate this unwanted flash of the warning sceen?
Jack

[vba]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[/vba]

[mikerickson]

Activating a sheet other than the warning sheet will give them something different to look at.

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

In the code, is WelcomePage a public string, declared elsewhere?

[jiddings]

I followed mirkerickson's above suggestion and received a Compile error: Invalid or unqualified reference referring to "Parent" in this line: .Parent.Worksheets(1).Activate 'Rem NEW line
Yes it's declared Const WelcomePage = "Macros"
I pasted in the code. Should I be changing something specific to the file?

[johnske]

I'm using the code from the page "Force users to enable macros in a workbook". I am aware that the page states that "If you reopen the workbook with macros enabled, you should see your warning screen for a second, then you should see all of your other sheets and the warning screen will dissappear."
I have been trying to eliminate the "warning screen for a second" without success.

Is it not possible to eliminate the "warning screen for a second"??

I have had some user comments about it as a bit disconcerting and can't figure it out.
Jack

no, it's not possible. When you close the workbook all sheets are hidden and the warning screen is unhidden, and that is the situation when the workbook is reopened. No matter what code you put in to try to overcome what you say is the problem, when you open the workbook it takes a short time for any macros to kick in and during that time you will see the warning screen - very very fast computer might solve it .

[mikerickson]

That should have been

ws.Parent.Worksheets(1).Activate

[jiddings]

no, it's not possible. When you close the workbook all sheets are hidden and the warning screen is unhidden, and that is the situation when the workbook is reopened. No matter what code you put in to try to overcome what you say is the problem, when you open the workbook it takes a short time for any macros to kick in and during that time you will see the warning screen - very very fast computer might solve it .

Thanks everyone for the feedback. I was beginning to believe it was not possible and johnske confirmed it. The users I work with do not have very very fast computers, so it's will something that they will have to bear through. In time, it will probably less noticeable for them.
I'll mark the thread as resolved / solved.
Jack