Adding a validation list by code

[Philippe Pons]

Hi all,

I would like to install a validation list by code.
I get a snippet using the macro recorder.(see below)
However when I run the code, 2 problems ocur:
1- the list will not show 2 choices: YES and NO, but only one: YES;NO
2- YES is not selected, although I selected it during the recording of
the macro
Do you know how I should improve the code to have it do what I need?
TIA,
Philippe


With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:="YES;NO"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

 

[Tom Ogilvy]

Sub AA()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="YES,NO"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.Value = "Yes"
End Sub

the only way to select it is to enter that value in the cell.

 

[Philippe Pons]

Thank's Tom, I'll test it.
Philippe

Sub AA()
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="YES,NO"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.Value = "Yes"
End Sub

the only way to select it is to enter that value in the cell.