Use VBA to prevent users from pasting over data validated cells

I am making an order form in excel that i would like to be reasonably secure (i realise that excel is always relatively insecure to someone who wants to crack it but 99% of my users will not have desire or ability to unlock passwords or VBA).  I have used data validation, hidden sheets with picklist information, i have locked cells, protected sheets and protected the workbook which is a good start. However what i would like help with is the ability to stop users from copying cells from another workbook and pasting them over the top of the data validation that exists in the "Item" column.  I have found some VBA code online that appears to be on the right track but is giving me an error at the moment.  Can someone please help edit this code and or provide some new code to solve my problem.  If there are any other suggestions with regard to making this document more secure then i would be interested to know them so i can learn and apply these methods again.

The code is

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation failed." & _
        "It would have deleted approved lists from the system.", 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 = False
End Function

Example-ordering-form.xls
JohnNZExcelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rory ArchibaldCommented:
What error are you getting?
0
 
JohnNZExcelAuthor Commented:
i get a simple variable not defined error on the line
x = r.Validation.Type
If i remove the option explicit line at the top of the module then the error does not occur but the code does not prevent users pasting over the top
0
 
Rory ArchibaldCommented:
Adding
Dim x as long

would be a better option than removing Option Explicit.

I'll look at the workbook tomorrow if noone else jumps in. (it's been a very long day and I'm too tired now)

Regards,
Rory
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
VictorBragaCommented:
As rorya said, just adding the "dim x as long" would solve.

One thing you have to remember is that the Worksheet_Change procedure have to be coded inside the private module of the Worksheet Object (where you read "Sheet1" and not "Module1", e.g.).

This is the working code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation failed." & _
        "It would have deleted approved lists from the system.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
Dim x As Long
'   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

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
JohnNZExcelAuthor Commented:
Thanks for the input - not sure why it is not working however, i can still simply paste data over the top of the validation range - i will keep working on it
0
 
Rory ArchibaldCommented:
It's the merged cells that are the issue. Change the named range definition to extend to column E (B11:E25 rather than B11:B25) and it should work, though you ought to disable events before the Application.Undo and reenable afterwards, or you'll get stuck in a loop.
0
 
JohnNZExcelAuthor Commented:
Thanks for the help and time - i can still paste over the top of the data validation.  Might have to park this one and move on - i am sure it is something stupid i have neglected to do
Integria-ordering-form-V4---Code.xls
0
 
JohnNZExcelAuthor Commented:
Thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.