0

I am getting an error on an excel macro that has been used in 2010 extensively. In Excel 2013, every other time I run the macro it gets a 438 error (Object does not support this property or method).

If I stop the code at the "WITH" statement and then force execution of the SELECT statement manually a second time i do not get the error.

Does anyone have a clue what could be causing this?

   ActiveSheet.Shapes("Drop Down 28").Select
   With Selection
       .ListFillRange = "Constants!$J$2:$J$13"
       .LinkedCell = "$E$2"
       .DropDownLines = 12
       .Display3DShading = False
   End With

Thanks in advance for any ideas.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Bryndwl
  • 3
  • 3
  • 2
    What's the *text* of the error message? – Tim Williams Mar 03 '14 at 20:34
  • "Object doesn't support this property or method" – Bryndwl Mar 03 '14 at 21:18
  • 1
    My psychic powers tell me your `Selection` transmutes at will because it's "what is currently selected", not "what you selected last with `.Select`. Try [not using `Select`](http://stackoverflow.com/q/10714251/11683). – GSerg Mar 03 '14 at 21:23
  • I would be interested if anyone has a further explanation why the code above would fail every other time and the code below would not...but it is now fixed with the explicit reference suggested below so many thanks! Works every time again. I will try to avoid selection references in the future if I don't need to use them. This is something I picked up by learning VBA through recording macros. – Bryndwl Mar 03 '14 at 22:45

1 Answers1

2
With ActiveSheet.DropDowns("Drop Down 28")
        .ListFillRange = "A1:A10"
        .LinkedCell = "A12"
        .DropDownLines = 10
        .Display3DShading = False
End With
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • This worked perfectly. I am not certain why the selection code above was not working as well with Excel 2013...and it was interesting that it actually would work or not work in alternating tries. The difference I noted between the executions that worked and those that did not was that when it failed with the error the first time, the hidden spreadsheet "Contacts" was exposed at that point. A subsequent execution would work, and then re-hide the sheet. The next execution would fail and leave it exposed again, and so on. But the explicit change above fixed the problem. Thanks for the help. – Bryndwl Mar 03 '14 at 22:39