Locking/unlocking cells using VBA while starting and closing workbook

[telesien]

Hi,

I have a task to create Excel sheet for team of people with questionable PC skills.

For that reason I have created several foolproof safety nets using VBA, but they fail on one thing now.

In order to make sure they work, I need make sure that people who use that file have macros enabled.
To make sure they do, I have set the input fields as locked by default and added

Private Sub workbook_open()

Range("B:B").Locked = false

End sub

This way you either have all the safety macros running or you can't use the file at all.

It does some other little things, but this one is important here.

Now the problem is that I need to make sure, that the column B is locked again by default every time someone opens the workbook, no matter how the person before closed it, saved or unsaved.

I tried playing around with "BeforeSave" "AfterSave" and "BeforeClose" and while it does work for some other things like hiding and showing warning message, it never seems to work on locking cells. I think the main issue is that when someone closes the file without saving, the B column simply remains unlocked.

Any idea?
Or alternatively and other idea that might do the same thing?

 

[Joe4]

I think it should only be a problem if they open the file while disabling the macros, manually unlock the range, and then save the file (since macros are still disabled). If they can do that, you will have issues.
If they are opening the file while enabling macros, you can ensure that the ranges get locked with a BeforeSave event procedure. If that is not working, please post your BeforeSave event procedure code so we can see it.

 

[telesien]

I think it should only be a problem if they open the file while disabling the macros, manually unlock the range, and then save the file (since macros are still disabled). If they can do that, you will have issues.
If they are opening the file while enabling macros, you can ensure that the ranges get locked with a BeforeSave event procedure. If that is not working, please post your BeforeSave event procedure code so we can see it.

No, it doesn't really work, my options are a bit limited, so I disabled trusted location and trusted documents, so I can decide what user I try to simulate by enableing or not enabling macros after opening the file.

The codes are:

Private Sub workbook_open()

    Me.Worksheets(1).Unprotect Password:="xxx"
    ThisWorkbook.Worksheets(1).Shapes.Range(Array("TextBox")).Visible = msoFalse
    Range("B:B").Locked = False
    Range("M:M").Locked = False
    Me.Worksheets(1).Protect Password:="xxx"

End Sub

Here I unprotect and than protect again, because other columns than B and M should always be locked for everyone, the textbox is information that user must enable macros

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

        Me.Worksheets(1).Unprotect Password:="xxx" 
        ThisWorkbook.Worksheets(1).Shapes.Range(Array("TextBox")).Visible = msoTrue
        Range("B:B").Locked = True
        Range("M:M").Locked = True
        Me.Worksheets(1).Protect Password:="xxx"

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Me.Worksheets(1).Unprotect Password:="xxx"
    ThisWorkbook.Worksheets(1).Shapes.Range(Array("TextBox")).Visible = msoFalse
    Range("B:B").Locked = False
    Range("M:M").Locked = False
    Me.Worksheets(1).Protect Password:="xxx"
    If Success Then Me.Saved = True


End Sub

If I understand BeforeSave and AfterSave correctly, this should allow all edits when user only saves, but only lock the file again, when user is saving while closing

Finally

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim SaveChanges As Integer
    SaveChanges = MsgBox("Save changes?", vbYesNoCancel + vbQuestion, "Save")
       
    Select Case SaveChanges
        Case vbYes
            ThisWorkbook.Save
            Me.Saved = True

        Case vbNo
            Me.Saved = True
        
        Case vbCancel
            Cancel = True
            
    End Select
    
End Sub

This is basically only to allow saving without the confusing warning about macros being displayed by suppressing the usual Excel save dialogue

The problem is that I can easily brake this.
I open the file with macros disabled, so they don't meddle with anything. Set everything the way I want. All cells locked and warning visible. I save it and close.
I re-open with macros disabled, everything is locked and warning visible. I save it, re-open again without macros, all fine.
Now I re-open, enable macros, save and re-open without macros. Warning is visible, but columns B and M are not locked...

 

[Joe4]

Get rid of the second two codes. You really only want the "BeforeSave" one here. This makes sure that the columns are locked whenever a file is saved.
The "AfterSave" one is no good. If it locks them AFTER the file is saved, and then they close it without saving again, what good is that?
And you BeforeClose does not address the lock columns at all.
I think having all three may be causing some interference, infighting between all three of them. You should only need the "BeforeSave" code.

Note, however if you have the situation where they can open with Macros disabled, and they are manually able to unlock those columns and then save the file, none of this will help, and the macros cannot run when disabled.
Hopefully, since you have password protected the file, they are unable to do this.

 

[telesien]

Get rid of the second two codes. You really only want the "BeforeSave" one here. This makes sure that the columns are locked whenever a file is saved.
The "AfterSave" one is no good. If it locks them AFTER the file is saved, and then they close it without saving again, what good is that?
And you BeforeClose does not address the lock columns at all.
I think having all three may be causing some interference, infighting between all three of them. You should only need the "BeforeSave" code.

Note, however if you have the situation where they can open with Macros disabled, and they are manually able to unlock those columns and then save the file, none of this will help, and the macros cannot run when disabled.
Hopefully, since you have password protected the file, they are unable to do this.

First of all yes, they are unable to do any changes to the file without macros, unless they guess my password. I know it is possible to crack it, I myself have password cracking macro in my posession, but I think I can safely assume, that those who are able to do that, are also good enough to actually use the file without breaking it

As for the advice, I am not sure if I understand.
The AfterSave actually unlocks them, so people can just save the file without closing it and still continue inserting data. It is normal user behavior to save in the middle of work and I want this option to be available.
It is the BeforeSave, that locks them.

I may not be correct about when exactly AfterSave fires, but it works with showing and hiding the warning textbox, so I think I am correct. It just briefly flashes on and off while saving, but is always visible after I open the file with macros disabled no matter what

 

[Joe4]

OK, I see what you are saying.
Then just try getting rid of the "BeforeClose". I don't think that is necessary.
Does that work out then?

 

[telesien]

OK, I see what you are saying.
Then just try getting rid of the "BeforeClose". I don't think that is necessary.
Does that work out then?

Yes, it is the before close that causes the problem. I wonder why...

It isn't necessary now, but I had some plans how to use it as well (besides the cosmetic thing about the message needlessly poping up and confusing people). I guess I can use BeforeSave instead