Restoring Data Validation rules once pasted over

**scottc_00** · 06-17-2015, 02:41 PM

I have a report that has a list of employees ID numbers in column E.
In Column J the manager will select from a Data Validation Dropdown to indicate the status of the employee (String)

The following month I will distribute a new report with all of the selections from the previous month populated in Column J.

I am preparing the report by using a macro to do a VLOOKUP from last months report using ID number from column E to get the Data Validation selection in Column J and then converting the VLOOKUP formulas to values.

As a lot of you already know, this somehow turns off my DV rules for Column J in the new report (apparently this is a known issue). Leaving column J open to any entry, which is unacceptable.

Once I realized that the users could just put anything into those cells by simply pasting it in, I added a block to my code to test last months entries against the list of acceptable entries and deleting any invalid entries after being pulled into the new report.

Next step then is to turn the DV rules back on in the new report. This is where I am stuck. I can't figure out how to turn them back on. I even tried to do it manually in the spreadsheet and I can still type anything into those cells.

Anyone know how to turn DV rules back on once the have been turned off via paste? VBA solution would be best.

I cant post my code because my company blocked the ExcelHelpForum as Social Media site, so I am posting this from my personal computer and dont have the code here. If needed I can add the logic.

Thanks

**mehmetcik** · 06-17-2015, 03:05 PM

Record yourself applying your data validation. and then copy the recorded code

Then right click on the sheet name in excel and select view code

in the window that opens there are two comboboxes

in the left combobox select worksheet

in the right corksheet select activate

a new macro will be created

paste your recorded code in that macro.

Ok now whenever you activate that worksheet the Data Validation will be refreshed.

You could also add a loop to clear any cells that do not comply at the same time.

**scottc_00** · 06-17-2015, 03:42 PM

Thanks for the response. I appreciate the help.
I really only need to reset the DV Rules once. not every time I activate the sheet, so an event procedure is probably overkill (but it also doesn't appear to work).

I did however record the code from setting the DV rules and tried adding that to my code. It runs fine but I am still able to type anything I want into the cells.
I even tried to set the DV rules manually and it acts like it is setting it (No errors) but It allows me to type anything I want.

I did notice that it added the line
With Selection.Validation
.Delete

Which looked promising but ... No Joy.

It's almost as if once I pasted into the DV cells and overwrote the rules those cells are forever blocked from DV lists.
That can't be true though, right ?

**mehmetcik** · 06-17-2015, 04:32 PM

I tried here and it works for me.

So there must me something else happening there.

Please Login or Register  to view this content.

**scottc_00** · 06-17-2015, 04:58 PM

Thanks Again...The only difference I can see is that your code contains the actual options for Data Validation (Yes, No)
Mine uses a named range (Due to issues between Excel 2007 and 2010 data validation)

Do you think that could be causing a problem ?

**scottc_00** · 06-17-2015, 05:04 PM

Also, not sure if this helps but here are my steps
I am using Vlookups in my code to populate the column (This is what is overwriting the DV cells).
I then convert the Vlookup formulas to values.
Then try to reset the DV Rules (This is where the issue is)

**scottc_00** · 06-17-2015, 05:11 PM

Here is my code

Please Login or Register  to view this content.

Sub ReconStatusUpdt()

'
' Procedure copies Recon Status from last months file to the current file
' Make sure last months file is opened and the file name is in
' cell Tables!DA3 of the current wb
'

Dim LstRow As Long
Dim rng As Range
Dim vldtnlst
Dim Test1

vldtnlst = Array("Known Exception (1)", _
"Known Exception (2)", _
"Known Exception (3)", _
"Correction in Process", _
"TBD - Requires Further Research", _
"Other")

Sheets("ABC").Select ' This is the first tab I want processed

'''Do
With ActiveSheet
LstRow = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

' Create VLOOKUP formulas in column J of all Business sheets
Set rng = Range("J3:J" & LstRow)
rng.FormulaR1C1 = _
"=IFERROR(IF(VLOOKUP(R[0]C5,INDIRECT(""["" & Tables!R3C105 & ""]"" & REPLACE(CELL(""filename"",R1C1),1,FIND(""]"",CELL(""filename"",R1C1)),"""") & ""!$E$3:J5000""),6,FALSE)>0,VLOOKUP(R[0]C5,INDIRECT(""["" & Tables!R3C105 & ""]"" & REPLACE(CELL(""filename"",R1C1),1,FIND(""]"",CELL(""filename"",R1C1)),"""") & ""!$E$3:J5000""),6,FALSE),""""),"""")"

' Convert formulas in column J to values
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

' Test validation - Delete entries that are not in the list
ActiveSheet.Range("J3").Select
Do
Test1 = UBound(Filter(vldtnlst, ActiveCell.Value))
If Test1 = -1 Then
ActiveCell.ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Row <= LstRow

' Set Data Validation
rng.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Recon_Resolution_Options"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Range("A1").Select
''' If ActiveSheet.Index <> Worksheets.Count Then
''' ActiveSheet.Next.Select
''' Else
''' Exit Do
''' End If
'''Loop

Application.CutCopyMode = False

End Sub

Please Login or Register  to view this content.

**scottc_00** · 06-17-2015, 05:17 PM

Please Login or Register  to view this content.

**scottc_00** · 06-17-2015, 05:20 PM

Sorry. Trying desperately to format the code correctly. I'll go back to read the rules

**mehmetcik** · 06-18-2015, 02:18 PM

I tried named range and had no issues there either.
I am sorry I cannot replicate your problem.

Can you try creating the same issue with the attached spreadsheet.

If you cannot then maybe your spreadsheet has become corrupted.

It might be best to rebuild it.

Please Login or Register  to view this content.

**scottc_00** · 06-18-2015, 04:49 PM

thanks again for getting back to me and for taking the time...
Just to be sure we are talking about the same thing...The cells have the drop down options available and I can choose them.
The problem is that I can type anything else in those cells as well.
Are you saying you cannot type (non accepted values) in those cells?

Restoring Data Validation rules once pasted over

LinkBack

Thread Tools

Rate This Thread

Display

Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Re: Restoring Data Validation rules once pasted over

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Produce error message when pasted values break validation rules

validation rules not working when someone copy paste data on validation cell

Protect Data Validation-able to change the validation rules

Data Validation - Ignored When Pasted

Data validation - when copied & pasted validation overlooked.

Bookmarks

Bookmarks

Posting Permissions