Data Validation in code...

I have a spreadsheet that is a template that has a button on it that allows
the user to select a file to import into the spreadsheet. On each row of the
data that is imported into the template, there is one column that should use
the data validation functionality of Excel to allow the user to select from
a list of values that will exist in one of 4 hidden columns, depending on
the value of one of the other cells in the row. I have code that I thought
would add the validation to the cell, but it keeps giving me an
"Application-defined or object-defined error" on this line:

..Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
xlBetween, Formula1:=cellfmla

I got the basic code by recording a macro of adding the validation to a
cell, then modifying it to fit my needs.

sCol = lwDisplaySheet.Range(Trim(lwDisplaySheet.Cells(lrNewRow.Row,
"V").Text) & "_Codes").Address()
sCol = Mid(sCol, InStr(sCol, "$") + 1)
sCol = Mid(sCol, 1, InStr(sCol, "$") - 1)
cellfmla = "=$" & sCol & "$6" & ":$" & sCol & "$7"
With lwDisplaySheet.Cells(lrNewRow.Row, "Z").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= xlBetween, Formula1:=cellfmla
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ErrorTitle = "Select New Opportunity Code"
.ErrorMessage = "You must select one of the codes in the list."
.ShowInput = False
.ShowError = True
End With

I thought at first it had something to do with the Formula I was setting,
but even when I hard code something there, I get the same error.

Can anyone tell me what is wrong with this code, or give me an example of
adding data validation to a cell in code?

Thanks!

Theresa Smallwood

Does this do it

sCol = lwDisplaySheet.Range(Trim(lwDisplaySheet. _
Cells(lrNewRow.Row, "V").Text) & "_Codes").Address()
sCol = Mid(sCol, InStr(sCol, "$") + 1)
sCol = Mid(sCol, 1, InStr(sCol, "$") - 1)
cellfmla = "=$" & sCol & "$6" & ":$" & sCol & "$7"
With lwDisplaySheet.Cells(lrNewRow.Row, "Z").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=cellfmla
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ErrorTitle = "Select New Opportunity Code"
.ErrorMessage = "You must select one of the codes in the list."
.ShowInput = False
.ShowError = True
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Theresa Smallwood" <[email protected]> wrote in message
news:[email protected]...
> I have a spreadsheet that is a template that has a button on it that
allows
> the user to select a file to import into the spreadsheet. On each row of
the
> data that is imported into the template, there is one column that should
use
> the data validation functionality of Excel to allow the user to select
from
> a list of values that will exist in one of 4 hidden columns, depending on
> the value of one of the other cells in the row. I have code that I thought
> would add the validation to the cell, but it keeps giving me an
> "Application-defined or object-defined error" on this line:
>
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
> xlBetween, Formula1:=cellfmla
>
> I got the basic code by recording a macro of adding the validation to a
> cell, then modifying it to fit my needs.
>
> sCol =
lwDisplaySheet.Range(Trim(lwDisplaySheet.Cells(lrNewRow.Row,
> "V").Text) & "_Codes").Address()
> sCol = Mid(sCol, InStr(sCol, "$") + 1)
> sCol = Mid(sCol, 1, InStr(sCol, "$") - 1)
> cellfmla = "=$" & sCol & "$6" & ":$" & sCol & "$7"
> With lwDisplaySheet.Cells(lrNewRow.Row, "Z").Validation
> .Delete
> .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
> Operator:= xlBetween, Formula1:=cellfmla
> .IgnoreBlank = False
> .InCellDropdown = True
> .InputTitle = ""
> .InputMessage = ""
> .ErrorTitle = "Select New Opportunity Code"
> .ErrorMessage = "You must select one of the codes in the
list."
> .ShowInput = False
> .ShowError = True
> End With
>
> I thought at first it had something to do with the Formula I was setting,
> but even when I hard code something there, I get the same error.
>
> Can anyone tell me what is wrong with this code, or give me an example of
> adding data validation to a cell in code?
>
> Thanks!
>
> Theresa Smallwood
>
>

Actually, I did not see any difference between your code and mine,
except that you spread out a couple of the lines of code to go to two
lines instead of staying on one line.

What difference were you trying to post?

Theresa Smallwood


*** Sent via Developersdex http://www.developersdex.com ***

I have also tried setting the Formula1 parameter to a hard-coded list,
just to see if that might make a difference - but I still get the same
error. If anyone can help me, I would greatly appreciate it! Thanks!

Theresa

With lwDisplaySheet.Cells(lrNewRow.Row, "Z").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="LV1, LV2"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ErrorTitle = "Select New Opportunity Code"
.ErrorMessage = "You must select one of the codes in the
list."
.ShowInput = False
.ShowError = True
End With



*** Sent via Developersdex http://www.developersdex.com ***