+ Reply to Thread
Results 1 to 6 of 6

Set Sheets to very hidden when closing file

  1. #1
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Set Sheets to very hidden when closing file

    I have a workbook that I've put alot of effort into and would like to protect the best I can. I have protected the sheets with a password but I would also like to go a little further.

    My plan is the following.

    Create a new sheet called "Enable Macros"
    This sheet will have a Simple message that Macros must be enable to use the workbook
    Set all other sheets to very hidden.

    What I am looking for is a code to do the following

    When workbook is opened Hide sheet named "Enable Macros"
    Set all other sheets to visible
    When workbook is closed (and saved) set all sheets to veryhidden and unhide sheet named "enable Macros"

    my though behind this is that if someone saves the sheet as a basic excel file when they open it they will only see the sheet "Enable Macros" with test telling them to enable the macros. And, as the workbook hides the other sheets when closing the file they cant just open it once and then save it without macros.

    Can anyone help or even advise on another/better method. I hope i've explained this well enough.

    Thanks in advance

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Set Sheets to very hidden when closing file

    If the workbook contains macros you can not save it as a 'basic workbook' (.xlsx) - if macros are present you will be prompted - if you select .xlsx the macro will not be saved.
    Torachan,

    Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Set Sheets to very hidden when closing file

    It depends on the skills of the people you are protecting it from. Excel is not the most secure environment

    https://www.excelmadeeasy.com/excel-...macro-code.php

    might be worth considering as well

    If you insist on saving with the sheets hidden except enable macros, why would you then change it when the workbook is open (do you mean button clicked)?

  4. #4
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Re: Set Sheets to very hidden when closing file

    Thanks for the reply.

    The idea is that, if the workbook is opened without macros enabled the other sheets will be veryhidden. Its the macro that unhides the sheets - No macros, sheet repair hidden.
    When saving (if macros are enabled) the sheets would go to very hidden. This way if they were to open it the 1st time with macros and then try and save without macros the sheets would hide

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Set Sheets to very hidden when closing file

    thats what i thought but you said

    When workbook is opened Hide sheet named "Enable Macros"
    Set all other sheets to visible

    you could just do nothing on opening as this is the position on closing anyway

  6. #6
    Forum Contributor
    Join Date
    09-25-2018
    Location
    Grimsby, UK
    MS-Off Ver
    365
    Posts
    148

    Re: Set Sheets to very hidden when closing file

    Sorry,

    When workbook is opened "Enable Macros" Sheet hides & All other sheets are visible
    When Workbook Closes (when saved) Enable Macros Sheet Is visible & all other sheets are veryhidden

    Therefore, if opened without macros enabled the Enable macros sheet will be visible and all other will be very hidden.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. clean method for closing excel file without leaving a shell or closing other excel files
    By QuantumSquirrel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2020, 08:20 AM
  2. VBA Project not closing after closing Excel file
    By GOGGS in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-29-2016, 10:26 AM
  3. [SOLVED] Closing WB with a timer no changing sheets to very hidden
    By Nitro2481 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-03-2016, 09:47 AM
  4. [SOLVED] Closing a workbook with the menu hidden
    By grumpyguppy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-08-2014, 06:41 AM
  5. How to have open a hidden sheet / hide on closing
    By danieluk9 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-10-2014, 01:14 PM
  6. [SOLVED] Hide specific sheets on closing file
    By Andy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2006, 08:30 AM
  7. Range all sheets to cell "A1" before closing file
    By brunces in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-05-2005, 03:33 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1