Protect sheet in macro

[Guest]

Hi,

I have this problem; I have a worksheet that is protected, the user kan
change values in 4 spesific cells. My macro unprotects the sheet, calcualte
and protects the sheet again. Now I would like to protect the sheet so that
it will not be changed by a mistake, so that the user cannot save the changes
in the workbook.

How can I do this?

traima

 

[Gord Dibben]

When you re-protect the sheet in your macro, protect all cells and make locked
cells unselectable.

With ActiveSheet
.Unprotect
.Calculate
.Cells.Select
Selection.Locked = True
.EnableSelection = xlUnlockedCells
.Protect
End With

 

[Guest]

Hi Gord,

This looks great! And now, how can I aviod that the user unprotecs the sheet
himself? (Tools - protection - unprotect sheet)

If I didn't need this spesific macro, I would password-protect the sheet.
How can I do this in the macro when the user need to run the macro several
times. Is there a way for the macro to type the password to open - and then
passwordprotect it again?

thanks,
traima

Gord Dibben skrev:

 

[Gord Dibben]

traima

With ActiveSheet
.Unprotect Password:="justme"
.Calculate
.Cells.Select
Selection.Locked = True
.EnableSelection = xlUnlockedCells
.Protect Password:="justme"
End With

Note: you might want to lock the project also to prevent users from looking at
the code to get the password.

With your workbook open.

Alt + F11, CTRL + r

Right-click on your workbook/project and "Properties"

Select "Protection" tab and "lock project for viewing".

Enter a password.

Note: workbook must be saved/closed and re-opened before the locking takes
effect.


Gord