vba prevent Range value and formula from change and deleting on unprotected.

[hajiali]

VBA or Data Validation to prevent range (A58:BK98) formula and value from being changed and deleted even if sheet is unprotected. if user attempt to error message to refer to Employee List tab to make changes

any suggestion greatly appreciated

 

[Yongle]

Place in SHEET code window
(do NOT put in a module like Module1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const rng = "A56:BK98"
    If Not Intersect(Selection, Range(rng)) Is Nothing Then
        Application.EnableEvents = False
        MsgBox "Refer Employee List Tab - cannot amend " & rng
        Range("A55").Activate
        Application.EnableEvents = True
    End If
End Sub

error message to refer to Employee List tab to make changes

Suggestion
The above code prevents the user selecting those cells
Instead of Range("A55").Activate , VBA could ask for a password to allow "authorised" user to make changes

 

[hajiali]

Thanks Yongle.

how would I combine that code and if i also have the below code together

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address = "$A$2" Then
    ActiveWindow.Zoom = 140
  Else
    ActiveWindow.Zoom = 60
  End If
End Sub

 

[hajiali]

.

 

[Yongle]

Try ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const rng = "A56:BK98"
    ActiveWindow.Zoom = 60
    If Target.Address = "$A$2" Then ActiveWindow.Zoom = 140
    If Not Intersect(Selection, Range(rng)) Is Nothing Then
        Application.EnableEvents = False
        MsgBox "Refer Employee List Tab - cannot amend " & rng
        Range("A55").Activate
        Application.EnableEvents = True
    End If
End Sub