Workbook protection

[Bonbon]

Hi, i searched the forum and found a post on how to protect the
workbook, which is what i wanted.

"Try Tools -> Options -> Security tab
to assign passwords to modify or open the workbook"

However, i want to be able to open the workbook without entering a
password, but the user can't modify/change anything.
Heres a clearer picture, i got a workbook called A and there are two
macros which leads to this workbook.
1-opens it and locks the workbook for protection
2-need to enter a password to insert layout lines etc.

So does anyone know how to protect the workbook from modify but could
be read? or is there a macro code for it as im making macros (sorry for
the wrong section post if this is a macro case )

thanks in advance,
Bonbon

 

[Guest]

Not sure if I'm understanding your question correct but....
If you choose from the menu path File / Save As and then from the Save
As dialogue box choose Tools / General Options you will get a dialogue box
that will allow you to enter a password to modify. If you set that all
up....when users open the file they will be prompted to either enter a
password to modify/edit the file OR open the file as read only. That way
users who do not have the password will be able to open the file in a read
mode and user who do have the password will be able to edit the file.

Will this do it for you?

Bill Horton

 

[Bonbon]

erm the thing is, i dont want t abox to appear asking for pw, i just
need a macro code to lock the whole workbook once workbook is opened so
nobody can modify it.
is there a way?

sorry for the hassle,
Bonbon

 

[Gord Dibben]

To lock workbook or worksheets go to Tools>Protection>Protect Workbook or
Protect Worksheet.

Neither of these will lock the workbook from opening, but can protect from
modification.

Should not require a macro. Just save the workbook with protection set to
"locked"


Gord Dibben MS Excel MVP

 

[Bonbon]

Dear Gord, i have tried the protect workbook many times but everytime
could still modify it. Then i checked the Window box to try, but tha
was not what i wanted. Is there a way to select all the wrksheets? so
can just 'protect wrksheet' whilst selecting all the sheets or groupin
them because the only protection i got to work, was the protec
wrksheet one, never the protect wrkbook.

Thx
Bo

 

[Gord Dibben]

You have to protect each worksheet one at a time unless you use VBA macro to do
them all at once.

Code for that follows............

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

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macros by going to Tool>Macro>Macros.

You can also assign these macros to a button or a shortcut key combo


Gord

Dear Gord, i have tried the protect workbook many times but everytime i
could still modify it. Then i checked the Window box to try, but that
was not what i wanted. Is there a way to select all the wrksheets? so i
can just 'protect wrksheet' whilst selecting all the sheets or grouping
them because the only protection i got to work, was the protect
wrksheet one, never the protect wrkbook.

Thx
Bon

Gord Dibben MS Excel MVP

 

[Bonbon]

That worked great, but could you go through each code/instruction and
explain what it means please? because i need to explain it to the user,
thanks.

Bonbon

 

[Gord Dibben]

Sub ProtectAllSheets()
'the name of the Sub

Application.ScreenUpdating = False
'turn off the screen flashing

Dim n As Single
'Set the data type as (single-precision floating-point)

For n = 1 To Sheets.Count
'count the sheets in the workbook

Sheets(n).Protect Password:="justme"
'protect the first sheet counted

Next n
'protect the next sheet counted and keep cycling until all sheets are protected

Application.ScreenUpdating = True
'turn screen updating back on

End Sub


Gord

That worked great, but could you go through each code/instruction and
explain what it means please? because i need to explain it to the user,
thanks.

Bonbon

Gord Dibben MS Excel MVP

 

[Bonbon]

thank you so much! i really appreciate the help.

thx again,
bon