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

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?

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

Keith

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.

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

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.

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

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

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.

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