Data Validation in code...

[Theresa Smallwood]

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

 

[Bob Phillips]

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]

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