+ Reply to Thread
Results 1 to 8 of 8

Format all cells in all sheets to Protection Hidden on visible and hidden tabs

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Hello,

    I am a novice at VBA programming, I have this VBA that I have build through some trial and error and via some web searching. I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book. I don't want it to actually move anyone anywhere and I feel there is probably a more efficient way for this to run.

    Thanks!

    Please Login or Register  to view this content.
    Last edited by alansidman; 02-28-2014 at 12:19 PM. Reason: code tags added

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Code Tags Added
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between
    Please Login or Register  to view this content.
    tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Thank you for doing that, I knew there was a way to insert into the window, but I couldn't figure it out. Still getting used to how the form works.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Add this line to the code before End Sub

    Please Login or Register  to view this content.
    If Sheet1 is not the name of your first sheet or the sheet you want to land on, then change it.

  5. #5
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Thanks for the reply, I would do this, but the sheet name where the macro button will be located will change depending on the book that it is in. This will need to apply to any book it is placed into.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    Okay. Understand. Give this a try.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-04-2013
    Location
    Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    That works great thanks a bunch!

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Format all cells in all sheets to Protection Hidden on visible and hidden tabs

    You are welcome. Glad to have helped.

+ 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. Replies: 4
    Last Post: 01-06-2014, 02:14 AM
  2. [SOLVED] trying to access the excel sheets/Tabs in the hidden/very hidden mode through hyperlinks
    By Kiran Kurapati in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2013, 06:50 AM
  3. Making sheets visible or hidden based on value of a range of cells.
    By alzhander in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2012, 09:12 AM
  4. Protection and hidden sheets
    By daddioja in forum Excel General
    Replies: 1
    Last Post: 02-15-2007, 10:03 PM
  5. [SOLVED] Making sheets visible / hidden
    By Xlyr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2005, 04:06 PM

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