Protecting Worksheets

[John]

I have an excel file with multiple worksheets. I want to
limit data entry by the users to only one sheet and grant
only view rights to the other worksheets.
Unfortunately it seems like I have to go to each
individual sheet and protect them one-by-one. I have
tried the "Protect Workbook" function but that does not
protect individual cells.
Is there any way I can do a mass protect cell in excel to
cover multiple sheets?

 

[John]

The Macro works like a charm. here is my problem though:
When I set up this Macro the way it is written, anyone can
go to Tools/Macro and run it to protect and unprotect.
How can I retain control over the formula and visibility
of the Macro?
p.s. I am really weak in this area.
John

 

[Ron de Bruin]

Hi

You can place Private before it like this
Private Sub test()

This way you don't see it in the macro list (Alt-F8)

You can Protect your project in the VBA editor

Alt-F11
Tools>VBA project properties
See the protection tab

This way they can't see or edit your code

 

[John]

I did place "private" as you said and the Macro is now
hidden. I went to the VBA Project properties and entered
the password in the protection Tab, created a project
name, and description. But what should I puyt in the help
file name?

I guess my problem is how do I create a relationship
between the Macro and the project properties Alt-F11. How
can I invoke the Macro when I go to Alt-F11. I am not
prompted for anything.

Sorry for all the confusion,
John

 

[John Kramer]

I figured out the button problem. But I guess that since
the buttons sort a group of cells that they have to be
unprotected before sorting then protected after sorting.

John

 

[Ron de Bruin]

Hi John

You can unprotect your sheet in the code
and protect it again after your code is ready.

But I like this way.(userinterfaceonly)

Protect your worksheets with code like this
Place this in the Thisworkbook module.

The macro's will be working now


Private Sub Workbook_Open()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
sh.Protect userinterfaceonly:=True
Next sh
Application.ScreenUpdating = True
End Sub


Change the sub that it don't protect the first sheet