newbie protection question

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

John

How do I pretect sheets in such a way that users can input data but
can't change formulas, macros etc?

Thanks
John
 
Make sure the cells you want your user to be ABLE to edit are unlocked.
You do that by rightclicking, Format Cells, Protection and make sure the
Locked option is unchecked.

Then you go to your menu bar, Tools, Protection, Protect Sheet.

Notice, if there are formulas you would like to hide, you can do what
was described above and this time, Check Hidden
 
John

On macros you can set up protection for them in the VBE (Alt+F11), for other
things it is a two-stage process. All Excel cells are 'locked' by default,
but this is ignored until the sheet is protected.

Highlight the cells you want users to input into (You can highlight multiple
cells by Ctrl+Click) and now, with these highlighted go to
Format>Cells...>Protection and deselect 'locked', now protect the sheet
(Tools>Protection>Protect Sheet...) and these cells will be 'un-locked' and
entry enabled

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
All cells are protected by default.

To unprotect a cell:
Right click the cells in question, and click "format cells"
Goto the Protection tab and deselect "Locked"
Now when you protect the sheet (tools->protection), the individual cell can
still be changed.

I don't think there is a way to protect macros, but I might be wrong.

Sloth July 2006
 
Hi John,

Highlight all the areas that you want the user to enter data,

go to Format|cells|protection| remove the checkmark from the lock box

then go to Tools|protection|protect sheet| remove the checkmark in the
select locked cells box and enter a password.

Hope this helps

Larry
 
Thanks. Is there a way to protect multiple sheets? The protect workbook
doesn't seem to do it. What does protect workbook do?
Thanks again

John
 
John

Just repeat the instructions on each sheet

Workbook protection prevents the user from opening/editing the file at all
without the password. Worksheet protection allows the user to see the data
and interact with areas allowed by the creator

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
Nick

Workbook Protection is not the same as File Open protection.

Workbook protection disables such features as deleting sheets, changing window
sizes.


Gord Dibben MS Excel MVP
 
John

You can do it with a macro.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Perfect thanks you
John

Gord said:
John

You can do it with a macro.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
To keep people from changing your macro code you have to protect the VB
project. To do that, when in the VB Editor (use [Alt]+[F11] to get there)
choose Tools | VBAProject Properties and then use the settings on the
[Protection] tab to keep people out of there.
 
Back
Top