Forcing users to enable macros

  • Thread starter Thread starter universal
  • Start date Start date
U

universal

Hello all,

To acheive this Ive employed the "hidden (useful) worksheet becomes
visible if macros are enabled" method.

A second sheet is visible, that tells users that they must enable
macros on loading the file. Should they already have macros enabled,
the first thing that is run is to hide this sheet.

Unfortunately, when loading the excel file, it shows the other sheet
for a split second. Is there any way to stop this happening?

Code here:

Private Sub Workbook_Open()

Application.ScreenUpdating = False

Call enablemacros

Call openformat

Application.ScreenUpdating = True

etc.........


Then in a module:

Sub enablemacros()

Worksheets("UsefulWorksheet").Visible = True
Worksheets("RemindToEnableMacros").Visible = False

End Sub



Many thanks,
Ed
 
Ed,

Just a reminder.
Are you insuring that you hide the "Useful" sheet(s) in the Before_Save
Event.

As for your question....
When you open the file, that "RemindToEnable" sheet is going to be the first
thing
that appears on the screen. Normally, it would cycle fast enough that you
should
hardly see it at all. The problem is that your Application.ScreenUpdating is
freezing the display until your code finishes (leaving that sheet visible
until your
code completes).
Try it without the Application.ScreenUpdating = False

John
 
Thanks for the reply John,

And thanks for the reminder, I am rehiding the sheet on Close.

Ive tried without the ScreenUpdating now. Unfortunately it doesnt see
to make too much difference. There is still a little delay. It i
perhaps my own fault though since the file is now over 1Mb due to th
amount of code.

Im thinking perhaps if no-one can think of a way round the problem,
might try to make the best of a bad situation and have the front pag
appear that it is in fact loading macros. It could say "Please wait
macros being enabled" and pause for a second then run the hidin
macro.

Any other ideas
 
Ed,
Any other ideas?
Click to expand...
Sure......

Create a UserForm to use as a Splash Screen.
Make it cover whatever you need to on that "reminder" sheet.
Call your code from within the UserForm Activate Event.
e.g.

'In the workbook module
Private Sub Workbook_Open()
UserForm1.Show
End Sub

' In the UserForm1 module
Private Sub UserForm_Activate()
DoEvents
enablemacros
openformat
Unload UserForm1
End Sub

When the user opens the file, the UserForm should come
right up and stay there until all your code completes.

John
 
Again, many thanks.

The userform is shown over the top of the "ReminderWorksheet", thoug
the reminder is still visible for a split second underneath, before th
userform loads.

Ummm..........
 
Ed,

Again.....if when you save the workbook, the only visible sheet
is your "reminder" sheet, that sheet will appear on the screen
whenever the workbook is opened.
When your Worksheet_Open code runs, it'll hide that sheet
but until it does so, it'll still be visible.
What I was suggesting was using the UserForm as a splash screen
to physically hide whatever "reminder" dialog that you have on that sheet
until your code (embedded in that UserForm), actually does hide the sheet.

Another option that you might want to experiment with is
Application.Visible.
Setting it to false will hide the entire workbook (not the UserForm). You
can set it back to true just before you close the UserForm.

John
 
Ed,

Adding to what I just wrote......
With macros enabled, the UserForm should "cover" the "Reminder" sheet.
If macros aren't enabled, the UserForm won't show and they'll be left
with nothing but your "reminder" sheet visible.

John

John Wilson said:
Ed,

Again.....if when you save the workbook, the only visible sheet
is your "reminder" sheet, that sheet will appear on the screen
whenever the workbook is opened.
When your Worksheet_Open code runs, it'll hide that sheet
but until it does so, it'll still be visible.
What I was suggesting was using the UserForm as a splash screen
to physically hide whatever "reminder" dialog that you have on that sheet
until your code (embedded in that UserForm), actually does hide the sheet.

Another option that you might want to experiment with is
Application.Visible.
Setting it to false will hide the entire workbook (not the UserForm). You
can set it back to true just before you close the UserForm.

John
Click to expand...
 
Ive now tried application.visible = false as the first command in
workbook_open. Still the reminder flashes up for (allbeit) probably a
tenth of a second.

Is there any way I can add a delay in somewhere to give Excel chance to
run more lines before displaying anything at all?
 
Ed,

As the workbook is saved with that one worksheet visible, I really
don't think that there's a way to not have it display before your
code starts running.

John
 
Back
Top