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