Protect Data validation cells from copy paste

[Guneet Ahuja]

Hi,

I have unlocked some cells with data validation, now after protecting the
work sheet, when the user types in data the validation is working fine.
However it also allows user to copy - paste any value in these cells
irrespective of the vaidaion conditions. Request you to please let me know
how to correct this and make even copy - paste of data subject to data
validation?

 

[Ken Johnson]

Hi,

I have unlocked some cells with data validation, now after protecting the
work sheet, when the user types in data the validation is working fine.
However it also allows user to copy - paste any value in these cells
irrespective of the vaidaion conditions. Request you to please let me know
how to correct this and make even copy - paste of data subject to data
validation?

You can't validate the pasted data, but you could prevent pasting of
data into the cells with data validation. To do that you use the
worksheet's Selection_Change Event to set the CutCopyMode of Excel
equal to True or False (doesn't matter which) whenever the validated
cells are selected, eg where the validated cell
is A1...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

This method of course fails should the user choose not to allow macros
when the workbook is opened.

To try this out...

Copy the code, Right click the worksheet tab, choose "View code", then
paste it into the code module, then Alt+F11 to return to Excel.

Ken Johnson

 

[Guneet Ahuja]

Hi Ken,

Thank you for your time, I just wanted to clarify this again, that "any
value can be copied & pasted in a cell with whatever Data validation
conditions it might have". If this is true then don't you think this is a big
bug in excel? Given the fact there is no way around this issue as no amount
of password protecting can rectify this problem.

Thanks again for your help.

Best regards
Guneet S Ahuja

 

[Ken Johnson]

Hi Ken,

Thank you for your time, I just wanted to clarify this again, that "any
value can be copied & pasted in a cell with whatever Data validation
conditions it might have". If this is true then don't you think this is a big
bug in excel? Given the fact there is no way around this issue as no amount
of password protecting can rectify this problem.

Thanks again for your help.

Best regards
Guneet S Ahuja

Hi Guneet,

Being able to overwrite data validation by simply copy/paste is
annoying. However, the VBA code I showed does solve the problem,
provided the user doesn't forget to enable macros when opening the
file.
The problem of either forgetful users or even users deliberately
disabling macros is solved with further VBA code.
When the workbook is closed the VBA code adds a worksheet with a
message like "You must enable macros to use this workbook. Close then
enable macros when you reopen this workbook.".
Also, the code hides all the sheets except for the new sheet with the
message. When using VBA to hide a worksheet its Visible property can
be set to either xlSheetHidden or xlSheetVeryHidden. If xlSheetHidden
is used the user can make the sheet visible again by going Format|
Sheet|UnHide, no good here. If xlSheetVeryHidden is used the user can
only get to the sheet by changing its Visible property back to
xlSheetVisible in the sheets Properties window in the VBA Editor,
used here.
This is all done automatically when the workbook is closed, so that
when it is next opened, if the user does not enable macros then all
they see is a sheet with the message reminding them that they should
have enabled macros and must close, reopen and enable macros.
When the workbook is opened and macros are enabled then VBA code runs
to remove the message sheet, that was made the last time the workbook
was closed and return the other worksheets back to there normal
visible state.

If you would like to examine an example of a workbook that is set up
to work as described above then email me (account name =
kencjohnson account type = gmail.com) and I can send you a copy.

Ken Johnson

Ken Johnson