Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / March 2007

Tip: Looking for answers? Try searching our database.

Protect sheet in macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
traima - 08 Mar 2007 11:02 GMT
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 - 08 Mar 2007 17:52 GMT
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

>Hi,
>
[quoted text clipped - 7 lines]
>
>traima
traima - 09 Mar 2007 07:36 GMT
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:

> When you re-protect the sheet in your macro, protect all cells and make locked
> cells unselectable.
[quoted text clipped - 19 lines]
> >
> >traima
Gord Dibben - 09 Mar 2007 18:05 GMT
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

>Hi Gord,
>
[quoted text clipped - 34 lines]
>> >
>> >traima
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.