Hello,

Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Any cool hacks?

Thanks you.
Gap

Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1 the
active cell and that your comma delimited list is in H1, selecting Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

Rick

Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1 the
active cell and that your comma delimited list is in H1, selecting Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1 the
active cell and that your comma delimited list is in H1, selecting Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Sorry, I missed the "list" part of your question. I don't think you can do
it the way you want (with the list in a cell). You can do it if you put your
list directly in the Data/Validation dialog and maintain it there (in the
Data/Validation dialog box, select List from the Allow combo box and just
put your list... no equal sign... in the Source text box). While I haven't
looked into it yet, I am reasonably sure a macro can be developed to do what
you want... is a macro solution acceptable to you?

Rick

Here's a possible solution that uses an event macro. I'm not the best VBA
programmer so you should test this on a test file before you implement it in
your real file. It does work in my tests! All you good programmers out there
I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a change in
cell A1. I'm assuming that cells to the right of A1 are empty so they will
accept the Text to Columns data. If these cells are not empty the TTC will
overwrite them. Then you can use a dynamic range formula as the source for
the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

I'm thinking an approach like this might be easier to implement. All that is
needed is to place this code in the worksheet code window and then type in a
comma separated list into the cell designated to hold it (assumed to be H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed (specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off as
the Target range is not being being affected in any way by this macro. I'll
let others more familiar with that aspect of the macro world comment on the
need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best VBA
programmer so you should test this on a test file before you implement it
in your real file. It does work in my tests! All you good programmers out
there I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a change
in cell A1. I'm assuming that cells to the right of A1 are empty so they
will accept the Text to Columns data. If these cells are not empty the TTC
will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

or just put this one in sheet tab module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Range("C2").Validation
.Delete
.Add xlValidateList, Formula1:=Range("A1").Value
.InCellDropdown = True
End With
End Sub


"Rick Rothstein (MVP - VB)" skrev:

I'm thinking an approach like this might be easier to implement. All that is
needed is to place this code in the worksheet code window and then type in a
comma separated list into the cell designated to hold it (assumed to be H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed (specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off as
the Target range is not being being affected in any way by this macro. I'll
let others more familiar with that aspect of the macro world comment on the
need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best VBA
programmer so you should test this on a test file before you implement it
in your real file. It does work in my tests! All you good programmers out
there I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a change
in cell A1. I'm assuming that cells to the right of A1 are empty so they
will accept the Text to Columns data. If these cells are not empty the TTC
will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

No doubt, that's a better approach.

One problem, though. If you clear cell H1 then you get a 1004 run-time
error.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm thinking an approach like this might be easier to implement. All that
is needed is to place this code in the worksheet code window and then type
in a comma separated list into the cell designated to hold it (assumed to
be H1 for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed
(specified in the ValidationList constant), the above macro will change
the Data Validation List for the designated range (stored in the
ValidationRange constant). I'm not sure if EnableEvents needs to be
toggled on and off as the Target range is not being being affected in any
way by this macro. I'll let others more familiar with that aspect of the
macro world comment on the need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best VBA
programmer so you should test this on a test file before you implement it
in your real file. It does work in my tests! All you good programmers out
there I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a change
in cell A1. I'm assuming that cells to the right of A1 are empty so they
will accept the Text to Columns data. If these cells are not empty the
TTC will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post
a link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into
multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

Nice and compact but this also results in a run-time error 1004 if you clear
cell A1.

--
Biff
Microsoft Excel MVP


"excelent" wrote in message
...
or just put this one in sheet tab module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Range("C2").Validation
.Delete
.Add xlValidateList, Formula1:=Range("A1").Value
.InCellDropdown = True
End With
End Sub


"Rick Rothstein (MVP - VB)" skrev:

I'm thinking an approach like this might be easier to implement. All that
is
needed is to place this code in the worksheet code window and then type
in a
comma separated list into the cell designated to hold it (assumed to be
H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed
(specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off as
the Target range is not being being affected in any way by this macro.
I'll
let others more familiar with that aspect of the macro world comment on
the
need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best
VBA
programmer so you should test this on a test file before you implement
it
in your real file. It does work in my tests! All you good programmers
out
there I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a
change
in cell A1. I'm assuming that cells to the right of A1 are empty so
they
will accept the Text to Columns data. If these cells are not empty the
TTC
will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll
post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a
comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into
multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with
A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating
the
commas from the text following the commas. In other words, if your
list
contained "apple", "cherry" and "peach", then H1 must contain
this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

Good point! This patched code should handle that problem...

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Range(ValidationList).Value = "" Then
Range(ValidationRange).Validation.Delete
ElseIf Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Rick



"T. Valko" wrote in message
...
No doubt, that's a better approach.

One problem, though. If you clear cell H1 then you get a 1004 run-time
error.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm thinking an approach like this might be easier to implement. All that
is needed is to place this code in the worksheet code window and then
type in a comma separated list into the cell designated to hold it
(assumed to be H1 for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed
(specified in the ValidationList constant), the above macro will change
the Data Validation List for the designated range (stored in the
ValidationRange constant). I'm not sure if EnableEvents needs to be
toggled on and off as the Target range is not being being affected in any
way by this macro. I'll let others more familiar with that aspect of the
macro world comment on the need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best
VBA programmer so you should test this on a test file before you
implement it in your real file. It does work in my tests! All you good
programmers out there I would appreciate and *constructive* feedback on
this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a
change in cell A1. I'm assuming that cells to the right of A1 are empty
so they will accept the Text to Columns data. If these cells are not
empty the TTC will overwrite them. Then you can use a dynamic range
formula as the source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll post
a link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a
comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into
multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating the
commas from the text following the commas. In other words, if your
list
contained "apple", "cherry" and "peach", then H1 must contain this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop down
as a list does.

Regards
Gap

I think it "looks" compact because he left out the Error Message coding and
did not use the Const(ant) definitions that I did (which I think make the
code easier to read and easier to maintain in the future should any changes
to the code be required). Oh, and he saved an End If statement by changing
the If-Then statement to exit the subroutine rather than using it to filter
the natural fall-through. With all that said, he actually has an extra line
of code to specify the InCellDropdown assignment which seemed to be covered
automatically within the code I posted. Don't get me wrong, I am not
knocking excelent's approach, just pointing out that the compact look comes
about as a result of omissions (which are not necessarily bad in and of
themselves).

Rick


"T. Valko" wrote in message
...
Nice and compact but this also results in a run-time error 1004 if you
clear cell A1.

--
Biff
Microsoft Excel MVP


"excelent" wrote in message
...
or just put this one in sheet tab module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Range("C2").Validation
.Delete
.Add xlValidateList, Formula1:=Range("A1").Value
.InCellDropdown = True
End With
End Sub


"Rick Rothstein (MVP - VB)" skrev:

I'm thinking an approach like this might be easier to implement. All
that is
needed is to place this code in the worksheet code window and then type
in a
comma separated list into the cell designated to hold it (assumed to be
H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed
(specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off
as
the Target range is not being being affected in any way by this macro.
I'll
let others more familiar with that aspect of the macro world comment on
the
need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best
VBA
programmer so you should test this on a test file before you implement
it
in your real file. It does work in my tests! All you good programmers
out
there I would appreciate and *constructive* feedback on this approach.

Assume:

A1 = cell with comma delimited text. Like: Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a
change
in cell A1. I'm assuming that cells to the right of A1 are empty so
they
will accept the Text to Columns data. If these cells are not empty the
TTC
will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll
post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a
comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into
multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with
A1
the
active cell and that your comma delimited list is in H1, selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the comma
delimited list in H1 cannot have any "neatening" spaces separating
the
commas from the text following the commas. In other words, if your
list
contained "apple", "cherry" and "peach", then H1 must contain
this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop
down
as a list does.

Regards
Gap

I understand. It's the same considerations when writing a formula, how
robust does it need to be and when does robutness cross the line into bloat.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
I think it "looks" compact because he left out the Error Message coding and
did not use the Const(ant) definitions that I did (which I think make the
code easier to read and easier to maintain in the future should any changes
to the code be required). Oh, and he saved an End If statement by changing
the If-Then statement to exit the subroutine rather than using it to filter
the natural fall-through. With all that said, he actually has an extra line
of code to specify the InCellDropdown assignment which seemed to be covered
automatically within the code I posted. Don't get me wrong, I am not
knocking excelent's approach, just pointing out that the compact look comes
about as a result of omissions (which are not necessarily bad in and of
themselves).

Rick


"T. Valko" wrote in message
...
Nice and compact but this also results in a run-time error 1004 if you
clear cell A1.

--
Biff
Microsoft Excel MVP


"excelent" wrote in message
...
or just put this one in sheet tab module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
With Range("C2").Validation
.Delete
.Add xlValidateList, Formula1:=Range("A1").Value
.InCellDropdown = True
End With
End Sub


"Rick Rothstein (MVP - VB)" skrev:

I'm thinking an approach like this might be easier to implement. All
that is
needed is to place this code in the worksheet code window and then type
in a
comma separated list into the cell designated to hold it (assumed to be
H1
for this example).

Private Sub Worksheet_Change(ByVal Target As Range)
Const ValidationList As String = "H1"
Const ValidationRange As String = "A1:A10"
If Not Intersect(Target, Range(ValidationList)) Is Nothing Then
With Range(ValidationRange).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=Range(ValidationList).Value
.ErrorTitle = "Value error"
.ErrorMessage = "You can only choose from the list."
End With
End If
End Sub

Whenever the cell containing the comma separated list is changed
(specified
in the ValidationList constant), the above macro will change the Data
Validation List for the designated range (stored in the ValidationRange
constant). I'm not sure if EnableEvents needs to be toggled on and off
as
the Target range is not being being affected in any way by this macro.
I'll
let others more familiar with that aspect of the macro world comment on
the
need for it.

Rick



"T. Valko" wrote in message
...
Here's a possible solution that uses an event macro. I'm not the best
VBA
programmer so you should test this on a test file before you
implement it
in your real file. It does work in my tests! All you good programmers
out
there I would appreciate and *constructive* feedback on this
approach.

Assume:

A1 = cell with comma delimited text. Like:
Apples,Oranges,Grapes,Pears
A10 = data validation drop down list

The macro will execute a Text to Columns operation when there is a
change
in cell A1. I'm assuming that cells to the right of A1 are empty so
they
will accept the Text to Columns data. If these cells are not empty
the TTC
will overwrite them. Then you can use a dynamic range formula as the
source for the drop down list.

As the source for the drop down list enter this formula:

=OFFSET($A$1,,,,COUNTA($1:$1))

If you get a message that says something like: The source currently
evaluates to an error....

Just answer YES.

Right click the sheet tab and select View Code
Paste the code below into the window that opens:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.DisplayAlerts = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:IV1").ClearContents
Target.TextToColumns _
Destination:=Range("A1"), _
DataType:=xlDelimited, _
Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1))
End If
Application.DisplayAlerts = True
sub_exit:
Application.EnableEvents = True
End Sub

Hit ALT Q to return to Excel.

If you would like to see this in a sample file let me know and I'll
post a
link.

--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
On Sep 16, 12:29 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Whats the simplest way to create a data validation list from a
comma
separated text present in another cell? The text in this cell is
dynamic and keeps changing.

I can think of breaking the text by using MID, FIND, IF into
multiple
cells and then using the multiple cells as source to the Data
Validation.

Assuming for this example that your selected range is A1:A10 with
A1
the
active cell and that your comma delimited list is in H1,
selecting
Custom
from the Allow combo box and placing this formula...

=ISNUMBER(SEARCH(","&A1&",",","&$H$1&","))

in the Formula text box appears to do what you asked.

I should point out that in order for this formula to work, the
comma
delimited list in H1 cannot have any "neatening" spaces separating
the
commas from the text following the commas. In other words, if your
list
contained "apple", "cherry" and "peach", then H1 must contain
this....

H1: apple,cherry,peach

and **not** this...

H1: apple, cherry, peach

(Note the space following the commas in the "not this" example.)

Rick

Thanks Rick. That was neat.

This solves the validation problem but does not display the drop
down
as a list does.

Regards
Gap