Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
Record a macro when you reapply the data|validation rules????
Brian T wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
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 wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
Sorry my bad, please look at this:
"Brian T" wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
Is there a difference between this and your previous post?
Brian T wrote: Sorry my bad, please look at this: "Brian T" wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
Yes there is, i added code at the bottom that is supposed to re-create the
validation drop down list. "Dave Peterson" wrote: Is there a difference between this and your previous post? Brian T wrote: Sorry my bad, please look at this: "Brian T" wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
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" wrote: Is there a difference between this and your previous post? Brian T wrote: Sorry my bad, please look at this: "Brian T" wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation and Macros
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. Brian T wrote: 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" wrote: Is there a difference between this and your previous post? Brian T wrote: Sorry my bad, please look at this: "Brian T" wrote: 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" wrote: Record a macro when you reapply the data|validation rules???? Brian T wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Excel 2007-programming macros that apply to data validation | New Users to Excel | |||
Data Validation & Macros | Excel Discussion (Misc queries) | |||
Validation/Macros | Excel Programming | |||
Validation/Macros | Excel Programming |