PDA

View Full Version : Data Validation issues



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

mdmackillop
10-08-2009, 02:55 PM
Simply


With ActiveSheet.Range("L16").Validation

Bob Phillips
10-09-2009, 04:20 AM
Worked fine for me, what error was it?

leal72
10-09-2009, 06:49 AM
Simply


With ActiveSheet.Range("L16").Validation



I had tried that, and did try it again, doesn't work for me. Still get an error. Debug highlights the row begining with [.Add]

Run-Time error '1004':
Application-defined or object-defined error

Bob Phillips
10-09-2009, 07:09 AM
I think that the sheet is protected.

leal72
10-09-2009, 07:16 AM
I think that the sheet is protected.

that piece of code is part of a larger code.

The whole thing creates a workbook, imports text files, formats each text file (now a worksheet), during this formating step is when the data validation is occuring. When I run the code using "select/selection" the worksheet it is not protected.