LinkBack URL
About LinkBacks
Hi
I am looking for a vba toggle interface protection with "password". With condition that upon closing the sheet will be auto protected (if not protected before closing).
Thank you
Regards,
Ash
Hi
I am looking for a vba toggle interface protection with "password". With condition that upon closing the sheet will be auto protected (if not protected before closing).
Thank you
Regards,
Ash
Last edited by ash2017; 04-22-2017 at 01:37 PM.
Just put this in the workbook before close event.
It will protect before closing .change the sheet and password as per your need
Please Login or Register to view this content.
Teach me Excel VBA
If you want to "toggle" between protected and not protected then:
And if you want to protect the sheet when you go to a different worksheet then:Please Login or Register to view this content.
This goes in the sheet's module
And (for the sake of completeness) the code provided in post#2 above should be placed in the ThisWorkbook modulePlease Login or Register to view this content.
Last edited by kev_; 04-22-2017 at 01:49 PM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Hi Kev,
Seems toggle option doesnt ask for password once unprotected.
Hi Imaran,
I am looking for toggle option - 1 key for both protect and unprotect. But will ask password when switch to unprotect.
If that is what you want try this:
Please Login or Register to view this content.
or even
Please Login or Register to view this content.
This toggles "protect" to ON if OFF and
to OFF if ON, but only if correct password entered
Please Login or Register to view this content.
Last edited by kev_; 04-22-2017 at 02:12 PM.
Hi Kev, the last option is what I am looking for. But I cannot run VBA script when protected. Is there any interface protection with similar function. Also upon closing can the sheet auto protected if not protected before closing.
This must be placed in the ThisWorkbook module
Please Login or Register to view this content.
Last edited by kev_; 04-22-2017 at 03:01 PM.
What you need to do is to unprotect your sheet first, then run your code, then protect it again - like this:But I cannot run VBA script when protected
Please Login or Register to view this content.
Thanks Kev,
The below code is working, the problem with re-protection. Thats why I am looking for toggle or auto protection on closing.
Please Login or Register to view this content.
My workbook full of VBA codes, Userforms. Very difficult to put the Password stament for each functions.
Without seeing what the code does, it's difficult to advise. But if you want to protect your sheet, you have little choice.
Does the user need to see the sheet?
Instead of protecting the sheet you could hide it
And there is one bit of code that stops user from unhiding the sheet without VBA
To unhide it via VBAPlease Login or Register to view this content.
If you hide a sheet, you will not need to unhide it to run VBA - UNLESS your code is selecting the sheet or cells in the sheet - so you may need to amend your code avoid that.Please Login or Register to view this content.
Thanks Kev,
I successfully applied your code.
Glad it worked for you
Please mark thread as SOLVED (thread tools at top of thread)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks