5

I am working on a project that wants all users to have macros enabled. It is a shared sheet so I cannot simply hide everything as a baseline and unhide the sheets when Open_Workbook is ran. My goal is to make it so only my designated "BLANK" sheet is shown when macros are disabled.

Using the 'Browser View' feature of the workbook, I've been able to make it only open to the "BLANK" sheet if on browser or teams, I want to do that here but with a check for enabled macros, or a default state that can be switched on with the enabling of macros.

A means of stopping someone from using the sheet entirely if they do not have macros enabled would also work. Any ideas would be appreciated.

Thomas Wohllaib
  • 55

1 Answers1

8

You can do this trick by setting the other sheets than "BLANK" to be "very hidden".

There are three "Visible" properties for a sheet :

  • xlSheetVisible (or TRUE) - the sheet is visible
  • xlSheetHidden (or FALSE) - the sheet is hidden
  • xlSheetVeryHidden - the sheet is very hidden

One can toggle using the Excel visual interface between the states of Visible and Hidden by using the Unhide or Hide commands. However, only a Visual Basic macro can set or unset the xlVeryHidden value, or it can also be manually done from the VBA Editor.

How to make a worksheet very hidden

The workbook must be stored in .xlsm format, and then do the following:

  • Press Alt+F11 or click in the the Developer tab the Visual Basic button to open the Visual Basic Editor. You will see the Project Explorer window in the top-left panel.
  • Press F4 or click View > Properties to open the Properties window just below (if not already there)
  • In the Project Explorer window, click to select the worksheet to make very hidden
  • In the Properties window, set the Visible property to "2 - xlSheetVeryHidden".

That's it : save and close the workbook. The hidden worksheets will now be totally and very hidden.

Showing very hidden worksheets

To unhide the very hidden worksheets will require a VBA macro, so that when macros are disabled they will stay hidden.

This macro will unhide all worksheets :

Sub UnhideVeryHiddenSheets()
Dim wks As Worksheet
For Each wks In Worksheets
  If wks.Visible = xlSheetVeryHidden Then
    wks.Visible = xlSheetVisible
Next
End Sub

The macro can also set the BLANK worksheet to hidden with a code-line like this:

Sheets("BLANK").Visible = False

To create a macro that will do that automatically when the workbook is opened, see the Microsoft article
Automatically run a macro when opening a workbook.

References :

harrymc
  • 498,455