| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
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 |
|
||
|
||||
|
|
|
| |
|
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:F7876842-6157-4C66-83BF-(E-Mail Removed)... >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 |
|
||
|
|
||||
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:F7876842-6157-4C66-83BF-(E-Mail Removed)... > >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 > > |
|
||
|
|
||||
|
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... >> >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 >> >> |
|
||
|
|
||||
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message > news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... > >> >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 > >> > >> > > |
|
||
|
|
||||
|
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:71ECBEAE-397A-439C-AD94-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... >> >> >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 >> >> >> >> >> >> |
|
||
|
|
||||
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
No... the other cells may contain a variety of data (i.e., some are
administrative data incl name, room# (may be "A" or "1" or "A1" or "1A"), bldg#, etc; other cells contain technical data, e.g., equipment#, serial#, etc. which have varying data input possibilities. "Rick Rothstein (MVP - VB)" wrote: > 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" <(E-Mail Removed)> wrote in message > news:71ECBEAE-397A-439C-AD94-(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > >> news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... > >> > 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" <(E-Mail Removed)> wrote in message > >> >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... > >> >> >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 > >> >> > >> >> > >> > >> > > |
|
||
|
|
||||
|
Rick Rothstein \(MVP - VB\)
Guest
Posts: n/a
|
Okay, give this code a try....
Private Sub Worksheet_Change(ByVal Target As Range) Dim Answer As Long Dim rCell As Range Const Col_AM As Long = 39 On Error Resume Next Application.EnableEvents = False If Target.Row > 1 And Target.Count = 1 Then If Target.Column = 1 Then Target.Offset(0, 1).Resize(, Col_AM - 1).Validation.Delete If Target.Value <> "" Then For Each rCell In Target.Offset(0, 1).Resize(, Col_AM - 1) With Cells(1, rCell.Column).Validation rCell.Validation.Add Type:=xlValidateList, _ Formula1:=.Formula1, AlertStyle:=.AlertStyle rCell.Validation.ErrorTitle = .ErrorTitle rCell.Validation.ErrorMessage = .ErrorMessage rCell.Validation.InputTitle = .InputTitle rCell.Validation.InputMessage = .InputMessage End With Next Else Answer = MsgBox("Do you want to clear the data in this row?", _ vbQuestion Or vbYesNo Or vbDefaultButton2, "Clear Data?") If Answer = vbYes Then Target.EntireRow.Clear End If ElseIf Target.Column >= 2 And Target.Column <= Col_AM And _ Target.Offset(0, 1 - Target.Column).Value = "" Then MsgBox "Put something in " & Target.Offset(0, 1 - _ Target.Column).Address & " first." Target.Clear Target.Select End If End If Application.EnableEvents = True End Sub Rick "Suzanne" <(E-Mail Removed)> wrote in message news:A24D00F3-2E65-4B5D-8672-(E-Mail Removed)... > No... the other cells may contain a variety of data (i.e., some are > administrative data incl name, room# (may be "A" or "1" or "A1" or "1A"), > bldg#, etc; other cells contain technical data, e.g., equipment#, serial#, > etc. which have varying data input possibilities. > > "Rick Rothstein (MVP - VB)" wrote: > >> 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" <(E-Mail Removed)> wrote in message >> news:71ECBEAE-397A-439C-AD94-(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> >> news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... >> >> > 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" <(E-Mail Removed)> wrote in message >> >> >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... >> >> >> >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 >> >> >> >> >> >> >> >> >> >> >> >> |
|
||
|
|
||||
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
Thanks Rick... I'll give this a try tomorrow morning... is there any chance
you can take a look at my "Validation code not working" problem??? Thanks again VERY much -- Suz "Rick Rothstein (MVP - VB)" wrote: > Okay, give this code a try.... > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim Answer As Long > Dim rCell As Range > Const Col_AM As Long = 39 > On Error Resume Next > Application.EnableEvents = False > If Target.Row > 1 And Target.Count = 1 Then > If Target.Column = 1 Then > Target.Offset(0, 1).Resize(, Col_AM - 1).Validation.Delete > If Target.Value <> "" Then > For Each rCell In Target.Offset(0, 1).Resize(, Col_AM - 1) > With Cells(1, rCell.Column).Validation > rCell.Validation.Add Type:=xlValidateList, _ > Formula1:=.Formula1, AlertStyle:=.AlertStyle > rCell.Validation.ErrorTitle = .ErrorTitle > rCell.Validation.ErrorMessage = .ErrorMessage > rCell.Validation.InputTitle = .InputTitle > rCell.Validation.InputMessage = .InputMessage > End With > Next > Else > Answer = MsgBox("Do you want to clear the data in this row?", _ > vbQuestion Or vbYesNo Or vbDefaultButton2, "Clear Data?") > If Answer = vbYes Then Target.EntireRow.Clear > End If > ElseIf Target.Column >= 2 And Target.Column <= Col_AM And _ > Target.Offset(0, 1 - Target.Column).Value = "" Then > MsgBox "Put something in " & Target.Offset(0, 1 - _ > Target.Column).Address & " first." > Target.Clear > Target.Select > End If > End If > Application.EnableEvents = True > End Sub > > > Rick > > > > > "Suzanne" <(E-Mail Removed)> wrote in message > news:A24D00F3-2E65-4B5D-8672-(E-Mail Removed)... > > No... the other cells may contain a variety of data (i.e., some are > > administrative data incl name, room# (may be "A" or "1" or "A1" or "1A"), > > bldg#, etc; other cells contain technical data, e.g., equipment#, serial#, > > etc. which have varying data input possibilities. > > > > "Rick Rothstein (MVP - VB)" wrote: > > > >> 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" <(E-Mail Removed)> wrote in message > >> news:71ECBEAE-397A-439C-AD94-(E-Mail Removed)... > >> > 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" <(E-Mail Removed)> wrote in message > >> >> news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... > >> >> > 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" <(E-Mail Removed)> wrote in message > >> >> >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... > >> >> >> >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 > >> >> >> > >> >> >> > >> >> > >> >> > >> > >> > > |
|
||
|
|
||||
|
=?Utf-8?B?U3V6YW5uZQ==?=
Guest
Posts: n/a
|
The formula didn't work as anticipated. Instead of copying down to the next
row, it copied down multiple rows, resulting #REF across the spreadsheet. "Rick Rothstein (MVP - VB)" wrote: > Okay, give this code a try.... > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim Answer As Long > Dim rCell As Range > Const Col_AM As Long = 39 > On Error Resume Next > Application.EnableEvents = False > If Target.Row > 1 And Target.Count = 1 Then > If Target.Column = 1 Then > Target.Offset(0, 1).Resize(, Col_AM - 1).Validation.Delete > If Target.Value <> "" Then > For Each rCell In Target.Offset(0, 1).Resize(, Col_AM - 1) > With Cells(1, rCell.Column).Validation > rCell.Validation.Add Type:=xlValidateList, _ > Formula1:=.Formula1, AlertStyle:=.AlertStyle > rCell.Validation.ErrorTitle = .ErrorTitle > rCell.Validation.ErrorMessage = .ErrorMessage > rCell.Validation.InputTitle = .InputTitle > rCell.Validation.InputMessage = .InputMessage > End With > Next > Else > Answer = MsgBox("Do you want to clear the data in this row?", _ > vbQuestion Or vbYesNo Or vbDefaultButton2, "Clear Data?") > If Answer = vbYes Then Target.EntireRow.Clear > End If > ElseIf Target.Column >= 2 And Target.Column <= Col_AM And _ > Target.Offset(0, 1 - Target.Column).Value = "" Then > MsgBox "Put something in " & Target.Offset(0, 1 - _ > Target.Column).Address & " first." > Target.Clear > Target.Select > End If > End If > Application.EnableEvents = True > End Sub > > > Rick > > > > > "Suzanne" <(E-Mail Removed)> wrote in message > news:A24D00F3-2E65-4B5D-8672-(E-Mail Removed)... > > No... the other cells may contain a variety of data (i.e., some are > > administrative data incl name, room# (may be "A" or "1" or "A1" or "1A"), > > bldg#, etc; other cells contain technical data, e.g., equipment#, serial#, > > etc. which have varying data input possibilities. > > > > "Rick Rothstein (MVP - VB)" wrote: > > > >> 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" <(E-Mail Removed)> wrote in message > >> news:71ECBEAE-397A-439C-AD94-(E-Mail Removed)... > >> > 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" <(E-Mail Removed)> wrote in message > >> >> news:93267DD5-B4CA-4B93-8B62-(E-Mail Removed)... > >> >> > 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" <(E-Mail Removed)> wrote in message > >> >> >> news:F7876842-6157-4C66-83BF-(E-Mail Removed)... > >> >> >> >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 > >> >> >> > >> >> >> > >> >> > >> >> > >> > >> > > |
|
||
|
|
||||
|
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Data Validation won't copy | CLR | Microsoft Excel Programming | 7 | 18th Feb 2010 07:39 PM |
| Copy Data Validation | CLR | Microsoft Excel Programming | 0 | 16th Feb 2010 08:20 PM |
| Paste validation, Copy, Paste Special Validation | Scott | Microsoft Excel Programming | 0 | 19th Dec 2006 09:54 PM |
| How to copy Validation? | Joe HM | Microsoft Excel Programming | 6 | 1st May 2005 02:38 PM |
| Copy Data validation | =?Utf-8?B?QUs=?= | Microsoft Excel Misc | 2 | 12th Oct 2004 04:03 PM |
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc. |
