data validation problem with vba

[mbenner1]

I am trying to use the following code snippet from MSDN.
It does not work.

Private Sub Workbook_Open()
' only the code is from MSDN not the Sub and End Sub
'statements

Range("e5").Validation _
.Modify xlValidateList, xlValidAlertStop, _
xlBetween, "=$A$1:$A$10"

End Sub

What should a valid code look like so that cell e5 get the list of numbers in A1:A10?

 

[Von Pookie]

I basically ended up with the same code by using the macro recorder; the only difference is that my recorder-generated code has

Formula1:="=$A$1:$A$10"

not just the range. Try adding the Formula1:= to your code.

 

[LEXCERM]

Hi there,

Try this:-

Private Sub Workbook_Open()

Sheets("sheet3").Select

Range("E5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

A bit long winded but it does the trick.

Regards,
PAUL.

 

[mbenner1]

Thanks

To both of you