Hi
The macro below works only if the sheet is not protected. How can we make it work when the sheet is locked? For all my module Macros i use a "unlock sheet" command but I have no idea how to do that with this macro since its a private sub and im not sure how or when to trigger the "call unlocksheet" macro I made.
Thanks!
Note on what this macro does: I have two sheets. Sheet one has a header on row 6 and the user is required to select from a drop down list in column C. The list is contained in sheet2 which i named the range as "DataList". I want to prevent a user from copying in data into column C on sheet one since it would get rid of the data validation i have in place. to prevent this, the macro below would undo last command if the range that should contain validations no longer has a validation in any cell. Also note that the range in sheet one is named "ValidationRange" and before this macro is written, every cell in that named range should already have a validation in place. If you have a better method than this id love to hear it. Thanks again!
The macro below works only if the sheet is not protected. How can we make it work when the sheet is locked? For all my module Macros i use a "unlock sheet" command but I have no idea how to do that with this macro since its a private sub and im not sure how or when to trigger the "call unlocksheet" macro I made.
Thanks!
Note on what this macro does: I have two sheets. Sheet one has a header on row 6 and the user is required to select from a drop down list in column C. The list is contained in sheet2 which i named the range as "DataList". I want to prevent a user from copying in data into column C on sheet one since it would get rid of the data validation i have in place. to prevent this, the macro below would undo last command if the range that should contain validations no longer has a validation in any cell. Also note that the range in sheet one is named "ValidationRange" and before this macro is written, every cell in that named range should already have a validation in place. If you have a better method than this id love to hear it. Thanks again!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If HasValidation(Range("validationrange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled. " & _
"It would have deleted data validation rules.", vbCritical
' Call ProtectIN
End If
End Sub
Private Function HasValidation(r) As Boolean
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function