[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
    Names.Add Name:="Ruter", RefersTo:="=Rutetider!$AQ$2:$AQ$" & i + 1
    Unprotect Password:="xxx"
    ‘ 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



  • Hi Woody


  • 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.


  • Woody, if you use

    Me.Protect Password:="xxx", userinterfaceonly:=True

    You will not need to unprotect. Just place it as the first line of code.

    The diff with my code and yours, other than the above is


  • 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?


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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!