workbook protection (1 Viewer)

mahen

Registered User.
Local time
Today, 20:26
Joined
Jan 13, 2008
Messages
56
I need to protect my workbook preventing from entering and make any changes. The work book contain about 100 worksheet which are copy of each one. Just information and names are differnt. Eg file (attached) only have three worksheet. Know how to protect and unprotect one sheet at a time. but how I should be able to protect and unproctect at once. That is workbook? (ie more than one worksheet)

- Mahen
 

Attachments

  • Marcham.xls
    84.5 KB · Views: 145

CJ_London

Super Moderator
Staff member
Local time
Today, 20:26
Joined
Feb 19, 2013
Messages
16,553
Sorry -this is an Access forum - you'll need to post this question on an Excel form
 

mahen

Registered User.
Local time
Today, 20:26
Joined
Jan 13, 2008
Messages
56
I am sure this forum has subforum for excel queires too.
 

pr2-eugin

Super Moderator
Local time
Today, 20:26
Joined
Nov 30, 2011
Messages
8,494
I am sure this forum has subforum for excel queires too.
It does have separate 'Excel' thread.. I will ask the moderators to move this for you..

I can see this is already in the Excel Thread.. My bad.. :eek:

Someone will be along to help you out
mahen..
 
Last edited:

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
You can protect a workbook from modification at the file level as described in EXCEL help

Brian
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
It does have separate 'Excel' thread.. I will ask the moderators to move this for you..

I can see this is already in the Excel Thread.. My bad.. :eek:

Someone will be along to help you out
mahen..

The fault was CJ_LONDON's not yours really as he would mislead you.

Brian
 

mahen

Registered User.
Local time
Today, 20:26
Joined
Jan 13, 2008
Messages
56
Brian,
I cannot use "Allow users to edit rangers" when you choose more than one worksheet at once.
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
Sorry but I don't understand, the workbook protection I mentioned allows/prevents workbook change, isn't that what you want?

Brian
 

mahen

Registered User.
Local time
Today, 20:26
Joined
Jan 13, 2008
Messages
56
How do I protect more than one sheet (workbook) without anyone entering data. I can protect from Rename, hide, delete etc...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:26
Joined
Feb 19, 2013
Messages
16,553
my apologies - I've been reading the Excel form in terms of Access reading writing to Excel rather than just about Excel.

If you want to protect a whole workbook then the vba command is - this assumes that the locked status for all cells is true which is the default state for a new worksheet cell. This allows a user to open the workbook, but not make any changes

Code:
Dim wsht As Worksheet
 
    For Each wsht In ActiveWorkbook.Sheets
       ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
How does that protect anything as no password will be required for a user to unprotect a sheet,

Brian
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
Protect a workbook file from viewing or editing

On the File menu, click Save As.

On the Tools menu, click General Options.

Do either or both of the following:

If you want users to enter a password before they can view the workbook, type a password in the Password to open box, and then click OK.

If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box, and then click OK.

When prompted, retype your passwords to confirm them.

Click Save.

If prompted, click Yes to replace the existing workbook.

As I have said, use file protection

Brian
 

CJ_London

Super Moderator
Staff member
Local time
Today, 20:26
Joined
Feb 19, 2013
Messages
16,553
OK - so add a password

Code:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="MyPassword"
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
But as every sheet now has the same password why not just protect the workbook, then a user knows the situation before opening the Workbook.

Brian

Edit As far as I know the vba code would be accessible and thus the password.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 20:26
Joined
Feb 19, 2013
Messages
16,553
Hi Brian,

Not if you are running the vba from another location (Access, another workbook), opening this spreadsheet using VBA, setting the protection and saving it as an .xlsx.

Alternatively if running the VBA from this one workbook, saving as a .xlsx prompts the user so the VBA is dumped (but then you need to recreate the code each time)

Benefit of doing it this way is the other users can open the file without the need for a password and can view but not change the data

Poster isn't very clear

preventing from entering and make any changes

If they simply want to prevent entering then as you say password protect the whole workbook. But if they want to allow entry to view but not make changes then I think my suggestion does the job

Another solution is to save the file as read only - the user can make changes but not save them
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
With the method I suggested i think the workbook can be opened read only without a password.

Also I don't think that prior to 2007 the user is prompted to dump the VBA but he could delete it after running the code, but as you say the user is not clear as to what he wants and is slow to respond anyway and has not commented on any suggestions.

Brian
 

mahen

Registered User.
Local time
Today, 20:26
Joined
Jan 13, 2008
Messages
56
Brian & CJ,
I have about 100 worksheets in a workbook. All are same format created by create a copy. Just Name of the employees are differnt. User who has the password for this workbook should be able to unlock and enter the input daily. And should be able to lock the workbook(without doing one sheet at a time). In the mean time other use be able to see all worksheets but shouldn't be able to enter or make any changes. Not very good in VBA. so can you do it in my above file please. I know the method of save as tools, general option..... . But this is not I want.
 

Brianwarnock

Retired
Local time
Today, 20:26
Joined
Jun 2, 2003
Messages
12,701
From your explanation I would say that the save as tools etc is exactly the method that is good for your requirement, can you explain where it fails and protecting all sheets separately ,even if by VBA , and thus having to unprotect them separately, wins.

Brian
 

Users who are viewing this thread

Top Bottom