Solved

Use VBA to prevent users from pasting over data validated cells

Posted on 2011-09-06
8
691 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Accepted Solution

by:
VictorBraga earned 250 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 250 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

789 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