leal72
10-08-2009, 08:16 AM
With the first code I get the 1004 error, second code works fine but I wanted to try and avoid using Select/Selection. What I'm I missing with the first one?
Dim CrvRng As Range
Set CrvRng = ActiveSheet.Range("L16")
Set RngP = ActiveSheet.Range("P1")
ActiveWorkbook.Worksheets(WsName).Names.Add Name:="CurveList", _
RefersTo:=Sheets(WsName).Range(RngP, RngP.End(xlDown))
With CrvRng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=CurveList" '<<<Debug highlights this line
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Dim RngP As Range
Set RngP = ActiveSheet.Range("P1")
ActiveWorkbook.Worksheets(WsName).Names.Add Name:="CurveList", _
RefersTo:=Sheets(WsName).Range(RngP, RngP.End(xlDown))
ActiveSheet.Range("L16").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=CurveList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Dim CrvRng As Range
Set CrvRng = ActiveSheet.Range("L16")
Set RngP = ActiveSheet.Range("P1")
ActiveWorkbook.Worksheets(WsName).Names.Add Name:="CurveList", _
RefersTo:=Sheets(WsName).Range(RngP, RngP.End(xlDown))
With CrvRng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=CurveList" '<<<Debug highlights this line
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Dim RngP As Range
Set RngP = ActiveSheet.Range("P1")
ActiveWorkbook.Worksheets(WsName).Names.Add Name:="CurveList", _
RefersTo:=Sheets(WsName).Range(RngP, RngP.End(xlDown))
ActiveSheet.Range("L16").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=CurveList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With