VB - Data Validation - Range

[manoj_th]

Hi Excel Gurus,


I have created a macro for DATA Validation, in the code below there is range (Formula1:="=$F$2:$F$4"), instead of the range, I want to declare the list there itself like ("123","abcd","pokeman", etc). Request you please help me in this regard.


F2=123

F3=abcd

F4=pokeman


Sub Macro1()

With Selection.Validation

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=$F$2:$F$4"

.IgnoreBlank = True

.InCellDropdown = True

.InputTitle = ""

.ErrorTitle = ""

.InputMessage = ""

.ErrorMessage = ""

.ShowInput = True

.ShowError = True

End With

End Sub

 

[vasim]

Just replace "=$F$2:$F$4" as "123,abcd,pokeman"....within the code...

 

[manoj_th]

Its not working.......

 

[Luke M]

Just to be clear, code line should look like:

[pre]

xlBetween, Formula1:="abcd,123,pokemon"

[/pre]
If that still doesn't work, perhaps you could elaborate on what exactly is not working? (wrong thing, code errors out, something else?)

 

[manoj_th]

thank you sir,

its working fine......

 

[Hui]

xlBetween, Formula1:="=$F$2:$F$4"

should be

xlBetween, Formula1:="abcd,123,pokemon"

 

[manoj_th]

Sir,


I want the list like : "1. abc, xyz, 2542", "2. efg, ujf, 952"

separately (i.e; two selections only). Its breaking after the comma (,).

I need

"1........."

"2........."

request you to provide the solution.

 

[Hui]

Manoj_th


As I read your question, Lukes answer works.


Can you please post a sample workbook with some notes and examples of what your trying to achieve


Thanx

 

[manoj_th]

can u please provide your mail id....

 

[NARAYANK991]

Hi Manoj ,


There is no solution to this problem , since the comma is used as a list item separator.


The workaround is given in the following link :


http://www.pcreview.co.uk/forums/need-help-excel-validation-lists-t3030535.html


In brief , it is to replace all the commas in your list items by the character CHR(130).


For example , make your first item as :


"1. abc" & CHR(130) & " xyz" & CHR(130) & " 2542"


The second item will be :


"2. efg" & CHR(130) & " ujf" & CHR(130) & " 952"


The complete code will be :

[pre]

Sub Macro1()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1. abc" & Chr(130) & " xyz" & Chr(130) & " 2542,2. efg" & Chr(130) & " ujf" & Chr(130) & " 952"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

[/pre]

Narayan