I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin

It sounds like your main concern is that someone that is authorized to change
things might leave one or more sheets unprotected when they close the
workbook. This should take care of that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect
Next
Set eachSheet = Nothing
End Sub

If the sheets are protected with a password, change the one line to
eachSheet.Protect Password:="yourSecret-word"
where yourSecret-word is the real password for the sheets.

To put it into the proper location in the workbook, open it and then
right-click on the little Excel icon at the upper left of the Excel window.
From the list that pops up, choose [View Code]. Copy the code and paste it
into the module presented to you - edit the code if you need to in order to
deal with the password if you use one.

Save the workbook. You can test it by simply unprotecting one or two
sheets, closing the workbook and then reopening it - all sheets should be
protected.

"Kevin89" wrote:

I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin

Thanks for the response. I copied and pasted the code per your instructions
and tried reopening it. The first time it worked as expected but subsequent
times when I saved the file with sheets unprotected, they would remain
unprotected when I'd bring them back up. Any suggestions on what I may be
doing wrong?
Here is what I pasted and how I interpreted your instructions for adding a
password.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect Password:="Shop"
Next
Set eachSheet = Nothing
End Sub

Thanks, Kevin

"JLatham" wrote:

It sounds like your main concern is that someone that is authorized to change
things might leave one or more sheets unprotected when they close the
workbook. This should take care of that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect
Next
Set eachSheet = Nothing
End Sub

If the sheets are protected with a password, change the one line to
eachSheet.Protect Password:="yourSecret-word"
where yourSecret-word is the real password for the sheets.

To put it into the proper location in the workbook, open it and then
right-click on the little Excel icon at the upper left of the Excel window.
From the list that pops up, choose [View Code]. Copy the code and paste it
into the module presented to you - edit the code if you need to in order to
deal with the password if you use one.

Save the workbook. You can test it by simply unprotecting one or two
sheets, closing the workbook and then reopening it - all sheets should be
protected.

"Kevin89" wrote:

I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin

Should work fine - I've double/triple checked it.

Make sure you put it into the right place: open the workbook, again right
click on the Excel icon immediately to the left of the word "File" in its
menu toolbar and select [View Code] from the list. You should see the code
you inserted. If not, you either put it into another workbook that was open
at the time, or in some other not-good-for-what-we-need location.

I created new workbook, added the code and unprotected all sheets. Closed
the book (responding "YES" to the prompt asking if I wanted to save changes).
Opened it back up and (enabling macros along the way), and sure enough the
sheets were protected.

Check your macro security level: Tools - Macro - Security. It needs to be
on Medium or (not recommended) Low. If you change the setting, as from High
or Very High down to Medium, you'll have to first accept the change then
close Excel and reopen it for the change to take effect. After that you'll
be prompted as to whether or not to permit macros to run for any workbook
that contains them. I consider the extra click worth it for the security of
being forwarned if a workbook I don't expect to have macros in it that came
from an unknown source suddenly does have them in it.

So: check macro security; be sure to reply "YES" when asked about saving
with changes (the setting of protection level is considered a change).
Should work.

"JLatham" wrote:

It sounds like your main concern is that someone that is authorized to change
things might leave one or more sheets unprotected when they close the
workbook. This should take care of that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect
Next
Set eachSheet = Nothing
End Sub

If the sheets are protected with a password, change the one line to
eachSheet.Protect Password:="yourSecret-word"
where yourSecret-word is the real password for the sheets.

To put it into the proper location in the workbook, open it and then
right-click on the little Excel icon at the upper left of the Excel window.
From the list that pops up, choose [View Code]. Copy the code and paste it
into the module presented to you - edit the code if you need to in order to
deal with the password if you use one.

Save the workbook. You can test it by simply unprotecting one or two
sheets, closing the workbook and then reopening it - all sheets should be
protected.

"Kevin89" wrote:

I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin

The macro security was the culprit. The file works great now. Thanks so
much for your help.

"JLatham" wrote:

Should work fine - I've double/triple checked it.

Make sure you put it into the right place: open the workbook, again right
click on the Excel icon immediately to the left of the word "File" in its
menu toolbar and select [View Code] from the list. You should see the code
you inserted. If not, you either put it into another workbook that was open
at the time, or in some other not-good-for-what-we-need location.

