Data Validation and Macros

[Brian T]

I have a spreadsheet that tracks inspection dates and ratings. I have macro
that moves the current dates and ratings to the prior year at the end of the
inspection season. The ratings are picked from a validation list, but the
validation is lost. Is there a way to recreate the validation within the
macro?

 

[Dave Peterson]

Record a macro when you reapply the data|validation rules????

 

[Brian T]

Here is the code I have to move the dates and ratings. Recording and
inserting that code didn't work for me.


Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

end sub

 

[Dave Peterson]

First, I don't see anything that would destroy any data|validation in the
original cells.

Second, I don't see any code where you tried to apply data|validation to
anything.

Copying the cells to a new location shouldn't destroy the data|validation. In
fact, the receiving cell should inherit the same formatting--including
data|validation that the sending cell had.

Maybe the data|validation rules were already lost before the code you posted
started.

And your code (at the the first two for/next loops) isn't really moving
anything--it's copy|pasting.

 

[Brian T]

Sorry my bad, please look at this:

Here is the code I have to move the dates and ratings. Recording and
inserting that code didn't work for me.


Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

end sub

 

[Dave Peterson]

Is there a difference between this and your previous post?

 

[Brian T]

Yes there is, i added code at the bottom that is supposed to re-create the
validation drop down list.

 

[Brian T]

My bad again,

Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim NewType_1 As Range
Dim NewAgency_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")
Set NewType_1 = Range("Type1")
Set NewAgency_1 = Range("Inspector")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewType_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewAgency_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

'Removes vailidation when moving new dates

Range("I:I").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

'reset drop down lists
Range("Current_Rating1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Condition_Rating"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

 

[Dave Peterson]

I still don't see anything in the original code that would remove the
conditional formatting.

And I don't see a problem with the code that adds it back--but I didn't test
it. I have no idea what each of those ranges refer to.

My bad again,

Sub Move_Date()

'Moves current inspection date and ratings
'to previous inspection date and rating columns

'Class 1
Dim NewInsp_1 As Range
Dim NewRating_1 As Range
Dim NewSent_1 As Range
Dim NewType_1 As Range
Dim NewAgency_1 As Range
Dim myCell_1a As Range
Dim myCell_1b As Range
Dim myCell_1c As Range

Set NewInsp_1 = Range("Current_Date1")
Set NewRating_1 = Range("Current_Rating1")
Set NewSent_1 = Range("Current_Report1")
Set NewType_1 = Range("Type1")
Set NewAgency_1 = Range("Inspector")

For Each myCell_1a In NewInsp_1.Cells
If myCell_1a.Value <> "" Then
myCell_1a.Copy myCell_1a.Offset(, -2)
myCell_1a.ClearContents
End If
Next myCell_1a

For Each myCell_1b In NewRating_1.Cells
If myCell_1b.Value <> "" Then
myCell_1b.Copy myCell_1b.Offset(, -3)
myCell_1b.ClearContents
End If
Next myCell_1b

For Each myCell_1c In NewSent_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewType_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

For Each myCell_1c In NewAgency_1.Cells
If myCell_1c.Value <> "" Then
myCell_1c.ClearContents
End If
Next myCell_1c

'Removes vailidation when moving new dates

Range("I:I").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

'reset drop down lists
Range("Current_Rating1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Condition_Rating"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub