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

[William Horton]

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" wrote:

>
> 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
>
>
> --
> Bonbon
> ------------------------------------------------------------------------
> Bonbon's Profile: http://www.excelforum.com/member.php...o&userid=31866
> View this thread: http://www.excelforum.com/showthread...hreadid=535932
>
>

[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

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" wrote:

>
> 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
>
>
> --
> Bonbon
> ------------------------------------------------------------------------
> Bonbon's Profile: http://www.excelforum.com/member.php...o&userid=31866
> View this thread: http://www.excelforum.com/showthread...hreadid=535932
>
>

[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

On Tue, 25 Apr 2006 11:59:53 -0500, Bonbon
<[email protected]> wrote:

>
>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
>
>William Horton Wrote:
>> 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" wrote:
>>
>> >
>> > 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
>> >
>> >
>> > --
>> > Bonbon
>> >
>> ------------------------------------------------------------------------
>> > Bonbon's Profile:
>> http://www.excelforum.com/member.php...o&userid=31866
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=535932
>> >
>> >

[Bonbon]

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]

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


On Tue, 25 Apr 2006 14:57:36 -0500, Bonbon
<[email protected]> wrote:

>
>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


On Wed, 26 Apr 2006 11:10:53 -0500, Bonbon
<[email protected]> wrote:

>
>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