How do I pretect sheets in such a way that users can input data but
can't change formulas, macros etc?
Thanks
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
Google is your best friend!
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
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:
> 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
[email protected]HIS
"John" <[email protected]> wrote in message
news:[email protected]...
> 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:
> 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
[email protected]HIS
"John" <[email protected]> wrote in message
news:%[email protected]...
> 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:
> 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"
<[email protected]> 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
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 <[email protected]> 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
>>
Perfect thanks you
John
Gord Dibben wrote:
> 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 <[email protected]> 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:
> How do I pretect sheets in such a way that users can input data but
> can't change formulas, macros etc?
>
> Thanks
> John
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks