What is the proper way to refer to a range name on another worksheet (same
workbook), that you want to use as a drop-down list for data validation?
I've tried everything to no avail. Here's what I have, that doesn't work
(the range name I want to use is "StartTime"). Can someone advise what I'm
doing wrong here?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("$E$13:$E$36").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Worksheets("Range Names").Range("StartTime")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry; please use the drop-down menu to select
a response."
.ShowInput = True
.ShowError = True
End With
End Sub

Use the name not the range

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=StartTime"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paige" wrote in message
...
What is the proper way to refer to a range name on another worksheet (same
workbook), that you want to use as a drop-down list for data validation?
I've tried everything to no avail. Here's what I have, that doesn't work
(the range name I want to use is "StartTime"). Can someone advise what
I'm
doing wrong here?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("$E$13:$E$36").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Worksheets("Range Names").Range("StartTime")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry; please use the drop-down menu to
select
a response."
.ShowInput = True
.ShowError = True
End With
End Sub

Well don't I feel like an idiot. Thanks, Bob, and bless you!

"Bob Phillips" wrote:

Use the name not the range

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=StartTime"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paige" wrote in message
...
What is the proper way to refer to a range name on another worksheet (same
workbook), that you want to use as a drop-down list for data validation?
I've tried everything to no avail. Here's what I have, that doesn't work
(the range name I want to use is "StartTime"). Can someone advise what
I'm
doing wrong here?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("$E$13:$E$36").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Worksheets("Range Names").Range("StartTime")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Invalid entry; please use the drop-down menu to
select
a response."
.ShowInput = True
.ShowError = True
End With
End Sub