Why did nobody tell me??????????????????????????

[nigelandrewfoster]

Because I never asked.... Where is the most appropriate place to post "discoveries" on this forum? I have been programming in Excel VBA for five years now, and have only just discovered the UserInterfaceOnly argument of the sheet Protect method. Oh boy! I would still have a full head of hair now if I'd known that years ago!!

 

[Jon von der Heyden]

Good one. When writing VBA you should always explore the arguments available. Use your object browser and help files.

I wouldn't bother posting this as a discovery. I'm glad that you discovered this, but it is well documented over at MSDN and on various other websites, and has been mentioned many times at this forum already!

But well done!

 

[RoryA]

Also be aware it doesn't work all the time.

 

[nigelandrewfoster]

Hi Rory.
Can you elaborate? Are there specific circumstances where it doesn't?

 

[Jon von der Heyden]

Not sure if there are other restrictions that Rory is aware of, but I know that it isn't saved when you close the workbook. See here.

 

[nigelandrewfoster]

Thanks, Jon. I intend to use it in a Workbook_Open procedure anyway. Any others anyone knows about? Useful to know before I engage in a wholesale deletion of my ten million .Protect / .Unprotect commands!

 

[Peter_SSs]

Thanks, Jon. I intend to use it in a Workbook_Open procedure anyway. Any others anyone knows about? Useful to know before I engage in a wholesale deletion of my ten million .Protect / .Unprotect commands!

If you already have the Unprotect/Protect in place why not leave them as is (and keep using that method)? It works, and you don't have the concern that somebody might open the workbook with macros disabled and find that your sheets are unprotected.

 

[nigelandrewfoster]

Hello Peter. You raise a good point. I keep meaning to ask about that. My application constantly turns EnableEvents on and off. Despite my error trapping, there are times when I find the Enable events disabled, fatal for the user. Since the method I would usually use to ensure its set to True cannot work (using an event driven method), is there a way to get Excel to re-enable events by default? Perhaps through a timed method?

 

[RoryA]

Hi Rory.
Can you elaborate? Are there specific circumstances where it doesn't?

They're not specific and not always the same in my experience, but generally relate to manipulation of objects - such as data validation with dropdowns, or Tables (listobjects).

 

[nigelandrewfoster]

Thanks Rory. So when you say it doesn't work, you mean that you find users can change things they shouldn't or that VBA cannot change things it should be able to using that method?