How do I pretect sheets in such a way that users can input data but
can't change formulas, macros etc?
Thanks
John
|
|
|
|
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
nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS
"John" <JOhn6328[ at ]mchsi.com> wrote in message
news:uxAhEiGoGHA.4192[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > How do I pretect sheets in such a way that users can input data but can't
> change formulas, macros etc?
>
> Thanks
> John
|
|
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
"John" wrote:
[Quoted Text] > How do I pretect sheets in such a way that users can input data but
> can't change formulas, macros etc?
>
> Thanks
> John
>
|
|
|
|
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
Nick Hodge wrote:
[Quoted Text] > 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
>
|
|
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
nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS
"John" <JOhn6328[ at ]mchsi.com> wrote in message
news:%23JTo1yKoGHA.4864[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > 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
>
> Nick Hodge wrote:
>> 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
>>
|
|
Jeez... there is 60+ sheets. Should be an easier way.
John
Nick Hodge wrote:
[Quoted Text] > 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
>
|
|
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
On Thu, 6 Jul 2006 07:18:41 +0100, "Nick Hodge"
<nick_hodgeTAKETHISOUT[ at ]zen.co.uk.ANDTHIS> wrote:
[Quoted Text] >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
|
|
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
On Thu, 06 Jul 2006 08:19:10 -0500, John <JOhn6328[ at ]mchsi.com> wrote:
[Quoted Text] >Jeez... there is 60+ sheets. Should be an easier way.
>
>John
>
>
>Nick Hodge wrote:
>
>> 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
>>
|
|
Perfect thanks you
John
Gord Dibben wrote:
[Quoted Text] > 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
>
> On Thu, 06 Jul 2006 08:19:10 -0500, John <JOhn6328[ at ]mchsi.com> wrote:
>
>
>>Jeez... there is 60+ sheets. Should be an easier way.
>>
>>John
>>
>>
>>Nick Hodge wrote:
>>
>>
>>>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
>>>
>
>
|
|
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.
"John" wrote:
[Quoted Text] > How do I pretect sheets in such a way that users can input data but
> can't change formulas, macros etc?
>
> Thanks
> John
>
|
|