Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Use VBA to prevent users from pasting over data validated cells

Posted on 2011-09-06
8
Medium Priority
?
824 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

971 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