Prevent Workbook Save BUT Save in Macro

Question

I am writing a code that will prevent the user from saving the workbook, and it will only save when I want it to. This is to prevent the user from making changes and saving when they are not supposed to. I have created two private subs, but I don't know how to make an exception when the workbook is being saved on my own. I would like to be able to place the saving code in various macros so that I can control the save at any point.

The following is my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    MsgBox "You can't save this workbook!"
    Cancel = True

End Sub

Private Sub Workbook_Open()
Dim myValue As String
Dim Answer As String
Dim MyNote As String

MsgBox "Welcome to the Lot Input Program"
If Range("A1").Value = "" Then
Line:
    myValue = InputBox("Please input your email address:", "Input", "x@us.tel.com")
    'Place your text here
    MyNote = "Is this correct?: " & myValue
    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Confirmation")
    If Answer = vbNo Then
        'Code for No button Press
        GoTo Line
    Else
        Range("A1").Value = myValue
    End If
    ActiveWorkbook.Save
End If
End Sub

You could check `Environ$("Username")` to check to see who has it open. That will return the username they use to log in to their computer. — braX, Sep 29 '17 at 12:12

Subodh Tiwari sktneer · Answer 1 · 2017-09-29T12:41:22.043

1

You may try something like this...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Environ("UserName") <> "YourUserNameHere" Then
    MsgBox "You can't save this workbook!"
    Cancel = True
End If
End Sub

Edit:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Ans As VbMsgBoxResult
Ans = MsgBox("You can't save this workbook!" & vbNewLine & _
        "Do you have password to save the file?", vbQuestion + vbYesNo)
If Ans = vbYes Then
    frmPassword.Show   'UserForm to accept the password
Else
    Cancel = True
End If
End Sub

edited Sep 29 '17 at 12:41

answered Sep 29 '17 at 12:15

Subodh Tiwari sktneer

9,906
2
18
22

The problem that I have with this is that I will be distributing this to everyone in the office, so I can't hardcode usernames into this. Also, username is irrelevant in this case because regardless of the user when I have save functions sprinkled throughout this project it will need to be able to save, so nothing can restrict the saves I force on the user. – Coding Novice Sep 29 '17 at 12:18
The code will prevent all other users to save the file except the case when you open the file on your own system and save it. Another workaround is, you can have a UserForm with one TextBox to accept the password to save and when someone tries to save the file, UserForm will be popped up asking user to enter the password and if the password entered is correct, user will be allowed to save the file else now. Advantage of having UserForm is, you can hide the entered password with the password char which is not available with InputBox. – Subodh Tiwari sktneer Sep 29 '17 at 12:23
The problem with this is that I still want to be able to initiate a save on a "non-authorized" person's computer. For example although i may password restrict the save, I still want to save their file during certain points of running. Is it possible to set up a password restriction on the save, and then enter the password automatically when running code? – Coding Novice Sep 29 '17 at 12:26
When the save is triggered, the code never knows who has triggered it and cannot decide whether to allow the save or not. So all you need to tell the code that it's you who has triggered the save and providing password explicitly is the only option if you try to save the file on another user's system. Though you may invoke the UserForm after the msgbox popped up telling the user "You can't save this workbook!" with a line "Do you have password to save the file? See the edited answer for the code... – Subodh Tiwari sktneer Sep 29 '17 at 12:40

score 0 · Accepted Answer · answered Sep 29 '17 at 12:35

I added a public variable saveLock that I reference in the save cancel code. This allows me to lock and unlock the save inside of my code. If anyone has a better way please let me know, but this did solve the problem.

Public saveLock As Integer
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If saveLock = 0 Then
    Cancel = True
End If
End Sub
Private Sub Workbook_Open()
Dim myValue As String
Dim Answer As String
Dim MyNote As String
saveLock = 0

MsgBox "Welcome to the Lot Input Program"
If Range("A1").Value = "" Then
Line:
    myValue = InputBox("Please input your email address:", "Input", "x@us.tel.com")
    'Place your text here
    MyNote = "Is this correct?: " & myValue
    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Confirmation")
    If Answer = vbNo Then
        'Code for No button Press
        GoTo Line
    Else
        Range("A1").Value = myValue
    End If
    saveLock = 1
    ActiveWorkbook.Save
    saveLock = 0
End If
End Sub

Prevent Workbook Save BUT Save in Macro

2 Answers2