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! [I]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! [/I]
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
This question generated 24 answers. To proceed to the answers, click here.
This thread is current as of November 14, 2013.