• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically unprotect all sheets when go to a particular sheet

K

kuldeepnagar

New Member
Hi All
I am new on this forum. Working on a very important workbook and need to submit in couple of hours.
I have sheet 1 with many (around 150) check boxes. These check boxes operates 150 macros on sheet2, sheet3 and sheet4. These sheets are password protected. Each marco is with IF ELSE condition.

Is there any way/vba code that as soon as i go to sheet1, all remaining 3 sheet gets unlocked and when i leave sheet1, all remaining 3 sheets gets locked so that i can operated all check boxes without entering password in macros.

Many thanks for your support.

Kind Regards
Kuldeep
 
Hi,
Like this?
Code to unprotect all sheets when you enter sheet1
Code: 
Private Sub Worksheet_Activate()
For i = 2 To Sheets.Count
    Sheets(i).Unprotect "pw"
Next i
End Sub
Code to protect all sheets when you leave sheet1
Code: 
Private Sub Worksheet_Deactivate()
For i = 2 To Sheets.Count
    Sheets(i).Protect "pw"
Next i
End Sub
Replace pw with your own password.
 
Belleke said:
Hi,
Like this?
Code to unprotect all sheets when you enter sheet1
Code: 
Private Sub Worksheet_Activate()
For i = 2 To Sheets.Count
    Sheets(i).Unprotect "pw"
Next i
End Sub
Code to protect all sheets when you leave sheet1
Code: 
Private Sub Worksheet_Deactivate()
For i = 2 To Sheets.Count
    Sheets(i).Protect "pw"
Next i
End Sub
Replace pw with your own password.
Click to expand...
Hi belleke, thank you so much for your reply. Can you please guide me how to link above code with sheet 1?
Thanks for your support.
 
Can you please guide me how to link above code with sheet 1
Click to expand...
At the botton of excel you have the sheetname tabs
Right click on the Sheet1 tab, then click on View Code.
The VBA editor will open in the sheet 1 module, copy and paste my2 codes blocks there.
Close the VBA editor.
Save file as xlsm
 
Hi Belleke
I did exactly same.
Belleke said:
At the botton of excel you have the sheetname tabs
Right click on the Sheet1 tab, then click on View Code.
The VBA editor will open in the sheet 1 module, copy and paste my2 codes blocks there.
Close the VBA editor.
Save file as xlsm
Click to expand...
Hi Belleke
I did exactly same but it is not unlocking other sheets. When i run any macro to make changes in other sheets while being on my sheet1, i get error message " Unable to set the hidden property of the range class". If i unprotect sheet manually, my macro works fine.
 
kuldeepnagar
Did You notice that ... 'someone' wrote?
I understand decorum of a forum and respect rules.
... and still, You're continue same way.
Ps. Your thread was about [un]protecting and now You're asking about [un]locking - different tasks.
 
vletm said:
kuldeepnagar
Did You notice that ... 'someone' wrote?
I understand decorum of a forum and respect rules.
... and still, You're continue same way.
Ps. Your thread was about [un]protecting and now You're asking about [un]locking - different tasks.
Click to expand...
Dear Vletm, that's the reason, i am writing in one forum on this topic. Is there a difference between unprotecting a sheet and un locking a sheet? my purpose is un protecting.
Regards.
 
You must log in or register to reply here.
Back
Top