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