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
|