+ Reply to Thread
Results 1 to 23 of 23

Hiding Columns with Macros in protected sheets with userinterfaceonly=True

  1. #1
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    I have a workbook used by many people. I am trying to limit their access to a few cells on the first sheet.

    There are macros triggered by worksheet_change events on that sheet. They "Advanced filter" rows, and hide columns with Custom Views. However, when I try to protect the sheet it breaks them (I protect them within the Workbook_Open macro). I was hoping that protecting with "userinterfaceonly=True" would solve the problem, but it appears I am wrong. If I unprotect they work again.

    Is my only option to turn off/back on protection within the macro? I haven't found a very good description of what exactly userinterfaceonly does & doesn't allow.

    Thanks for any help!

    K

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi keith

    Add this to your Code
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Thanks John! I think I'd just noticed that option myself. I'm a little confused by this comment in the Excel Object Model Reference Worksheet.Protect Method notes:

    If changes wanted to be made to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

    The implication of this curious wording, in my mind, is that INSTEAD of unprotecting, one can simply "protect" at the beginning of a macro, including the password, and it will act as if it's unprotected for the duration of that macro. One would not need to (re)protect at the end then... Am I thoroughly misinterpreting?

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi keith

    Don't know...got me...

  5. #5
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    If I get a chance, I'll play with it. Thanks for the help!

    Keith

  6. #6
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    John or anyone: this AllowFormattingColumns does not appear to allow Custom Views to hide/unhide columns. It seems like my only choice is to unprotect the sheet within the macro.

    Also: as for my comment above, I still don't understand what that statement in the Object Model Reference means, but in any case my suggestion about one possible weird interpretation didn't pan out: Protect doesn't work like Unprotect within a single macro, as far as I can tell.

  7. #7
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    390

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Is my only option to turn off/back on protection within the macro? I haven't found a very good description of what exactly userinterfaceonly does & doesn't allow.
    UserInterfaceOnly lets VBA change the contents and formatting of a protected sheet, but not the user.

    Without seeing your spreadsheet, I can't offer much insight into the issues you are having, but if it's any consolation I have resorted to turning on and off protection before when I couldn't get it to behave the way I thought it should...
    Last edited by mgs73; 01-06-2015 at 06:08 PM.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    You'll need to post your Workbook with an explanation of what you wish to do. I can't troubleshoot your issue without SEEING what it is you wish to do.

    MGS73 is correct in this
    UserInterfaceOnly lets VBA change the contents and formatting of a protected sheet, but not the user.

  9. #9
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Well, all kinds of things go on that I don't understand, so I'm attaching the sheet and if anyone has time...

    First, There's a drop-down list called "Edit Mode" that triggers a series of macros, and it basically runs custom views and advanced filters. That breaks if I try to just run "userinterfaceonly", so one of those two things doesn't play well with that approach. So then I go to turning protected on & off. But periodically, for no reason I can discern, my superhidden sheet shows up (becomes visible), even though I'm NEVER touching it's protection. So right now I have it set up to superhide that periodically, but there are some changes I need to make to all that.

    It seems like things are glitchy: I'd like to believe it's just me/my coding, but it seems like maybe Excel is a little inconsistent/glitchy around these things. It seems like the overall protection system is not comprehensive and robust... I'm still quite new to VBA, I'm happy to be able to do some of what I need to do and endlessly thankful for all the help people have given me, I don't meat to complain. Too much.

    Keith

    the pw is DCGCCS
    Attached Files Attached Files

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    I get this when I try to open your File and cannot open with Code enabled.
    Keith.jpg
    Last edited by jaslake; 01-07-2015 at 12:35 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    390

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    John or anyone: this AllowFormattingColumns does not appear to allow Custom Views to hide/unhide columns. It seems like my only choice is to unprotect the sheet within the macro.
    I've not used Custom Views before, but from what little I've learned about them today, I think they cannot be used on a Protected sheet, so yes I would recommend unprotecting and reprotecting the sheet at the top and bottom of the Worksheet_Change for that sheet.

    UserInterfaceOnly and all the other arguments apart from the password are probably not needed in this case. Also, there is no need to protect the sheets that are hidden.

    But periodically, for no reason I can discern, my superhidden sheet shows up (becomes visible), even though I'm NEVER touching it's protection.
    I didn't see this happen, so I'm afraid I can't offer any suggestions there...
    Last edited by mgs73; 01-07-2015 at 05:56 AM.

  12. #12
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Thanks for your thoughts mgs73. jaslake, I have the impression that allowing/not allowing macros is a local setup issue, and not anything I've done on the sheet, though I did self-sign it... I'm going to have to figure out more about that, since lots of different people will be using this... I can not easily get a certificate to officially sign this, and I can't figure out another decent way to approach this part.

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    I'll be happy to look at the File...you'll need to remove the Digital Signature for me to do so.

  14. #14
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi jaslake: is that true? I think removing the signature doesn't change your options at all? That is, one way or another you have to allow macros. In the absence of me having it signed by a certificate based on a root certificate of yours (and I can't get such a certificate), you either have to just accept running macros, or not. Do I still not understand that properly? I am trying to figure out if there's a way I can share my certificate and you can install it, but things aren't really set up to make that simple, as far as i can tell.

    Keith

  15. #15
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    What can I tell you Keith...my Security will not allow me to run Macros in your attached File.

    Now, I suppose I could revise my Security Settings, somehow, to allow your Code to run. I'm not inclined to do that, sorry.

    I'd assume your multiple Users will have the same issue, depending on their Security Setting.

  16. #16
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hey jaslake: sorry, I'm actually thankful you are pointing this out, I just didn't think signatures could do that. I'm attaching an unsigned version, why don't you let me know if that works for you. I have the impression that in order to run any Excel file with macros that is unsigned, you basically have to turn off security -- enable macros from unsigned files or whatever it's called. Why don't you let me know if this works for you. Have you been able to run other unsigned files without doing whatever it is you don't want to do? You are exactly right, this is actually very helpful to me, sorry if I sounded snippy.

    KeithAttachment 369006
    Attached Files Attached Files

  17. #17
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    Well, I can open the File and enable Macros. It's late, I'll look at it tomorrow and we'll see what tomorrow brings.

  18. #18
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    Alright, I'm looking at your File.

    Please explain the issue and what I need to do to re-create it. Also, are you getting an Error Message? If so, what is the Error Message?

  19. #19
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Jaslake! Well, this has already been educational, I didn't think signing ever made it more difficult to open a file, that's quite disconcerting.

    If you look at the macros, you'll see that I am turning protection on and off with macro operation. That's because, when I use userinterfaceonly (which is also still in there) the macros don't work. Now, I think I've already got my answer, in some sense: userinterfaceonly may work with hiding rows or columns, but it does NOT work with either/both Advanced Filters and/or Custom Views. And that leads me to doing it the way I'm doing it. Meanwhile, I discovered I can protect my macros separately from my sheets, so they can't be viewed/messed with, which probably makes like simpler, and I instituted a little switch to put things in & out of protected mode (more complicated by the fact that a superhidden sheet kept popping up as visible, so I reprotect/superhide it over and over: I have to turn that off to do edits).

    So right now the major thing you've already given me is this issue about signing, and I think I might have to start a new thread unless I get more information here: I thought a privately-signed document used on a computer without the certificate would behave precisely the same as an unsigned document (maybe some difference in the message, but no fundamental difference in bahavior/required actions), but you seem to be saying this is clearly not the case. Thanks for that!

    If you see/experience any other strange behavior from my sheet/macros I'd love to hear about it, but I guess I don't have anything to point you towards right now, since I think my original question was resolved, mostly in the "it can't be done" way.

  20. #20
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    Here's a couple of Links that describes the issue I have with your Signed File.

    http://www.excelforum.com/excel-gene...rtificate.html
    http://www.experts-exchange.com/Soft..._26178592.html

  21. #21
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    I've commented out all the Protect/Unprotect lines of Code (except those in the Workbook Open Code).

    I saved and closed the File.

    I reopened the File and made selections in your Dropdowns.

    All appears to run...what do YOU think should be happening? Do you think I should be getting Error Messages?

  22. #22
    Registered User
    Join Date
    04-01-2009
    Location
    WashDC
    MS-Off Ver
    Excel 2003
    Posts
    84

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi again, and thanks for the links. One requires a credit card, so I don't know what it says. I'll keep researching that end of it, this self-signed certificate problem is quite surprising to me.

    Anyway: when you pick from the "Edit Mode" drop-downs, you should see combinations of a changing assortment of hidden rows (by Advanced Filters) and hidden columns (by Custom Views). I think either or both of those break in userinterfaceonly mode.

    I'm in Excel 2010 (I have not tested this on 2013, at home yet): what are you working in?

    K

  23. #23
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,747

    Re: Hiding Columns with Macros in protected sheets with userinterfaceonly=True

    Hi Keith

    As previously described
    I've commented out all the Protect/Unprotect lines of Code (except those in the Workbook Open Code).
    I can open the Workbook in both Excel 2007 and 2010. The Code appears to work as expected but then, I don't know what to expect. I get no error message. What happens when YOU run the Code?

+ 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. [SOLVED] Macros in protected sheet hiding/unhiding rows & cols
    By keithwins in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2014, 07:06 PM
  2. Changing Locked property of cells in Protected sheet with option UserInterfaceOnly=True
    By MathUKTeacher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2014, 07:34 AM
  3. .protect userinterfaceonly:=true not working?
    By dreamz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2006, 07:31 PM
  4. Replies: 1
    Last Post: 09-19-2006, 02:02 AM
  5. [SOLVED] Hiding Macros-protected my macro coding
    By mrbalaje in forum Excel General
    Replies: 2
    Last Post: 04-18-2005, 04:07 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