Please help with Dropdown list error

[Eric Penfold]

Trying to create a dropdown list but it`s saying Run Time Error 1004

Sub BO_Drop_DownList()

   Dim ws      As Worksheet
   Dim Sws     As Worksheet
   Dim Rng     As Variant
   Dim LRow    As Long
   Dim STRws   As String


   Set Sws = ThisWorkbook.Worksheets("Summary")
   Set ws = ActiveSheet
   LRow = ws.Range("J2").End(xlDown).Row
   STRws = "='ws'!J2:J" & LRow
   
   If ws.Name <> "Summary" And ws.Name <> "Trend" And ws.Name <> "Supplier BO" And ws.Name <> "Dif Depot" Then

    ws.Range("J2:J" & LRow).Select

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True

    End With

     Sws.Activate
     Sws.Range("A4:A17").Select
     ws.Activate
     With Selection.Validation
     .Add Type:=xlValidateList, _
     AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, _
     Formula1:=STRws
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "All BOReason Codes"
    .ErrorTitle = "Wrong Code"
    .InputMessage = "Input Code"
    .ErrorMessage = "Check Summary Sheet Code For Correct Code"
    .ShowInput = True
    .ShowError = True

     End With

End If

End Sub

 

[6StringJazzer]

Which line of code is giving the error?

Do you have protection turned on for ActiveSheet?

 

[rollis13]

Hi to all.

Change this line to:

STRws = "='" & ws.Name & "'!$J$2:$J$" & LRow

Note that you also need absolute reference for the range.

 

[Eric Penfold]

Thanks now sorted I had my source range wrong.

 

[rollis13]

Thanks for the positive feedback, glad we were of some help.