Range Name in Data Validation
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 |
Range Name in Data Validation
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 |
Range Name in Data Validation
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 |
All times are GMT +1. The time now is 05:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com