VBA Protection Question

[dfenton21]

Hi,

I am using this code to protect all non-blank cells.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.EnableSelection = xlUnlockedCells

If Target <> "" Then
Unprotect
Target.Locked = True
Protect
End If
End Sub

I set up the protection so that the user can't select the protected cells. However, if I save and close the file, and reopen it, the cells are still protected, but the user can select them (but not change). Can I stop this from happening?

Thanks.

 

[Colin Legg]

Hi,

Unfortunately, if you apply this setting via VBA code then the setting is not retained when the workbook is closed and re-opened.
It is, of course, retained if it is set manually via the Excel interface.

In terms of VBA, one option would be to enforce it in the workbook's open event handler. In the ThisWorkbook class module:

Private Sub Workbook_Open()
    Sheet1.EnableSelection = xlUnlockedCells
End Sub

And change Sheet1 to the codename of the sheet in question.

Hope that helps...