Doesn't work and I can't work out why?

[SandyUK]

Old chestnut about ensuring macro's are enabled on open. I have a warning sheet that I would like displayed if macro's are disabled which says "please re open with Macro's enabled" My code is below but i get an error on the BeforeClose and i can't see why?

Any help much appreciated

Regards

Adrian


Private Sub Workbook_BeforeClose(Cancel As Boolean)


For Each Sh In Sheets
Sh.Visible = False

Next Sh

Sheets("Warning").Visible = True


End Sub

####################################

Private Sub Workbook_Open()


For Each Sh In Sheets
Sh.Visible = True
Next Sh

Sheets("Warning").Visible = False


End Sub

[Leith Ross]

Hello Adrian,

It seems like you want to pause the Workbook Close Event until the user has had time to read the "Warning". Using a Message Box, you can pause closing the Workbook and then resume when the user is ready.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 Dim Answer, Msg
  
  Cancel = True
  Msg = "Click OK when you are ready."
  Answer =  MsgBox(Msg, vbExclamation + vbOKOnly)
  Cancel = False

End Sub

Sincerely,
Leith Ross

[SandyUK]

What i am trying to do is reset the worksheets so that when the workbook is closed all the sheets other than the warning sheet are hidden, therefore if it is opened without enabling macor's all the user will see is the warning sheet.

Hope this makes it a little clearer and also that you know where i am going wrong?

Regards

Adrian

[Leith Ross]

Hello Adrian,

My apologies for having misunderstood code. Use your original code and Set Cancel True before your loop and Set Cancel False before Exit Sub. This will prevent the system from processing anything else related to closing the Workbook until you have finished hidiing the sheets.

Sincerely,
Leith Ross

[SandyUK]

Thanks for your help Lieth

I am still getting an error and when i went to look up cancel in help it bought up 2 topic titles but no information which is odd!

This is what i am trying

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Set Cancel = True
For Each Sh In Sheets
Sh.Visible = False

Next Sh

Sheets("Warning").Visible = True

Set Cancel = False

End Sub


Adrian