Data Validation Macro stops working when worksheet is protected

K

keeferino

New Member
Joined
May 21, 2014
Messages
16
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)
[COLOR=#0000ff]    'Does the validation range still have validation?[/COLOR]
    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
[COLOR=#0000ff]'   Returns True if every cell in Range r uses Data Validation[/COLOR]
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Your function is looking for a range object to be passed to it. Target.Column is not a range object so I'm surprised that the code works on an unprotected sheet.
Try this to see if it works on your protected sheet:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    MsgBox Target.Column
    If HasValidation(Target.Columns(1)) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
        Application.EnableEvents = True
    End If
End Sub

Private Function HasValidation(r As Range) 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 = False
End Function
 
Upvote 0
keeferino said:
I gave that a shot but still have the same issue. It still works until I protect the sheet.
Click to expand...
Try adding this as the first line - note the comment.
Code: 
    Me.Protect Password:="Pswd", userinterfaceonly:=True  'Substitute your password for Pswd
 
Upvote 0
keeferino said:
Unfortunately still a no. The function still returns true.
Click to expand...
If the function returns True after you paste in an invalid value, that indicates the cell(s) that received the paste have retained their validation.

Perhaps it would be better to prevent pasting to cells on the sheet. Try this:
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If HasValidation(Target.Columns(1)) Then
    If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
        Application.CutCopyMode = False
        MsgBox "Your last operation was canceled." & _
                "Pasting is not permitted on this sheet", vbCritical
    End If
End If

End Sub
Private Function HasValidation(r As Range) 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 = False
End Function
 
Upvote 0
Preventing copy/paste isn't really an option. I just don't want the data validation to be pasted over. Everything works fine until I protect the workbook.
 
Upvote 0
keeferino said:
Preventing copy/paste isn't really an option. I just don't want the data validation to be pasted over. Everything works fine until I protect the workbook.
Click to expand...

Can you post the code that "works fine" until you protect the workbook?
 
Upvote 0
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim columnNumber
'Dim columnHeader
'columnHeader = Cells(1, Target.Column)
columnNumber = Target.Column

Select Case columnNumber
Case 2
'Type of Change
'Does not allow data validation override by copy/paste
        If HasValidation(Columns(columnNumber)) Then
            Exit Sub
        Else
            Application.Undo
            MsgBox "You must choose a valid response from the DropDown.", vbCritical
        End If

End Select
End Sub

'Function for Data Validation sub
Private Function HasValidation(r As Range) 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 = False
End Function

When I step through everything is fine until the last line of the function and no matter what I paste in the cell it returns true. When I protect the sheet I have the header row cells locked but nothing else.
 
Last edited:
Upvote 0
I tried unlocking the cells and allowing everything when I click protect and still having the issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Protected sheet disables macro buttons
Replies
5
Views
303
warda11
W
A
  • Question
Worksheet Change Event Triggering With Cell Validation "Cancel"
Replies
1
Views
208
rlv01
R
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
263
Gajendran Yadhav
G
K
  • Solved
I have code that works for creating a drop-down list on a WORKSHEET that allows you to select multiple items... can this also be done on a USERFORM?
Replies
2
Views
150
kbishop94
K
E
  • Solved
VBA fails when worksheet is protected?
Replies
1
Views
120
EssKayKay
E

Forum statistics

Threads
1,214,646
Messages
6,120,718
Members
448,986
Latest member
andreguerra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top