PDA

View Full Version : Solved: Force users to enable macros in a workbook



jiddings
02-12-2008, 06:57 PM
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. :banghead:
Jack

rbrhodes
02-12-2008, 09:43 PM
Hi Jack,

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

zoom38
02-12-2008, 10:14 PM
http://vbaexpress.com/kb/getarticle.php?kb_id=578

mikerickson
02-12-2008, 10:53 PM
Removing the test in the UnHide routine would speed things up and activating a sheet other than Prompt might show it sooner.

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

jiddings
02-13-2008, 09:46 AM
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
02-13-2008, 05:43 PM
Other than the names, your ShowAllSheets is the same as UnhideSheets from the other link.
The same modification is applicable.

jiddings
02-13-2008, 07:55 PM
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

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

mikerickson
02-13-2008, 09:08 PM
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
02-13-2008, 11:03 PM
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
02-14-2008, 05:37 AM
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. :banghead:
Jackno, 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 :devil2: .

mikerickson
02-14-2008, 06:50 AM
That should have been

ws.Parent.Worksheets(1).Activate

jiddings
02-15-2008, 08:05 AM
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 :devil2: .

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:yay