0

I'm trying to add data validation to a worksheet, but when I paste a value, the data validation is removed, as well as the conditional formatting.

I've tried "Protect Workbook" but I can't find any option that will allow editing of values in locked cells while preserving everything else.

I've also tried about five different methods of writing some VBA to do this but so far none of them work, mostly due to not being able to save the old properties before the new values are applied.

How can I lock the sheet or write some VBA to lock everything about a cell except its value? I'm using Excel 2016 but my users might not be. I need a solution that will work in as many older versions as possible.

I am aware that "Paste Special" will paste only values, but I am giving this spreadsheet to users to fill in and return, and I'm not confident that they'll understand or remember to "Paste Special" every time, resulting in us trying to use invalid data.

3 Answers 3

0

There is no easy answer for this. You've already explored the options with VBA, but you've probably found that there are way too many ways to paste something into a cell to intercept them all with VBA.

There are several discussions on the web about handling this scenario, but none of them have so far delivered a solution that actually works. If a cell is not protected, then anything can be pasted into it.

The only way that works is to educate your users.

2
  • Not entirely true. VBA can reconstruct the cell, including the data validation.
    – LPChip
    Jul 17, 2017 at 7:40
  • @LPChip It can. But intercepting every possible way that can be used to do the pasting is the problem.
    – teylyn
    Jul 17, 2017 at 20:59
0

VBA is indeed the way to go here.

What you want to do is record a macro. Once the macro recording is active, setup data protection for one cell, and stop the recording.

This will give you the VBA code for setting up data protection the way your cell needs to be.

Now, create a macro that runs when a cell changes. You can use activecell to get which cell the user has pasted into, and execute the setup routine for data protection on that cell to fix it again.

Next, you will want to evaluate if whatever the user has pasted into the cell is correct, and if not, either fix it in the macro, or simply clear it. An msgbox can be used to notify the user what they did wrong.

Of course, if you instruct your users on what is allowed, the chance of errors will reduce, but I have worked in a large office environment and I know how powerful a good macro is against silly mistakes.

4
  • How would this work when the value is incorrect? The rule is set after the value is saved, presumably not prompting the user to correct it?
    – CJ Dennis
    Jul 17, 2017 at 7:47
  • Thats why you set the data protection first, then make it evaluate the cell. It could be as simple as using SENDKEYS "{F2}{ENTER}" which does, edit cell, submit.
    – LPChip
    Jul 17, 2017 at 8:12
  • In a large workbook with many different columns that use data validation, you would have to set up worksheet change events to monitor a lot of cells and the code that repairs data validation would be very complex. With a single column and a single DV rule, it's not too hard. But how about a table with 100 columns and 99 of them using DV? How about catching when a user pastes a row of data instead of just a single cell? So you'd be restricting edits to one cell only. And the code gets more and more complex.
    – teylyn
    Jul 17, 2017 at 21:03
  • Most of the time, you are not doing data validation with many different condition. Usually its just: if its a number. In those cases it is not that hard. But I guess that pasting a row could break the sendkeys approach. But if I'm not mistaken there is an event for cell change which would have a reference to its cell. So cells.activate followed by a sendkeys would still work. But it was more than 6 years ago when I last worked extensively with excel macros.
    – LPChip
    Jul 18, 2017 at 7:12
0

Another approach would be this.

You still need VBA for it, but you can basically find out if the user pasted. If so, undo the paste, then paste special instead.

You will have to record the macro for paste special to see the excel code.

Your code would look something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim lastAction As String

  ' Get the last action performed by user
  lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)

  ' Check if the last action was a paste
  If Left(lastAction, 5) = "Paste" Then

    ' Perform an undo here

    ' Do a Paste special here

  End If
End Sub

see also https://stackoverflow.com/questions/12525942/how-to-detect-paste-event-in-excel

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .