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

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
747
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!! :)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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! :)
 
Also be aware it doesn't work all the time.
 
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.
 
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!
 
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.
 
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?
 
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).
 
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?
 

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top