Macro error
I've problem with the macro. Can someone help?
Sub Validation() Sheets("Negotiation Tool Report").Select Range("Q1:T1").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Criteria!$A$1:$A$7" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Thanks |
Macro error
You cannot refer a different sheet directly; instead create a named range and
then refer..Try the below macro and feedback Sub Validation() Dim ws As Worksheet Set ws = Sheets("Negotiation Tool Report") ActiveWorkbook.Names.Add Name:="myRange", RefersTo:= _ Sheets("Criteria").Range("$A$1:$A$7") ws.Range("Q1:T1").Validation.Delete ws.Range("Q1:T1").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _ Formula1:="=myrange" End Sub If this post helps click Yes --------------- Jacob Skaria "Kim" wrote: I've problem with the macro. Can someone help? Sub Validation() Sheets("Negotiation Tool Report").Select Range("Q1:T1").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Criteria!$A$1:$A$7" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub Thanks |
All times are GMT +1. The time now is 11:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com