+ Reply to Thread
Results 1 to 7 of 7

Hide/Unhide rows using If funtion on protected sheet

  1. #1
    Forum Contributor
    Join Date
    08-10-2017
    Location
    Lansing, Michigan
    MS-Off Ver
    2016
    Posts
    140

    Hide/Unhide rows using If funtion on protected sheet

    I have a project with Yes/No drop down boxes and depending on Yes, No, or clear, rows are hidden/unhidden using the If funtion. This has not worked correctly when the sheet has been protected, but unprotected it used to work. I've obtained help with other needs to this project, and now this doesnt work correctly unprotected either along with it still not working protected. Not to mention, depending on the situation, it's giving me errors.

    My goal on this project is to cycle through Yes, No, and clear on all of the drop-down boxes and have the rows hidden/unhidden appropriately while the sheet is protected.

    I currently only have the If funtion set to process the first few rows until this is resolved.

    Attached is my Workbook (pw:123456) if someone has some time to check it out and assist. Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Hide/Unhide rows using If funtion on protected sheet

    Your worksheet is apparently set manually to protect, so right click the name tab on the sheet then click 'Unprotect'. Now repeat the tab click and click 'Potect" in the pop up menu. Click the checkboxes for Format Rows and Format Columns in the Allow Users... block. Click OK, then try your macro.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Contributor
    Join Date
    08-10-2017
    Location
    Lansing, Michigan
    MS-Off Ver
    2016
    Posts
    140

    Re: Hide/Unhide rows using If funtion on protected sheet

    Sorry, I guess I should have been specific. This doesnt work properly whether it's protected or not. Different outcomes happen depending on if it's protected or not.

    When the sheet is unprotected: It doesnt work properly when cycling through Yes, No, and clear. In this instance, rows 53-55 are hidden to begin with (clear). If "Yes" is selected from the drop down box, rows 53-55 are unhidden otherwise "No" they stay hidden. If the user then wants to change it from "Yes" to "No" , rows 53-55 stay unhidden instead of hiding like I need them to. If the user changes it from "Yes" to the cell being clear, this does work properly and rows 53-55 become hidden.

    When the sheet is protected with allowing users to edit rows: Regardless of when the user selects "No" the rows stay unhidden. If the user selects "Yes" or clears out the cell, I get a runtime error 1004.

    All of this regardless of protected or unprotected worked properly before these were added:

    Please Login or Register  to view this content.
    And

    Please Login or Register  to view this content.

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Hide/Unhide rows using If funtion on protected sheet

    You have a Worksheet_Change macro in your Sheet1 code module that is conflicting with your Workbook_SheetChange code. You need to combine the two into a single event procedure to eliminate the problem.

  5. #5
    Forum Contributor
    Join Date
    08-10-2017
    Location
    Lansing, Michigan
    MS-Off Ver
    2016
    Posts
    140

    Re: Hide/Unhide rows using If funtion on protected sheet

    It appears that this was two different issues. Combining them into a single event procedure did eliminate that problem(thank you), but only for when it's unprotected. I believe allowing users to format the rows option is not working as I am still getting the run time error 1004.

  6. #6
    Forum Contributor
    Join Date
    08-10-2017
    Location
    Lansing, Michigan
    MS-Off Ver
    2016
    Posts
    140

    Re: Hide/Unhide rows using If funtion on protected sheet

    I've removed the protect/unprotect codes to resolve this. They're not as important.

    Thank you so much for all of your help!

  7. #7
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Hide/Unhide rows using If funtion on protected sheet

    You're welcome,
    Regards, JLG

+ 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. Hide/Unhide cells on protected sheet
    By snuffnchess in forum Excel General
    Replies: 1
    Last Post: 10-17-2015, 03:54 PM
  2. Enabling hide/unhide rows in protected sheet
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2014, 05:16 AM
  3. [SOLVED] allow hide/unhide rows&columns when sheet protected (macro)
    By jw01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-15-2013, 03:18 AM
  4. complicated hide/unhide rows on protected sheet
    By achohan in forum Excel General
    Replies: 15
    Last Post: 06-21-2012, 02:27 PM
  5. [SOLVED] VBA Hide/Unhide Rows when Sheet protected
    By Kinez101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2012, 01:34 PM
  6. Replies: 1
    Last Post: 08-19-2011, 10:25 AM
  7. Hide & Unhide columns in Protected sheet
    By Anirudh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2008, 06:46 AM

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