Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Use VBA to prevent users from pasting over data validated cells

Posted on 2011-09-06
8
Medium Priority
?
849 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:JohnNZExcel
  • 4
  • 3
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36487811
What error are you getting?
0
 

Author Comment

by:JohnNZExcel
ID: 36490970
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491299
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Accepted Solution

by:
VictorBraga earned 750 total points
ID: 36497099
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
 

Author Comment

by:JohnNZExcel
ID: 36508738
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 750 total points
ID: 36509011
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
 

Author Comment

by:JohnNZExcel
ID: 36521511
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
 

Author Closing Comment

by:JohnNZExcel
ID: 36597589
Thanks all
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question