+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 28

Protect - Unprotect Macro

  1. #1
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Protect - Unprotect Macro

    All,

    Attached is a macro I've been using for years, and would like to add:
    1. Ability to use the 'data filters'
    2. use the 'grouping' commands.

    On various web pages, I've found syntax to perform additional steps 1 & 2. However, I've not been able to successfully compile them together.

    Any help would be appreciated.

    Protect-Unprotect.xlsm
    Best Regards,

    Jason Hampton
    Freedom Isn't Free

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    Would you clarify what you mean please.

    The filter drop down and grouping functionality are in place and work.

    Arev you wanting to automate the appliation of some other grouping and to toggle the Autofilter?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    Richard,

    They are working, but the workbook is unprotected. Click the button, and then input any password to verify. My apologies.

  4. #4
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    Richard,

    Did you have any further questions?

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    I'm still not clear what you're asking for.

    Are you saying that with protection on you can't use the filter?

    If so that's because when you set the password protection you need to tick the option that allows the filter to work

    i.e. in VBA

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    Yes sir that is correct. When I protect the document, I can not 1. filter (or) 2. use grouping.

    I'd like to add this to the aforementioned VBA syntax; allowing filtering and grouping after protection.

    Thanks in advance.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    OK,
    There are two aspects here.
    1. Filtering has to be enabled when the sheet password is set.
    2. Using the Grouping functionality is not allowed when the worksheet is protected.

    To overcome #1 you should add that line I gave you last time to the CommandButton1 macro. i.e.
    Please Login or Register  to view this content.
    To overcome #2 you need to use some code in the Workbook Open event. i.e.

    Please Login or Register  to view this content.
    When a sheet is protected with UserInterfaceOnly in operation, the protection applies to stuff like keyboard clicks made by the user. VBA however is permitted to modify the worksheet as if there is no protection.
    The UserInterfaceOnly setting isn't saved when you close the workbook, so you need to set it when the workbook is opened. The best place to do this is in the Workbook_Open event procedure.

    Hope thsi helps.

  8. #8
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    Richard,

    Thank you for your assistance. I've added the syntax to the CommandButton1. However when I run the macro it is now showing an error on the first line of Sub ShowPass() [indicated underline below]

    Please Login or Register  to view this content.
    Last edited by jason.hampton; 09-12-2019 at 09:24 AM.

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    Re your PM to me.

    Please upload the workbook in the forum so that I may check.

  10. #10
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,404

    Re: Protect - Unprotect Macro

    Your example file only has one sheet, not 2.
    Please Login or Register  to view this content.

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    Quote Originally Posted by Kenneth Hobson View Post
    Your example file only has one sheet, not 2.
    Please Login or Register  to view this content.
    Hi Kenneth

    IIRC there was a second hidden sheet.

  12. #12
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    Richard,

    As requested, attached is my actual workbook I am having difficulties with..
    Last edited by jason.hampton; 09-13-2019 at 11:11 AM.

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    What's the password?

  14. #14
    Forum Contributor
    Join Date
    04-20-2012
    Location
    Republic of Texas
    MS-Off Ver
    2007, 2010, 2016
    Posts
    111

    Re: Protect - Unprotect Macro

    I pm'd the password to you.

  15. #15
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,690

    Re: Protect - Unprotect Macro

    Kenneth was correct.

    Although your previous workbook had two sheets - which prompted my response to Kenneth, this latest one only has one sheet.

    Hence your code
    Please Login or Register  to view this content.
    which is looking for the second sheet tab fails.

    You should avoide using code like Sheets(2) or Sheets("TimeLine").
    The order of sheet tab names can easily be changed so that the sheet which VBA knows as Sheets(1) for instance will become Sheets(3) if it's tab name is moved to be the third sheet. Similarly sheet tab names are too easily changed so if you hard code these things you are creating a potential problem

    Always use the VBA Sheet Code name. In this case Sheet1. This stays consistent whatever the user does in the Excel App.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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