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" wrote in message
...
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(lrN ewRow.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