Solved

Use VBA to prevent users from pasting over data validated cells

Posted on 2011-09-06
8
612 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Highlighting cells in Excel 9 17
Update As Well As Add 6 38
File not loading into PowerPivot 4 9
Auto Adjust Percent rate 5 29
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now