Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Unprotech 16 sheets in one go

0

Hi

I have 16 sheets that I protected.

Is there any way that I can unprotec ALL of them in one go?

Also  is there a macro that I can run to HIDE all the icons at the top of workbook like

file insert page layout data etc and all below it?, is it called the menu bar?

Thank You

Kim

excel
MS Office Version: 2007
posted by: kimzac (rep: 2) on Sun Dec 10, 2023 at 7:03 am
Answer
Discuss

Discussion

Hello Kim and welcome to the forum,  
Umprotecting / protecting all sheets "in one go" is definitely possible by using VBA. There are several different methods to do this so more information from you is needed.
Do all sheets have the same password? When do you want the sheets unprotected? When the file is opened (not recommended as it defeats the purpose of protecting the sheets) or by some action by the user (you)? What action would that be?
When do you want to re-protect the sheets? While the workbook is open or when you close the workbook?
Hiding the ribbon (File, Home, Page Layout, etc) with a macro is also possible. Hiding the ribbon greatly limits what can be done. Why do you want to hide the ribbon?
WillieD24 (rep: 557) Dec 10, '23 at 10:29 am
Willied24
Thank you for getting back to me
I hope I'm doing this correct below
Or do I just paste it in then commit below it
Do all sheets have the same password?

Yes they do
==================================
When do you want the sheets unprotected?

Action by me with a button or some thing like that
===========================
When do you want to re-protect the sheets?

Next time the file opens
============================
Why do you want to hide the ribbon?

Because that out of the way I have more work space .
BUT if I want it back to do some more work on the sheet a button or something to bring it back again/

Hope I have made my self clear for a Dumb blond

Thanking you
Kim
kimzac (rep: 2) Dec 10, '23 at 2:19 pm
Hi Kim

It would help Willie (and me) to provide a solution if you would provide a representative Excel file (no personal data). Please edit your question and use the Add Files button to attach a file. 
John_Ru (rep: 6142) Dec 10, '23 at 4:02 pm
Hi Kim,

Adding to the discussion as you have is OK, but copy / paste is not necessary. As long as answers to questions are complete and clear, that is all that's needed.

I don't know if I will have the time today to get back with a possible solution, but hopefully tomorrow. I do have a couple more questions:
Who has access to / works with this file - just yourself or others too?
How familiar are you with VBA (code modules, worksheet code windows, workbook code window, etc)?
WillieD24 (rep: 557) Dec 10, '23 at 4:15 pm
John.RU

It is nothing as a spreadsheet all I have done is made 12 sheets (with nothing in them) and protect them with a password for each sheet with the same password.
It is just a test for the sheets when I do make my proper spreadsheet.

willieD24

Who has access to / works with this file.

Just me at this time BUT could be more than one

How familiar are you with VBA (code modules, worksheet code windows, workbook code window, etc)?

Well being a newby to this i'm not to bad at working things out.

Depends what you mean by the above, but do know how to use vba and how mudules are and macros etc

Hope that heps.

Kim
kimzac (rep: 2) Dec 10, '23 at 6:20 pm
Add to Discussion

Answers

0
Selected Answer

Hi Kim,

I found some spare time so I made the attched workbook so you can see how things would work.

The workbook only has 5 worksheets, but you can add as many as you like.

On the first sheet there are 2 buttons:  "UnProtect" and "Protect". When the macros run a password is reqired; no password - nothing happens. All sheets are currently unprotected. I have also placed the protect macro in the Workbook_BeforeClose event so there is no chance to forget protecting the worksheets.

Updated Dec. 11

I knew there was a better way to close the file so I have added another file (Ver 1B) with the change. With "Ver 1B" file, upon closing the workbook, if the sheets are not protected, a messagebox will pop-up to advise you of this and give you the chance to protect them now by clicking "Yes". Clicking "No" will leave the sheets unprotected.

For the ribbon a macro is not necessary.  To hide the ribbon press "Ctrl + F1"; to unhide the ribbon press "Ctrl + F1" again

If this solves what you were hoping for please mark answer as Selected.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Sun Dec 10, 2023 at 9:28 pm
Discuss

Discussion


WillieD24

Sorry i'm confused to what is going on , I see your new answer above BUT I do not get all the other stuff above .
Now you have done a new answer do i delete all the other stuff above?
You say you have done another file
I have added another file (Ver 1B)
BUT all I see is
Files Protect UnProtect - Ver 1.xlsm Go To Group A-B-C - Ver 2.xlsm





Confused
kimzac (rep: 2) Dec 12, '23 at 6:25 am
Hi Kim,

Sorry about that, I attached the wrong second file (I was rushing). I have corrected it. The "Ver 1B" file is the better of the two.
I will try to clear things up for you.
The macros use the worksheet code names - not the worksheet tab names; so the sheet tab names can be changed without affecting the macros. The passord is not stored in the file; if it was, someone would be able to find it and unlock the sheets. To protect all worksheets click the green "Protect" button and enter the password of your choice when prompted. To unprotect all worksheets click the yellow "UnProtedct" button and enter the password that was used when protecting the sheets. To hide / unhide the ribbon just press "Crtl + F1".

Hope this helps.
WillieD24 (rep: 557) Dec 12, '23 at 11:40 am
willieD24

Thank you for your help all done now and working

Kind Regards
Kim
kimzac (rep: 2) Dec 13, '23 at 7:40 am
Kim,

Glad I was able to help. Thank you for selecting my answer.

Cheers   :-)
WillieD24 (rep: 557) Dec 13, '23 at 8:25 am
WillieD24
Sorry to bother you again.
But do you know how I can turn on to email me when someone gets back to me
I did not see your last post till I logged on
KIM
kimzac (rep: 2) Dec 14, '23 at 4:29 pm
Hi Kim,

There used to be an email sent to the poster when someone commented on/answered their post but that feature has gone away. I don't know why and I miss it. Now, the only way you can know is to login and the "Alerts" at the top will be RED with the number of un-read comments in brackets.
Logging-in is now the only way a poster can see if there has been any activity on their post.

Cheers   :-)
WillieD24 (rep: 557) Dec 14, '23 at 9:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login