Pasting into a cell with data validation

[Dave Punky]

Hi all,

I'm having a real issue in regards to being able to paste into a cell which supposedly has data validation on it.

Basically I have a cell with a dropdown which gives one of 3 options. If you try and type manually into this cell it's fine it throws up the error saying you can't do that. However if I paste into the cell it goes completely unchallenged and just sits there.

I also have the same issue with a cell which has data validation for date specific values, I can just copy text into it and it completely defeats the object of having data validation in the first place.

Is there any way of disabling paste into specific cells, so say A1 has validation on it then you can't paste into that cell, you either have to manually type in or make a selection?

Any help would be appreciated!

 

[Dave Punky]

Jonmo, that script isn't working for me which makes me think I could be doing something wrong rather than this script.

mariog, I tested the new one as well as using the one in the deleted post but it's almost as if it's not there. I tried closing and opening the workbook aswell as reopening Excel thinking it might be that but no joy.

Both look pretty clear and logical to me so I can't understand why I'm not having any luck when I paste into A1, it won't deny me from doing it.

Both of these are now under Sheet 1 rather than ThisWorkbook as advised.

 

[Jonmo1]

Are macros enalbed?

put this in standard module (Insert - Module)

Sub Test()
Msgbox "Hello"
End Sub

And Tools - Macro - Macros
Select and Run "Test"

Does it work?

 

[Dave Punky]

Macros are enabled - my spreadsheet is full of them along with userforms as well. i ran the test anyway just to be sure and that test works in a normal module.

Macros from buttons or actions appear fine (as they are all in standard modules, not the ones for specific worksheets), it's just this one which I guess should be running all the time or certainly as soon as someone attempts to paste into Range A1.

I also have a macro (well a hidden worksheet) which runs on startup which recognizes if macros are enabled or not and if not shows the user how to turn them on, so I'm confident I covered that base.

 

[Jonmo1]

Sounds like Events are Disabled..

Run this macro

Sub test()
Application.EnableEvents = True
End Sub

 

[shg]

Here's another option.

This will prevent changes to validation from copying and pasting, EXCEPT in the case where the copied cell also has validation, but different than the target cell. In each sheet with protected validation, create the name rgnVal with SHEET scope that contains the cells with validation, e.g.,

Sheet1!rgnVal Refers to: =Sheet1!$A$2,Sheet1!$C$2,Sheet1!$E$2

Sheet2!rgnVal Refers to: =Sheet2!$A$1:$A$10

If only selected sheets are to be tested, then in the relevant sheet modules:

Private Sub Worksheet_Change(ByVal Target As Range)
    CheckValidation Target
End Sub

OR, if all sheets are to be tested (even though some may not have the defined name), then in ThisWorkbook,

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    CheckValidation Target
End Sub

Then, in a code module,

Sub CheckValidation(r As Range)
    Dim rChk        As Range
    Dim cell        As Range
    Dim iVal        As XlDVType
 
    On Error Resume Next
    Set rChk = Intersect(r, r.Worksheet.Names("rgnVal").RefersToRange)
    If Err.Number <> 0 Or rChk Is Nothing Then Exit Sub
 
    For Each cell In rChk
        iVal = cell.Validation.Type
        If Err.Number Then
            Err.Clear
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            MsgBox Prompt:="Last operation canceled." & vbLf & vbLf & _
                           "It would have deleted data validation rules.", _
                   Buttons:=vbCritical, _
                   Title:="Oops!"
            Exit Sub
        End If
    Next cell
End Sub

 

[Dave Punky]

Jonmo I ran that script but haven't noticed any change as I can still copy into that cell.

shg, I've run that and I'm still able to paste into A1, despite doing exactly what you have stated there.

I just do Ctrl & V into the cell with any copied text and it throws up no challenge whatsoever.

I know this would be a temporary fix but is there any way I could set a macro to Ctrl & V and if it's one of the cells in the range it doesn't copy? That way your activating the macro when you go to paste? I know this wouldn't cover if somebody right clicked and pasted but it's a bit of the battle at least...

 

[shg]

See if http://www.box.net/shared/nahqoo4qi2 works for you.

The highlighted cells have validation. You have to enable macros.

 

[Dave Punky]

Just tested that workbook, I can paste directly into those cells without challenge with Ctrl V and Right Click > Paste.

Just so you know my macro settings are set to low aswell.

 

[shg]

Just so you know my macro settings are set to low as well.

Ouch! Don't do that! Set it to Medium, or in Excel 2007, Disable all macros with notification, and then enable macros selectively.

Change the event in the sheet module to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "Hellooo!"
    CheckValidation Target
End Sub

Does that work?

 

[shg]

BTW, did you close and restart Excel before opening the workbook?