Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation in code...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation in code...
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation in code...
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB code + Data Validation query | Excel Programming | |||
Data Validation - Code Not Recognizing Something | Excel Programming | |||
Data validation on print - need code | Excel Programming | |||
Data validation code | Excel Programming | |||
Data validation using code | Excel Programming |