VBA, read-only
pirate
-
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
eriiic Posted messages 24581 Registration date Status Contributor Last intervention -
Hello everyone,
I'm trying to secure a file as much as possible when it is opened in read-only mode.
Let me explain, when you open the file it asks for a password or to open it in read-only mode, if I open it in read-only mode, I would like to block input, saving, etc.
I'm at:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Me.ReadOnly = True Then
Application.Undo
MsgBox "This file is read-only, input is not allowed"
ThisWorkbook.Saved = True
End If
Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.ReadOnly = True Then
SaveAsUI = False
Cancel = True
End If
End Sub
The problem is that sometimes the dialog box asking if I want to save the document when I close it appears and therefore allows saving as.
The goal of all this is to prevent my colleagues (very, very, very novice in Excel) who will use it from creating multiple copies of the same document.
Thank you in advance for your help!!
I'm trying to secure a file as much as possible when it is opened in read-only mode.
Let me explain, when you open the file it asks for a password or to open it in read-only mode, if I open it in read-only mode, I would like to block input, saving, etc.
I'm at:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Me.ReadOnly = True Then
Application.Undo
MsgBox "This file is read-only, input is not allowed"
ThisWorkbook.Saved = True
End If
Application.EnableEvents = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ActiveWorkbook.ReadOnly = True Then
SaveAsUI = False
Cancel = True
End If
End Sub
The problem is that sometimes the dialog box asking if I want to save the document when I close it appears and therefore allows saving as.
The goal of all this is to prevent my colleagues (very, very, very novice in Excel) who will use it from creating multiple copies of the same document.
Thank you in advance for your help!!
2 answers
A method that isn't very pretty is to use this:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close False
End Sub
This will close the Excel workbook without prompting to save the file, but it will also leave a gray empty Excel window.
There is definitely a better way, but this one works.
Have a nice day.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close False
End Sub
This will close the Excel workbook without prompting to save the file, but it will also leave a gray empty Excel window.
There is definitely a better way, but this one works.
Have a nice day.
Hello,
a bit late but maybe it will be useful.
You can do without supervision of the changes.
'Save As...', to the left of the Save button you have a dropdown list Tools.
In General Options you can choose a password to prevent changes plus a checkbox Recommended read-only.
In your case, the SaveAs and Close still need to be dealt with anyway.
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, it happens!!!), remember to mark it as resolved. Thank you.
a bit late but maybe it will be useful.
You can do without supervision of the changes.
'Save As...', to the left of the Save button you have a dropdown list Tools.
In General Options you can choose a password to prevent changes plus a checkbox Recommended read-only.
In your case, the SaveAs and Close still need to be dealt with anyway.
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, it happens!!!), remember to mark it as resolved. Thank you.
I found other things that work really well.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Me.ReadOnly = True Then
Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents a save prompt when closing)
End If
End Sub