"Notify" causing problems

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
Hi all

A while ago I got a lot of code to find out if a workbook was read-only or not, then to super-protect the read-only versions so that "SaveAs" couldn't be used to replace the read-write version. I did stuff like removing copy and past, and the 'file' and 'tools' menus were disable. On closing a read-only version, all of it was enabled and protection was taken away.

I've just found a problem in my code! When a user presses "notify" after the workbook is opened read-only, and then opens the read-write version when he is prompted to do so, the new read-write version still keeps some of the protected characteristics of the read-only version.

Once it is closed, all future versions behave like the read-only versions until i go through the process of painstakingly fixing the WB.

Any ideas as to why "notify" might be causing the problem, and any workarounds?

Ta very much

Tamer
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Tamer,

Would this solve the whole problem (no other code required) or do I miss something?

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.ReadOnly = False Then Exit Sub
Cancel = True
MsgBox "Saving not allowed", 48, "NO SAVE"
End Sub

kind regards,
Erik
 

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
Hi Erik

Thanks a lot for responding. Just to give you some specifics...

The idea of all the code for the read only version is to stop people from entering info onto that, then being lazy and pasting over someone else’s info on the read-write version later on. And it prevents people here (who don’t have write access) from sabotaging the real thing.

So the code protects all the cells on the read only version, it disables copy & paste and drag & drop, disables the tools end edit menus, prevents the user from arranging windows (so that they can’t copy entire sheets to another book), and disables save and save as.

When the user closes (or deactivates) the read-only version, all of the above is reversed. It works brilliantly – until “notify” is used to switch from read-only to read-write.

So I don’t think your code gives me the level of security I need.

I don’t suppose vba has a way to recognise when “notify” is used – and then to act accordingly?

Thanks again

Tamer
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Are you saying that some macros of the read-only version are activated in the read-write versions? Could you solve this by using If ThisWorkbook.ReadOnly = False Then Exit Sub to start those macros?

Yet, users don't like all those restriction in menus. When a few workbooks are open at the same time it's not funny to miss menus. So if possible I would advise to protect in another way.
The idea of all the code for the read only version is to stop people from entering info onto that
= lock all cells and protect the sheets ==> see code
then being lazy and pasting over someone else’s info on the read-write version later on
if they can't select something, they can't paste ==> see code
And it prevents people here (who don’t have write access) from sabotaging the real thing
isn't this the same as the first quote
so that they can’t copy entire sheets to another book
therefore you can protect the workbook ==> see code
and disables save and save as
==> see my previous post

Code:
Sub block_it()
For Each sh In Worksheets
sh.Unprotect "password"
sh.Cells.Locked = True
sh.EnableSelection = xlUnlockedCells
sh.Protect "password"
Next sh
ActiveWorkbook.Protect Password:="password", structure:=True, Windows:=True
End Sub

isn't this code doing all you need?

you could undo this for some users
on sheet 1 you define a range input_cells1
on sheet 2 input_cells2
...

Code:
Sub allow_me_to_edit()
If Inputbox("code",.... <> "password" Then Exit Sub
For Each sh In Worksheets
sh.Unprotect "password"
sh.Range("input_cells" & sh.Index).Locked = False
.......
End Sub

is this a step in the good direction ?

kind regards,
Erik
 

The Tamer

Well-known Member
Joined
Jun 10, 2004
Messages
520
Hi Erik,

Thanks again. The code you gave me in your first reply was pretty similar to part of the code i was using in my workbook - it just didn't do all that i need. Howevr, your second piece of code is much simpler than mine and does seem to do what i need it to, so thanks.

As for:
users don't like all those restriction in menus. When a few workbooks are open at the same time it's not funny to miss menus.
. The menus were restored whenever a different workbook was activated.

The idea of all the code for the read only version is to stop people from entering info onto that. = lock all cells and protect the sheets ==> see code
And it prevents people here (who don’t have write access) from sabotaging the real thing. isn't this the same as the first quote
No, I'm saying that some people are lazy, or forget they're in read-only, and, on the other hand, i'm saying that others are more intent to break it deliberately - which prompted me to add more protection.

Anyhow, as I've said, your code seems to do what i need - I just need to test it with "notify" now.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
OK, Tamer,

I wish you succes and hope you will post the result here!

kind regards,
Erik
 

Forum statistics

Threads
1,147,690
Messages
5,742,647
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top