Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data Validation
Hi
My users are copy/pasting from cell to cell. This is fouling my data validation. Is there any way to protect he DV and still allow copy/paste? Thanks |
#2
|
|||
|
|||
VBA can do this:
The first is an event module for your worksheet, the second needs to go into a standard code module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but works for whole workbook ' and works with different types of validation ' Does the validation range still have validation? ' requires sheet level ValidationRange for each sheet seperatly (that has validation) ' All ranges that have been set seperatly with Data Validation needs to be unioned ' seperatly. ' e.g. if A2:A20 and B2:B20 both have their own validation, ValidationRange = A2:A20,B2:B20. Dim rngVal As Range On Error GoTo err If Not Sh.ProtectionMode And Sh.ProtectContents Then Sh.Protect "", , , , True End If On Error Resume Next Set rngVal = Sh.Range("ValidationRange") If err.Number < 0 Then Exit Sub On Error GoTo err If HasValidation(rngVal) Then Exit Sub Else Application.EnableEvents = False Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical Application.EnableEvents = True End If Exit Sub err: MsgBox err.Description, , err.Source, err.HelpContext, err.HelpContext End Sub Public Function HasValidation(r As Range) As Boolean ' needs to be in code module ' Returns True if every cell in Range r uses Data Validation ' The validation.type will raise an error when de range contains validations that where not set ' in one action (even if the type is the same). We assume the range to be split per validation set Dim rngarea As Range Dim x As Integer On Error Resume Next HasValidation = True For Each rngarea In r.Areas x = rngarea.Validation.Type If err.Number < 0 Then HasValidation = False Exit For End If Next rngarea End Function\ DM Unseen |
#3
|
|||
|
|||
Thanks for the reply!
I do not want to disable copy/paste of cells that have validation, which it seems this code will do. I understand that indirect will not work for validation, is there a work around? My validation is: Range: d22:IV22 d25:IV25 d28:IV28 Validation: =AND($C$22=E$8+1,$C$19<=E$9) Range: D30:IV30 D33:IV33 D36:IV36 Validation =AND($C$30=E$8+1,$C$19<=E$9) This pattern repeats on down the sheet several more times. Thanks for all replies "DM Unseen" wrote: VBA can do this: The first is an event module for your worksheet, the second needs to go into a standard code module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but works for whole workbook ' and works with different types of validation ' Does the validation range still have validation? ' requires sheet level ValidationRange for each sheet seperatly (that has validation) ' All ranges that have been set seperatly with Data Validation needs to be unioned ' seperatly. ' e.g. if A2:A20 and B2:B20 both have their own validation, ValidationRange = A2:A20,B2:B20. Dim rngVal As Range On Error GoTo err If Not Sh.ProtectionMode And Sh.ProtectContents Then Sh.Protect "", , , , True End If On Error Resume Next Set rngVal = Sh.Range("ValidationRange") If err.Number < 0 Then Exit Sub On Error GoTo err If HasValidation(rngVal) Then Exit Sub Else Application.EnableEvents = False Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical Application.EnableEvents = True End If Exit Sub err: MsgBox err.Description, , err.Source, err.HelpContext, err.HelpContext End Sub Public Function HasValidation(r As Range) As Boolean ' needs to be in code module ' Returns True if every cell in Range r uses Data Validation ' The validation.type will raise an error when de range contains validations that where not set ' in one action (even if the type is the same). We assume the range to be split per validation set Dim rngarea As Range Dim x As Integer On Error Resume Next HasValidation = True For Each rngarea In r.Areas x = rngarea.Validation.Type If err.Number < 0 Then HasValidation = False Exit For End If Next rngarea End Function\ DM Unseen |
#4
|
|||
|
|||
AI,
The code will block Paste(ALL) but not e.g. Paste Special - Values since that will not overwrite Validation. The code does *not* block copying and pasting, just *validation destroying* copying and pasting (e.g. pasting All/Validation). DM Unseen |
#5
|
|||
|
|||
DM Unseen I have been having problems with this issue for a long time. I tried implementing you code on a test sheet but to no avail. Any help would be appreciated. Regards +-------------------------------------------------------------------+ |Filename: Validation.zip | |Download: http://www.excelforum.com/attachment.php?postid=3823 | +-------------------------------------------------------------------+ -- safflenow ------------------------------------------------------------------------ safflenow's Profile: http://www.excelforum.com/member.php...o&userid=27256 View this thread: http://www.excelforum.com/showthread...hreadid=441574 |
#6
|
|||
|
|||
safflenow
looked into the code: sorry, I made the code to go into the Thisworkbook module: The following code can be used in a sheetmodule: Private Sub Worksheet_Change(ByVal Target As Range) ' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but works for whole workbook ' and works with different types of validation ' Does the validation range still have validation? ' requires sheet level ValidationRange for each sheet seperatly (that has validation) ' All ranges that have been set seperatly with Data Validation needs to be unioned ' seperatly. ' e.g. if A2:A20 and B2:B20 both have their own validation,ValidationRange = A2:A20,B2:B20. Dim rngVal As Range On Error GoTo err 'If Not Sh.ProtectionMode And Sh.ProtectContents Then 'Sh.Protect "", , , , True 'End If On Error Resume Next Set rngVal = Me.Range("ValidationRange") If err.Number < 0 Then Exit Sub On Error GoTo err If HasValidation(rngVal) Then Exit Sub Else Application.EnableEvents = False Application.Undo MsgBox "Your last operation was canceled." & _ "It would have deleted data validation rules.", vbCritical Application.EnableEvents = True End If Exit Sub err: MsgBox err.Description, , err.Source, err.HelpContext, err.HelpContext End Sub The rest is all OK on your example DM Unseen |
#7
|
|||
|
|||
DM Unseen That's perfect! You have made life infinitely easier for me. Regards -- safflenow ------------------------------------------------------------------------ safflenow's Profile: http://www.excelforum.com/member.php...o&userid=27256 View this thread: http://www.excelforum.com/showthread...hreadid=441574 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation lists | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
Data validation | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Using Validation List from Another Workbook with Dependent Data | Excel Worksheet Functions |