Most valuable professional
  • Hot Topics

Excel Data Validation Macro stops working when worksheet is protected

Hey guys, I'm using a macro to ensure that data validation hasn't been pasted over and everything is working fine, until I protect the workbook. Only the first row of data is protected and user are allowed to make almost any modification aside from inserting new columns. The hang up is the HasValidation function. Once the workbook is protected the function always returns 0 errors even if i have pasted in an invalid value. I've been searching all morning and can't come up with an answer. I've tried including code to unprotect the worksheet but the function still doesn't pick up the invalid value. Code: Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(target.column) Then Exit Sub Else Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical End If End Sub Private Function HasValidation(r) As Boolean ' Returns True if every cell in Range r uses Data Validation On Error Resume Next x = r.Validation.Type If Err.Number = 0 Then HasValidation = True Else HasValidation End Function

This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of June 2, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel