| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Validation to next row
I have a spreadsheet with data validation drop-downs; the spreadsheet may
have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Validation to next row
I'm not entirely sure how you want to implement this, but the VBA statements
needed to copy the Validation List and Validation Properties from B1 to B2 would be this... With Range("B2").Validation .Delete .Add Type:=xlValidateList, _ Formula1:=Range("B1").Validation.Formula1, _ AlertStyle:=Range("B1").Validation.AlertStyle .ErrorTitle = Range("B1").Validation.ErrorTitle .ErrorMessage = Range("B1").Validation.ErrorMessage End With Rick "Suzanne" wrote in message ... I have a spreadsheet with data validation drop-downs; the spreadsheet may have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Validation to next row
My worksheet is something like this:
A B 1 NAME TYPE 2 Smith Admin 3 Jones Clerk 4 Roberts Admin 5 Adams Manager etc. COL B contains a validation drop-down This worksheet will be used for various buildings; therefore, COL A could contain 3 names or it could contain 100 names I want to avoid having empty rows and I want to ensure if users enter more than 100 names, the validation drop-downs will continue to be available. Thanks very much... Suzanne "Rick Rothstein (MVP - VB)" wrote: I'm not entirely sure how you want to implement this, but the VBA statements needed to copy the Validation List and Validation Properties from B1 to B2 would be this... With Range("B2").Validation .Delete .Add Type:=xlValidateList, _ Formula1:=Range("B1").Validation.Formula1, _ AlertStyle:=Range("B1").Validation.AlertStyle .ErrorTitle = Range("B1").Validation.ErrorTitle .ErrorMessage = Range("B1").Validation.ErrorMessage End With Rick "Suzanne" wrote in message ... I have a spreadsheet with data validation drop-downs; the spreadsheet may have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Validation to next row
Give this code a try... I think it will do what you want:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ReEnableEvents Application.EnableEvents = False If Target.Row 1 And Target.Count = 1 Then If Target.Column = 1 Then Target.Offset(0, 1).Validation.Delete If Target.Value < "" Then With Target.Offset(0, 1).Validation .Add Type:=xlValidateList, _ Formula1:=Range("$B$1").Validation.Formula1, _ AlertStyle:=Range("$B$1").Validation.AlertStyle .ErrorTitle = Range("$B$1").Validation.ErrorTitle .ErrorMessage = Range("$B$1").Validation.ErrorMessage End With End If ElseIf Target.Column = 2 And Target.Offset(0, -1).Value = "" Then MsgBox "Put something in " & Target.Offset(0, -1).Address & " first." Target.Clear Target.Select End If End If ReEnableEvents: Application.EnableEvents = True End Sub Rick "Suzanne" wrote in message ... My worksheet is something like this: A B 1 NAME TYPE 2 Smith Admin 3 Jones Clerk 4 Roberts Admin 5 Adams Manager etc. COL B contains a validation drop-down This worksheet will be used for various buildings; therefore, COL A could contain 3 names or it could contain 100 names I want to avoid having empty rows and I want to ensure if users enter more than 100 names, the validation drop-downs will continue to be available. Thanks very much... Suzanne "Rick Rothstein (MVP - VB)" wrote: I'm not entirely sure how you want to implement this, but the VBA statements needed to copy the Validation List and Validation Properties from B1 to B2 would be this... With Range("B2").Validation .Delete .Add Type:=xlValidateList, _ Formula1:=Range("B1").Validation.Formula1, _ AlertStyle:=Range("B1").Validation.AlertStyle .ErrorTitle = Range("B1").Validation.ErrorTitle .ErrorMessage = Range("B1").Validation.ErrorMessage End With Rick "Suzanne" wrote in message ... I have a spreadsheet with data validation drop-downs; the spreadsheet may have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Validation to next row
Thanks Rick... the formula appears to work for one column.
The example below was a poor example of what I have to work with... I actually have data through COL "AM" but not all columns contain validation drop-downs. Can the code below be nudged to include the entire row? "Rick Rothstein (MVP - VB)" wrote: Give this code a try... I think it will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ReEnableEvents Application.EnableEvents = False If Target.Row 1 And Target.Count = 1 Then If Target.Column = 1 Then Target.Offset(0, 1).Validation.Delete If Target.Value < "" Then With Target.Offset(0, 1).Validation .Add Type:=xlValidateList, _ Formula1:=Range("$B$1").Validation.Formula1, _ AlertStyle:=Range("$B$1").Validation.AlertStyle .ErrorTitle = Range("$B$1").Validation.ErrorTitle .ErrorMessage = Range("$B$1").Validation.ErrorMessage End With End If ElseIf Target.Column = 2 And Target.Offset(0, -1).Value = "" Then MsgBox "Put something in " & Target.Offset(0, -1).Address & " first." Target.Clear Target.Select End If End If ReEnableEvents: Application.EnableEvents = True End Sub Rick "Suzanne" wrote in message ... My worksheet is something like this: A B 1 NAME TYPE 2 Smith Admin 3 Jones Clerk 4 Roberts Admin 5 Adams Manager etc. COL B contains a validation drop-down This worksheet will be used for various buildings; therefore, COL A could contain 3 names or it could contain 100 names I want to avoid having empty rows and I want to ensure if users enter more than 100 names, the validation drop-downs will continue to be available. Thanks very much... Suzanne "Rick Rothstein (MVP - VB)" wrote: I'm not entirely sure how you want to implement this, but the VBA statements needed to copy the Validation List and Validation Properties from B1 to B2 would be this... With Range("B2").Validation .Delete .Add Type:=xlValidateList, _ Formula1:=Range("B1").Validation.Formula1, _ AlertStyle:=Range("B1").Validation.AlertStyle .ErrorTitle = Range("B1").Validation.ErrorTitle .ErrorMessage = Range("B1").Validation.ErrorMessage End With Rick "Suzanne" wrote in message ... I have a spreadsheet with data validation drop-downs; the spreadsheet may have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Validation to next row
Do or can the other columns that do not have validation drop-downs... can
they have any other kind of validation (that must be retained)? Rick "Suzanne" wrote in message ... Thanks Rick... the formula appears to work for one column. The example below was a poor example of what I have to work with... I actually have data through COL "AM" but not all columns contain validation drop-downs. Can the code below be nudged to include the entire row? "Rick Rothstein (MVP - VB)" wrote: Give this code a try... I think it will do what you want: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ReEnableEvents Application.EnableEvents = False If Target.Row 1 And Target.Count = 1 Then If Target.Column = 1 Then Target.Offset(0, 1).Validation.Delete If Target.Value < "" Then With Target.Offset(0, 1).Validation .Add Type:=xlValidateList, _ Formula1:=Range("$B$1").Validation.Formula1, _ AlertStyle:=Range("$B$1").Validation.AlertStyle .ErrorTitle = Range("$B$1").Validation.ErrorTitle .ErrorMessage = Range("$B$1").Validation.ErrorMessage End With End If ElseIf Target.Column = 2 And Target.Offset(0, -1).Value = "" Then MsgBox "Put something in " & Target.Offset(0, -1).Address & " first." Target.Clear Target.Select End If End If ReEnableEvents: Application.EnableEvents = True End Sub Rick "Suzanne" wrote in message ... My worksheet is something like this: A B 1 NAME TYPE 2 Smith Admin 3 Jones Clerk 4 Roberts Admin 5 Adams Manager etc. COL B contains a validation drop-down This worksheet will be used for various buildings; therefore, COL A could contain 3 names or it could contain 100 names I want to avoid having empty rows and I want to ensure if users enter more than 100 names, the validation drop-downs will continue to be available. Thanks very much... Suzanne "Rick Rothstein (MVP - VB)" wrote: I'm not entirely sure how you want to implement this, but the VBA statements needed to copy the Validation List and Validation Properties from B1 to B2 would be this... With Range("B2").Validation .Delete .Add Type:=xlValidateList, _ Formula1:=Range("B1").Validation.Formula1, _ AlertStyle:=Range("B1").Validation.AlertStyle .ErrorTitle = Range("B1").Validation.ErrorTitle .ErrorMessage = Range("B1").Validation.ErrorMessage End With Rick "Suzanne" wrote in message ... I have a spreadsheet with data validation drop-downs; the spreadsheet may have 2 names or may have 200 names. Need some help with code: Where A1 = Name; B1 has validation dropdown B2: If B1 < "" (if there is a name) then copy A1 validation dropdown to B2 Thanks -- Suzanne |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to copy formatting in validation cells? | Excel Worksheet Functions | |||
| Paste validation, Copy, Paste Special Validation | Excel Programming | |||
| data validation and copy/paste....... | Excel Programming | |||
| How to copy Validation? | Excel Programming | |||
| Excel2000: How to copy data validation using VBA | Excel Programming | |||
Hybrid Mode
