how to hide a sheet unless Macro is enabled and then show the sheet

I have an excel file that i want to share with my colleagues.

  and it should only show a dummysheet with a text showing " Enable Macros to view the Master Sheet"  Once the user enables macros.  this dummy sheet should be changed to veryhidden and the MasterSheet should become visible which is currently is with veryhidden status.

also the user should get a messagebox that "thank you" Macro is enabled"

any help is appreciated.
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try in the Workbook module
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("dummy").Visible = xlSheetVisible
Sheets("Master sheet").Visible = xlSheetVeryHidden

End Sub

Private Sub Workbook_Open()
Sheets("Master sheet").Visible = xlSheetVisible
Sheets("dummy").Visible = xlSheetVeryHidden

MsgBox "Thank you: Macro is enabled"
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
Just use the workbook open event:
Option Explicit

Private Sub Workbook_Open()
    Dim wb As Excel.Workbook
    
    Set wb = ThisWorkbook
    wb.Worksheets("master").Visible = xlSheetVisible
    wb.Worksheets("dummy").Visible = xlSheetVeryHidden
    Set wb = Nothing
    msgbox "thank you, Macro is enabled"
End Sub

Open in new window

Notes:
We can't detect if macro are enabled or not, for the simple reason that if disabled, no code will run.
0
FloraAuthor Commented:
Thank you very much
0
Fabrice LambertFabrice LambertCommented:
Allow me to add the following:

Never ever use objects such as ActiveWorkbook, ActiveSheet, ActiveChart, ActiveCell, Selection, because they are user dependant, and by nature, are chaotic and unpredictable.
As a developper, you don't want to use chaotic objects.
Same goes for objects such as WorkSheets (alone, it is the same as ActiveWorkbook.Worksheets), Range (alone, it is the same as ActiveWorkbook.ActiveWorksheet.Range).

Always explicitly and precisely set your references to the object you are working on, else a simple click from the other can mess up your code's execution.
0
FloraAuthor Commented:
thank you Fabrice
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.