[Solved] Validation-problem

Hi,

When I activate my Worksheet ‘Medarbejdere’, I get an error-message.
“Runtime error ‘1004’. Application – defined or object – defined error.”
It comes in the line
.Add Type:=…
I can’t see why it comes? And it is only sometimes the error comes! Can You?

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
Range(Cells(7, 29), Cells(46, 34)).Locked = False
‘ making the list/name ‘Ruter’
i = 2
Do Until Worksheets("Rutetider").Cells(i, 1).Value = ""
Worksheets("Rutetider").Cells(i + 2, 43).Value = Worksheets("Rutetider").Cells(i, 1).Value
i = i + 1
Loop
Names("Ruter").Delete
Names.Add Name:="Ruter", RefersTo:="=Rutetider!$AQ$2:$AQ$" & i + 1
Unprotect Password:="xxx"
Cells.Validation.Delete
‘ making the dropdownlist in the cells
For i = 7 To 46
If Cells(i, 27).Value <> "" And Cells(i, 27).Value <> "Ledig" Then
With Range(Cells(i, 29), Cells(i, 34)).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Ruter"
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
Next i
Me.Protect Password:="xxx", userinterfaceonly:=True
Application.ScreenUpdating = True

End Sub

Thanks.

Woody93

Hi,

No. If I don't Unprotect. The Validation gives an error everytime!
But should this work?

Me.Unprotect Password:="xxx"

With Range(Cells(i, 29), Cells(i, 34)).Validation

...

end with

Me.Protect Password:="xxx", DrawingObjects:=False, contents:=True, Scenarios:=True, userinterfaceonly:=True

It hasn't made any mistakes yet.
But what is the difference for the validation code? I can't see any.

Woody93

Thanks for your advise Dave, but I hav tried them, and it seem not to work that way.

With your advise (where I put in the Protection-line, remove my unprotection, and replace my cells.validation.delete with yours .delete). I will get the error-message everytime.

It seem to need the unprotection make the validation, or?

What else could be wrong? Perhaps a setup-thing?

Woody

PS. I have made an On Error Goto -&gt; MsgBox "There is a problem with ..."
But It isn't a solution.