[Solved] Validation-problem

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

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


    Try


  • 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

  • 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


    .Delete

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

Participate now!

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