I created new workbook, added the code and unprotected all sheets. Closed
the book (responding "YES" to the prompt asking if I wanted to save changes).
Opened it back up and (enabling macros along the way), and sure enough the
sheets were protected.

Check your macro security level: Tools - Macro - Security. It needs to be
on Medium or (not recommended) Low. If you change the setting, as from High
or Very High down to Medium, you'll have to first accept the change then
close Excel and reopen it for the change to take effect. After that you'll
be prompted as to whether or not to permit macros to run for any workbook
that contains them. I consider the extra click worth it for the security of
being forwarned if a workbook I don't expect to have macros in it that came
from an unknown source suddenly does have them in it.

So: check macro security; be sure to reply "YES" when asked about saving
with changes (the setting of protection level is considered a change).
Should work.

"JLatham" wrote:

It sounds like your main concern is that someone that is authorized to change
things might leave one or more sheets unprotected when they close the
workbook. This should take care of that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect
Next
Set eachSheet = Nothing
End Sub

If the sheets are protected with a password, change the one line to
eachSheet.Protect Password:="yourSecret-word"
where yourSecret-word is the real password for the sheets.

To put it into the proper location in the workbook, open it and then
right-click on the little Excel icon at the upper left of the Excel window.
From the list that pops up, choose [View Code]. Copy the code and paste it
into the module presented to you - edit the code if you need to in order to
deal with the password if you use one.

Save the workbook. You can test it by simply unprotecting one or two
sheets, closing the workbook and then reopening it - all sheets should be
protected.

"Kevin89" wrote:

I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin

You're welcome, glad it's doing the job for you.

"Kevin89" wrote:

The macro security was the culprit. The file works great now. Thanks so
much for your help.

"JLatham" wrote:

Should work fine - I've double/triple checked it.

Make sure you put it into the right place: open the workbook, again right
click on the Excel icon immediately to the left of the word "File" in its
menu toolbar and select [View Code] from the list. You should see the code
you inserted. If not, you either put it into another workbook that was open
at the time, or in some other not-good-for-what-we-need location.

I created new workbook, added the code and unprotected all sheets. Closed
the book (responding "YES" to the prompt asking if I wanted to save changes).
Opened it back up and (enabling macros along the way), and sure enough the
sheets were protected.

Check your macro security level: Tools - Macro - Security. It needs to be
on Medium or (not recommended) Low. If you change the setting, as from High
or Very High down to Medium, you'll have to first accept the change then
close Excel and reopen it for the change to take effect. After that you'll
be prompted as to whether or not to permit macros to run for any workbook
that contains them. I consider the extra click worth it for the security of
being forwarned if a workbook I don't expect to have macros in it that came
from an unknown source suddenly does have them in it.

So: check macro security; be sure to reply "YES" when asked about saving
with changes (the setting of protection level is considered a change).
Should work.

"JLatham" wrote:

It sounds like your main concern is that someone that is authorized to change
things might leave one or more sheets unprotected when they close the
workbook. This should take care of that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim eachSheet As Worksheet
For Each eachSheet In ThisWorkbook.Worksheets
eachSheet.Protect
Next
Set eachSheet = Nothing
End Sub

If the sheets are protected with a password, change the one line to
eachSheet.Protect Password:="yourSecret-word"
where yourSecret-word is the real password for the sheets.

To put it into the proper location in the workbook, open it and then
right-click on the little Excel icon at the upper left of the Excel window.
From the list that pops up, choose [View Code]. Copy the code and paste it
into the module presented to you - edit the code if you need to in order to
deal with the password if you use one.

Save the workbook. You can test it by simply unprotecting one or two
sheets, closing the workbook and then reopening it - all sheets should be
protected.

"Kevin89" wrote:

I have a question that probably seems pretty basic to most, but here goes...I
have a workbook containing multiple sheets that gets updated many times
throughout the day by just three people. I don't want anyone other than
those three people to be able to make any changes or accidentally delete
something. I know I could unprotect and then protect the workbook each time
a change is needed, but I'm worried that that one time, someone will forget.
Is there a way to create an identical copy of the original that would get
it's info and updates directly from the original? The copy could then be
read-only and the master could remain unprotected and easier to use.

Thanks in advance for any suggestion.

Kevin