0

I wrote a script to create a yes/no drop-down list. However, in the few tries at first, it worked perfectly. However, later on, there's an error message "Run time error 1004: Select method of Range class failed"

Sub Drop_Down_List()

Worksheets("Sheet1").Range("M2:N2851").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes,No"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
End Sub
Laura
  • 35
  • 5
  • [There is no need to Select here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Oct 07 '22 at 13:44
  • 3
    Change `With Selection.Validation` to `With Worksheets("Sheet1").Range("M2:N2851").Validation`. – BigBen Oct 07 '22 at 13:45
  • Is the sheet in discussion protected (now)? – FaneDuru Oct 07 '22 at 13:46
  • Ah yes, the sheet is protected, but I have a script that allows macros: Worksheets.Protect Password:="ABC123", UserInterfaceOnly:=True – Laura Oct 07 '22 at 14:04
  • 1
    There are some things that you can't change on a protected sheet via code, even with UserInterfaceOnly:=True. (don't know if this is documented somewhere). You will have to unprotect the sheet first. – FunThomas Oct 07 '22 at 14:34
  • 1
    You can't select a range n a sheet if that sheet is not active – Tim Williams Oct 07 '22 at 17:39
  • May I ask, is there a way that makes macro buttons, autofilter work in a protected sheet? – Laura Oct 10 '22 at 06:06

0 Answers0