Eric Penfold
Active Member
- Joined
- Nov 19, 2021
- Messages
- 424
- Office Version
- 365
- Platform
- Windows
- Mobile
Trying to create a dropdown list but it`s saying Run Time Error 1004
VBA Code:
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