Protecting Sheets - Code stops???

[thomas.szwed]

Hello,

I have a workbook with multiple sheets and buttons/macros/operations. The workbook will be used by many users and will no doubt will try to be changed be each individual user. I want to stop this and lock it down to one format for everybody. But as soon as i try to protect the sheet, some of my VB functions like copy data from sheet to sheet wort work because protecting a sheet prevents the code from inputting data into protected sheets etc.....does anyone know of a work around???

Thanks!

[Simon Lloyd]

you need to unprotect before running your code and protect after like this:

ActiveSheet.UnProtect Password:="password" 'password would be your actual password
'Your code
ActiveSheet.Protect Password:="password"
note this if for the active sheet, just use your standard sheets notaion for individual worksheets!

[thomas.szwed]

Do you know of how to adapt this code to unlock multiple sheets at once.....say sheet1 and sheet2......all in the one Sub that is performing the commands...?

Thanks!

[Simon Lloyd]

something like:

for each sheet in sheets
sheet.unprotect Password:="password"
next sheet
and to reprotect

for each sheet in sheets
sheet.protect Password:="password"
next sheet

[thomas.szwed]

Hi again,

The only little problem i have left is that within my userform i have many buttons that perform operations in many different subs. Will i have to use this protect statement for every single button or is there a way to say "when the userform is activated - unprotect this sheet" then you can click all the buttons and they will all work because protection is disabled. Then "when the userform is closed" protect the sheet again....?

thanks!

[Simon Lloyd]

just put the unprotect statement in a sub and then Call the sub when the userform initializes then in the userform close query put the protect statement

[thomas.szwed]

I havent built a button to close the userform.......instead the user just clicks on the X in the top menu bar....this i cant see anywhere in the code? How would i reprotect the sheet when the user closes the form using the standard X button???

Thanku!

[PaSha]

try something like

If UserForm1.Unload Then Sheets("name").Protect Password := "pass"
End If


this code should be than in your userform ...

this is a sugestion...

[rory]

In the Workbook_Open event, use something like:
Dim sh As Object
For Each sh In ThisWorkbook.Sheets
sh.Protect Password:="whatever", userinterfaceonly:=True
Next sh


Your code will then be able to change the sheets without having to unprotect them each time.

[Simon Lloyd]

Just a quick word of warning:


Your code will then be able to change the sheets without having to unprotect them each time
Rory is of course right but you will also not be able to make changes to worksheet unless by code!

put these in your userform module:

Private Sub UserForm_Initialize()
For Each sheet In sheets
sheet.unprotect Password:="password"
Next sheet
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
For Each sheet In sheets
sheet.protect Password:="password"
Next sheet
end if
End Sub