Data Validation error Message 'application or Object Defined Error

[SJ-NY]

Hi

I am using the following code to change the data validation source list based on the value in column "B"

It was working fine and then stopped. I get the error message at the lines;
.Add Type:=xlValidateList, Formula1:="=TempDescriptions"
.Add Type:=xlValidateList, Formula1:="=PermDescription"

Can anyone spot what is causing this problem.

Thanks

Steve

Application.EnableEvents = True
Select Case Target.Column
Case 1
With Selection
.Offset(, 1).FormulaR1C1 = "=RIGHT(RC[-1],9)"
.Offset(, 2).ClearContents
.Offset(, 5).ClearContents
End With
i = Selection.Row

'this section determines which list to use based on the the value in the cell in column "B"
If Cells(i, 2).Value = "Temporary" Then
With Cells(i, 3).Validation
.Delete

' Problem occurs here
.Add Type:=xlValidateList, Formula1:="=TempDescriptions"

End With
Else
With Cells(i, 3).Validation
.Delete

' Problem occurs here
.Add Type:=xlValidateList, Formula1:="=PermDescription"

End With
End If
End Select
Application.CutCopyMode = False
End Sub

 

[vw412]

Be sure the Defined Names exist. Be sure they are Global Defined Names, not Local to a particular worksheet.

 

[SJ-NY]

Hi:
That was the first thing I checked.

I noticed the problem was not consistent. I did further testing an realized the problem only occurred when the sheet was protected. Early on I thought that was the problem and I examined each cell to make sure it was not locked.

Once I removed the protection from the sheet it worked fine.

I am not sure why when the sheet was protected this problem happened. Luckily I was able to work around this, but I would like to know why it was happening.

Again thanks for your help

Steve

 

[vw412]

It has been my experience that there are some things we try to do in VBA that will not work if Protection is turned on. When confronted with such a situation, the only way I know to figure it out is experimenting to see if that is the problem. If it is, you can put code into your procedure to turn protection off, do the changes, and turn protection back on.

There is a protection mode that allows VBA to make changes to protected cells but prevent users from doing so. Check out the UserInterfaceOnly property on the Worksheet.Protect method. Note though that UserInterfaceOnly must be set every time you open the workbook that you use it in. It is not saved when you save the workbook. Also, I don't know if it will be the solution you need, but it might. If so it will simplify what you have to do concerning Protection.

 

[SJ-NY]

Hi

Fortunately my application of worksheet protection was a little over zealous. The sheet where the problems were occurring does not need protection. Thank you for reminding me of the other method of handling this. After I complete this project I will try that method to see if it will prevent the error.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Again thank you for your help<o></o>
Steve<o></o>