You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.
What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.
I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.
Let me know what you think!!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Wow, very comprehensive JK. But if all that you need is to prevent data validation from being deleted, here’s a much simpler approach:
http://www.j-walk.com/ss/excel/tips/tip98.htm
But the real problem is that none of these tricks do the job when the user doesn’t enable macros.
Since the author’s approach requires VBA, better to use Change and Calculate event handlers to check for invalid entries rather than trying to trap all paste operations. Besides, it’s not too difficult to enforce activation of event handlers. Most cell formulas could check a named cell containing a formula calling a udf that returns the value of Application.EnableEvents, and if FALSE, these other formulas could all return error values. OTOH, it’d be much more complicated for a udf to check that all the usual ways of pasting from the keyboard are disabled, and a practical impossibility to check for the user using the right-click pop-up menu to paste.
I also catch paste operations to preserve formatting, so it’s not just DV.
Preserving formatting can also be handled by Change and Calculate event handlers.
Dim v As Variant, i As Long, j As Long
On Error GoTo CleanUp
Application.EnableEvents = False
Application.ScreenUpdating = False
v = Target.Value
Application.Undo
If IsArray(v) Then
With Target
For i = 0 To .Rows.Count – 1
For j = 0 To .Columns.Count – 1
.Offset(i, j).Cells(1, 1).Value = v(i + 1, j + 1)
Next j
Next i
End With
Else
Target.Value = v
End If
CleanUp:
Application.CalculateFull
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
My favorite client doesn’t care if the user messes up their workbook by pasting over DV, or overwriting formulas, or whatever. He says they’re grownups, and should follow instructions. He also doesn’t like protected sheets, so the chances of hosing the workbook are not infinitessimal. The way the system now works, the code is all in an add-in and the sheets created from templates, so if the user does mess up something, it usually only takes deleting one sheet and (properly) repopulating a new one to get back on track.
John: Nice approach, and yes, I had read it before. My requirements were a bit different, e.g not all target cells have validation, nor the same validation.
fzz: I considered this approach. But the project I created this for demanded a solution without use of the sheet events. BTW: I see your solution also effectively prevents me from entering a formula in a cell and does not prevent changing formatting by a paste-special operation. Also: Why are you writing the values one-by-one? This would do it too:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
If mbDisableEvent Then Exit Sub
mbDisableEvent = True
On Error GoTo CleanUp
Application.ScreenUpdating = False
v = Target.Value
Application.Undo
Target.Value = v
CleanUp:
mbDisableEvent = False
Application.CalculateFull
Application.ScreenUpdating = True
End Sub
Doesn’t John Walkenbach’s approach also require sheet events?
And fewer problems arise from adding classes, remapping keys, and trapping various commandbar controls than from just using sheet events?
I hadn’t thought about paste special, but on my system running Excel 2003 SP1, the Change event handler is triggered by paste special format operations, so my event handler undoes formatting changes via paste special.
You have a point about the For loop. Also about overwriting formulas with their values. However, there are situations in which only constant data is expected.
JKP,
I downloaded the workbook and tried to copy paste using right non default drag drop…it lets me do it …
Sam
Good work JKP, There also a good bit on this in PED.
I think it’s a hard thing to get right, Like Jon say, users should know better, and like J-walk says what about Macros off?
As for formating I tend to re-apply a format is it’s important, via a recored macro, which is much like using a template i guess.
good stuff
I have a solution that I use where multiple users input to a common spreadsheet. I am going to preface my comments with the acknowledgment that an expert Excel user can get around anything. This solution is in place to protect the data from non experts. I have all sheets very hidden except for the first sheet which states that if you want to use the spreadsheet you must have macros enabled. When launched with macros enabled it unhides the rest of them. Only Column A cells are unprotected. This allows users to navigate up and down the sheet. If they happen to accidentally enter any data in a cell in Column A I have the changed event clear it. The whole point is that they select a Row by mouse click in it’s Column A cell, which then brings up a user form for them to enter data into. All error checking is done within the code for the user form, and as they exit the form their changes are applied to the cells in the Row. When they exit the spreadsheet all the sheets except the first one are again made to be very hidden. This has worked very well for us for about 2 years now.
I’m with Dan. If I want to make a workbook more secure, I don’t mess with all this worksheet stuff. I put together a userform, build validation into it, and usually don’t even let the user know it’s storing data on a worksheet somewhere.
Dan: I do the hiding/unhiding stuff too in the real world app. Might be good to add that to the demo too. The app I wrote this for doesn’t have simple enough layout to enable me to use your userform approach, but it certainly would be a good way to do it.
Sam: good catch.
Jon: Point taken, makes sense, but not always feasible.
I think it’s silly we have to do all of this stuff for validation. It seems ridiculous that there’s nothing MS has done to paste cell contents without destroying validation. You can paste special-validation so why can you not paste special-following validation rules. DV could be so great if it actually were fool proof.
This article caught my attention because this is something I deal with constantly. The best thing that has worked for me so far is just putting a small ‘error check’ button at the top of the workbook, looping through and stopping at each data error so the user can find the bad values and fix them. I use the DV too, but don’t rely on it totally so I end up using both approaches. Also every time the ‘error check’ is done I use code to re-create the data validation on the sheet. That way even if they pasted in blank cells or columns the DV gets reset.
Thanks for this code, it seems to be the best option so far for preserving formatting & valiation but it does not enforce the validation rules nor does it revert back to the previous value upon failure.
This code also moves the active cell back to the cell in which data was entered instead of where the user expects the cursor to be in the next cell.
Instead of using “v = Target.Value” in the code above, pasting by formula will preserve both the formulas entered as well as any cell values if there were no formulas.
I’ve also removed the Application.CalculateFull simply because my Workbook is large and sluggish.
This is what I’m using:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant
Dim activeCell As Variant
Dim oldValue As Variant
If mbDisableEvent Then Exit Sub
mbDisableEvent = True
On Error GoTo CleanUp
Application.ScreenUpdating = False
Dim r As Range
‘ Get the value/formula entered
v = Target.Formula
‘ Determine where the cursor is currently so we can revert back to here if data entry successful
activeCell = Application.activeCell.address
‘ Remove the user’s pasted object and replace with the formula/value only
Application.Undo
oldValue = Target.Formula
Target.Formula = v
‘ Check if the validation passed or failed
If Target.Validation.Value = False Then
‘ Give error message
MsgBox “Warning!!!” & vbNewLine & vbNewLine & _
“The paste operation has caused illegal entries to appear” & vbNewLine & _
“in one or more cells containing validation rules.” & vbNewLine & vbNewLine & _
“Please check all cells you have just pasted ” & vbNewLine & _
“into and correct any errors!”, vbOKOnly + vbExclamation
‘ Revert to previous value
Target.Formula = oldValue
Else
‘ If successful put the cursor back where it was before validation
Range(activeCell).Select
End If
CleanUp:
mbDisableEvent = False
Application.ScreenUpdating = True
End Sub
(sorry, forgot my tags in the first post)
Thanks for this code, it seems to be the best option so far for preserving formatting & valiation but it does not enforce the validation rules nor does it revert back to the previous value upon failure.
This code also moves the active cell back to the cell in which data was entered instead of where the user expects the cursor to be in the next cell.
Instead of using “v = Target.Value” in the code above, pasting by formula will preserve both the formulas entered as well as any cell values if there were no formulas.
I’ve also removed the Application.CalculateFull simply because my Workbook is large and sluggish.
This is what I’m using:
Dim v As Variant
Dim activeCell As Variant
Dim oldValue As Variant
If mbDisableEvent Then Exit Sub
mbDisableEvent = True
On Error GoTo CleanUp
Application.ScreenUpdating = False
Dim r As Range
‘ Get the value/formula entered
v = Target.Formula
‘ Determine where the cursor is currently so we can revert back to here if data entry successful
activeCell = Application.activeCell.address
‘ Remove the user’s pasted object and replace with the formula/value only
Application.Undo
oldValue = Target.Formula
Target.Formula = v
‘ Check if the validation passed or failed
If Target.Validation.Value = False Then
‘ Give error message
MsgBox “Warning!!!” & vbNewLine & vbNewLine & _
“The paste operation has caused illegal entries to appear” & vbNewLine & _
“in one or more cells containing validation rules.” & vbNewLine & vbNewLine & _
“Please check all cells you have just pasted “ & vbNewLine & _
“into and correct any errors!”, vbOKOnly + vbExclamation
‘ Revert to previous value
Target.Formula = oldValue
Else
‘ If successful put the cursor back where it was before validation
Range(activeCell).Select
End If
CleanUp:
mbDisableEvent = False
Application.ScreenUpdating = True
End Sub
John,
Tried your code, thanks! The message box appears, but the application.undo does not undo the paste operation. Any ideas?
Thanks,
Brett
Amend that. It works for cut operations and Edit => clear, but not copy.
Brett
I have been working on this problem lately and I have been searching the Web and forums for information on this subject, and this discussion here have been the best I have found. So I tought I would submit my solution here.
It is very possible that the users of my sheet will like to copy data from their own sheets onto my sheet, and copy data within my sheet. I also use datavalidation to make sure I can use the data when I get it back from the users.
My solution is to run a macro when the sheet is opened. This copies the formatting and the datavalidation of my input range to a hidden sheet. When the user changes any cell in the input range, a worksheet change event simply copies the formating and validation back from the hidden sheet.
That way the use is free to use all of excels copy, cut, paste and drag/drop features without destroying the formating and validation.
The problem with this is that it is possible to enter values that is outside the validation conditions, because the validation is applied to the cell after data entry. The way I solve this is to recheck the validation with a user defined function. Conditional formatting (bright red colour) then uses this function to inform the user that the data has to be corrected.
Using VBA to make changes like this in the sheet causes the undo buffer to be flushed. To still be able to do a undo, I also had to create a custom one level undo function.
I think this solution will be the best for my users. They will be able to use copy/paste like they are used to, and probably want to. Some times some of the data they paste will not be within the validation ranges, but most of it will probably be. The conditional formatting will make them aware of this, and they can manually correct these cell values. I think stopping their paste action with a warning or making it not work will make them frustrated.
To make backup of validation and formatting (called from workbook_open):
Worksheets(1).Range(“rngInput”).Copy
Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).PasteSpecial xlPasteValidation
Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Worksheets(1).Range(“A1”).Select
End Sub
This is the code that take care of the restoring and undo
In sheet1 code:
On Error GoTo xit
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range(“rngInput”)) Is Nothing Then
MakeUndo
RestoreFormat
End If
xit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
‘ Undo Button
Private Sub CommandButton1_Click()
On Error GoTo CustomUndo
Application.Undo ‘ uses ordinary undo if available
Exit Sub
CustomUndo:
DoUndo
End Sub
In a module:
Dim rngSel As Range
Set rngSel = Selection ‘remember what cell to return to
Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Copy
Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteValidation
Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteFormats
Application.CopyCutMode = False
rngSel.Select
End Sub
Public Sub MakeUndo()
Dim ArrayNew As Variant
Dim ArrayOld As Variant
Dim rngSel As Range
Set rngSel = Selection ‘Remember what cell to return to
ArrayNew = Range(“rngInput”).Value
Application.Undo
ArrayOld = Range(“rngInput”).Value
Range(“rngInput”).Value = ArrayNew
Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Value = ArrayOld
rngSel.Select
End Sub
Public Sub DoUndo()
Dim rngSel As Range
Set rngSel = Selection
Worksheets(3).Range(Worksheets(1).Range(“rngInput”).Address).Copy
Worksheets(1).Range(“rngInput”).PasteSpecial xlPasteValues
rngSel.Select
End Sub
“rngInput” is my defined name for the my user input range.
I would wery much appreciate any comments from you guys on this, as I have not fully completed my project yet, and maybe I have missed some important parts.
The conditional formatting is not tested yet, but here is a link to where I think I have figured it out:
http://www.mrexcel.com/forum/showthread.php?t=296934
Hey Bjørnar,
Looks great!!
I’d really like to test this solution. To see if it suits my needs.
Can you give us a link?
Thanks,
Breezback
I have now done some testing on the conditional formatting too. I had to remove the Application.Voilatile statement, as this was slowing down the application siginificantly. The result of this is that the conditional formatting will not update before you enter a cell value. For most uses this does not seem to be a problem. My application uses a language selection option, that changes the validation list. This could make some of the validations conditions false, and will not highlight these errors before next input. I don’t think this will be a problem because i will put in a restriction that does not allow language change after the user has started to do inputs.
The input sheet is protected as it will be for my users, but there is no password, so feel free to remove the protection. I have also removed a lot of data from my real application, that does not consern this test.
My main goal with this solution is to not put to much restriction on the users. I think they could easily get frustrated if Excel does not allow the to do what they normaly do. The only drawback is that they only get one level of undo, but in most cases i think this will be enough.
I would appreciate some input on how you think this works, and if you find any bugs, that should be fixed.
Here is a link to the test file:
http://www.savefile.com/files/1315951
Nice job Bjørnar.
The only drawback I see is that your users could still insert cells or cut and paste cells, which would change the structure of your input area.
My project also requiered that named ranges be left alone, so cut and paste operations had to be prevented as well (in fact anything that may affect named ranges: a paste from an external file may add range names too).
Thanks for your input. We all have different needs. The application in my example will be used for collecting information that the user probably already have in another Excel workbook, exported from a database. The users will probably like to copy and paste to put the information in the right places. For the columns with drop down list, they will probably use the drop down for one cell, and use drag and drop or copy and paste to fill the rest of the column (or part of the column). The users source workbook wil probably not contain any named ranges, so I think I can live with this.
The next thing I will like to improve is to get ridd off the Undo button I made and try to make this work with the user using excels standard undo button. If I do this I think a average excel user will not even notice my that the VBA code is doing something with his worksheet.
Hi Everyone,
Firstly thanks for such a useful and constructive page, lots of great ideas here.
Bjørnar: Great job, I really like what you have done and intend to borrow some of the ideas as I have a spreadsheet with surprisingly similar needs (creating worksheets which will be imported into access tables, hence having strict data validation!)
A couple of things spring to mind, firstly in the list column the ability to fill with the ‘+’ in the bottom right corner of each cell is disabled because the drop down is opened by any click on the cell which may be a minor annoyance (interestingly is works fine on the backup sheet so possibly this is deliberate!).
Secondly, when backing up and restoring the validation and formatting you could consider saving only one row and restoring n times where n is the number of rows (i think vba is fairly capable of identifying the last row). This would mean you do not have to limit the number of rows in the sheet and should bypass the issues of insert/cut-paste since the structure becomes irrelevant. It might be sensible to set n to a minimun of say 50 so the sheet looks like it all has validation on first use.
Hope this makes sense. Any luck with the amendment of the built in edit button?
I think in my project I may create the ability to turn on/off the auto validation/conditional formatting and add a button to apply the RestoreFormat sub manually.
Many thanks
Al
Well, you can schedule your undo macro to be run when the user clicks any of the default undo buttons:
Application.OnUndo “Description of Undo” , “YourUndoSub”
but the vba restore code clears the undo buffer so the undo buttons are disabled? or have i missed something….
Thanks for all your feedback.
I have not had any time too figure out the undo button yet. At first I also tought that the Application.OnUndo would be a easy solution, but I think Alistair is right about that the button will be disabled, when I actually need it.
The tip about only copying one row is a good tip, and I think I will try to do this. That way I will also be sure that all rows have the same validation and formatting, in case I manage to destroy some of this myself when I make changes to the sheet (with restore function disabled). I then only have to edit and test the first row.
As Alistair mentioned, I have also noticed that the fill handle (the little black + in bottom right corner) in a cell with drop down list is sometimes difficult to reach because of the drop down arrow beeing on top of it.
I’m also going to use this solution in another Workbook where cells in other sheets will have references to the input range. I see that this could bring up some problems, as jkpieterse pointed out, if the user insert cells or use cut operations, so I might have to do something about this. For many of these references I already use a combination of INDEX and MATCH referencing only to index cells in row 1 and column A, Which is hidden, protected and outside the input range. I think maybe if I use this solution on all references to the input range, the refferences will still be valid even if the user uses cut operations inside the input range.
AFAIK if you set the APplication.OnUndo, the button will be enabled, but the hsitory will be limited to one entry: the one you just defined in your code. Indeed the history is wiped by your code.
Check out this page for an example on building the undo:
http://www.jkp-ads.com/Articles/UndoWithVBA00.asp
Forgot to add that the Application.OnUndo must be the last entry of your code.
Thanks JK. I tested the Application.OnUndo as my last code and it works perfect.
I have used the feedback from jkpieterse and Alistair to modify my project. I have now created a class module to take care of the preserving of datavalidation and formatting.
You can still do as much cut, copy, paste and drag/drop as you like.
The one level undo works for multiple input ranges.
I have also added a couple of UDF’s that I use for all references to the input area, so no references gets destroyed when you cut or drag/drop on cells in the input area.
Inserting cells is prevented by worksheet protection.
For those of you who wants to take a look at this I have made a demo, available from the link below.
This demonsrates the use of this class module on 2 separate input ranges. There is also a output range showing the use of the UDF’s that references the input areas. Try cut/paste and you can see that no references is destroyed.
The use of a template row for all validation and formatting, that Alistair suggested, also makes changes easier, I dont have to copy the changes to all rows, just change the template.
If the user that puts in the data don’t activate the macro’s, he can still use the workbook, and can probably destroy some formatting and validation. But the template cells is hidden and all invalid data is highlighted and can be corrected, when you reopen the workbook with macros activated.
http://www.savefile.com/files/1398313
Some thoughts (no code) borrowed from an approach I took to this problem. First, before the workbook is saved, it hides all sheets except a warning sheet which explains that macros must be enabled to view the meaningful sheets. Second, all sheets are PW protected and all the fields are locked except data entry cells. Third, Most editing is done in VBA, so there is little data validation. Finally, whenever there is a change in a data entry cell, the entry is edited, reset to a non-harmful state if in error, and formats, conditional formats, and validation if any is reapplied from a mask on a hidden sheet, customized to that particular cell or range. This has multiple advantages: the user can’t screw up formats or edits (we all do it, usually by inadvertence), 1000 cells never go to div0 or NA or NAME? and force you to debug, it allows paste operations into data entry cells, and it’s relatively secure.
This code looks perfect for what I need it for, but the link to http://www.savefile.com/files/1398313 doesn’t work!
Bjørnar Tofteland, are you still out there? And is there anywhere else I can get a copy of this code?
This workbook..
‘ Activate/deactivate – we only want to capture ctrl+v on this specific workbook
Private Sub Workbook_Activate()
Application.OnKey “^v”, “RunMyPaste”
End Sub
Private Sub Workbook_Deactivate()
Application.OnKey “^v”
End Sub
Public Sub RunMyPaste()
‘ worksheet is unprotected for VBA, if cell is locked – do not paste (VBA would allow it)
If ActiveCell.Locked = True Then
Exit Sub
End If
Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
‘ Set up for error checking
On Error Resume Next
S = DataObj.GetText
‘ Clean non-printable characters
S = Application.WorksheetFunction.Clean(S)
‘ Check if error and check if zero length (blank)
If (Err.Number 0) Or (Len(S) = 0) Then
On Error GoTo 0
Exit Sub
End If
‘Paste
Application.EnableEvents = False
Application.ScreenUpdating = False
‘backup cell value
Dim sValue As String
sValue = ActiveCell.Formula
ActiveCell.Formula = S
If ActiveCell.Validation.Value = False Then
‘Give error message
MsgBox ActiveCell.Validation.ErrorMessage, vbCritical, ActiveCell.Validation.ErrorTitle
ActiveCell.Value = sValue
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
‘ paste value again so worksheet_change is processed (colors etc)
ActiveCell.Formula = S
End Sub
Dear Bjørnar Tofteland, would you be kind enough to repost an updated link to the corrected code? Thank you. The olderversion looks good. But would like the undo option to work
Hi, I do not want to have a macro in my spreadsheet because some users doesn’t enable macro due to the risk of internet security/virus issues. I am trying to create a timesheet in excel spreadsheets. Each user will have to use the worksheets that named after their name. There will be 3 columns on left hand side,one for charge number, other for part number, and the third for entering Type of document. Since there are several charges numbers, part numbers and document types on which users would work each days. Therefore, I want to use data validation to create the list of document types, charge numbers and part numbers so that users can select the right combination by using drop down list and then enter their daily timesheet hours against the date and day. Date and Days will start from column 4th but split into two rows. Each user worksheets within a spreadsheet is fully protected with a confidential password. I do not want the users to edit the first 3 columns and then date and days rows. However remaining cells are editable because they need to enter the hours in those cells.
I have shareed the workbook in the network so that every users can access the same spreadsheet and click on their respective worksheets and then select the charge number from column 1, then part number and document type from column 2 and 3 and then enter hours against the date cell. The data validation works fine when the workbook is not shared over the network, but the moment I share it, users won’t able to see the drop down list.
How do I overcome from this disappearing of drop down list when workbook is shared in network.
I appreciate your help.
Thanks,
Srini
Does anyone have the file that Bjørnar Tofteland’s file from February 23, 2008? If so would you be kind enough to post a link to it? Bjørnar Tofteland’s link does not work any more. Many thanks for all your help.