• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 26
  • Last Modified:

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.
0
Flora
Asked:
Flora
  • 2
  • 2
2 Solutions
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now