Protecting Worksheets

  • Thread starter Thread starter John
  • Start date Start date
J

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?
 
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
 
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
 
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
 
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
 
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
 
Back
Top