"Notify" causing problems

[The Tamer]

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

 

[erik.van.geit]

Tamer,

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

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]

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]

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

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

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]

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]

OK, Tamer,

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

kind regards,
Erik