Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 152
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 152
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 152
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,202
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy formatting in validation cells? iamkrisc Excel Worksheet Functions 0 June 21st 07 05:43 PM
Paste validation, Copy, Paste Special Validation Scott Excel Programming 0 December 19th 06 09:54 PM
data validation and copy/paste....... MPR Excel Programming 3 July 27th 06 03:02 PM
How to copy Validation? Joe HM Excel Programming 6 May 1st 05 02:38 PM
Excel2000: How to copy data validation using VBA Arvi Laanemets Excel Programming 1 July 13th 04 12:32 PM


All times are GMT +1. The time now is 12:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"