Method 'MacroOptions' of object'_Application' failed Runtime Error 1004

[EagleOne]

Note to the Google Search Group

There are at least two explanations for the subject error message:

Most often it occurs when one places VBA code in the ThisWorkBook
module when it should be in the General Module

Also, if you have the same-named macro in two modules of the same
VBA project AND you then attempt to execute an "Application as
object" macro like:

With Application
.MacroOptions Macro:="PswdAdd", Description:="Passwords Add", _
ShortcutKey:="A"
End With

' This will fail if you have a copy of the "PswdAdd" macro in
' another module in the same VBA application i.e. workbook.

EagleOne

 

[johnywhy]

There is another scenario when this can happen:

If the assigned-macro is in a different workbook than active workbook at the time of assignment, you'll get an error.

Eg, If MyWorkbook contains the assigned macro, and if the keyboard shortcut is assigned during the MyWorkbook Deactivate event (in ThisWorkbook module), then the active workbook will NOT be MyWorkbook.

Ie, the active workbook will not be the workbook containing assigned macro.

Solution: in the assignment, qualify the macro name with the workbook name. Eg:

Application.MacroOptions Macro:="'MyWorkbook.xlsb'!SpecialMacro", Description:="", ShortcutKey:="M"

PS, minor refinement of OP:

You CAN place the assigned macro into the ThisWorkbook module (or any worksheet module). But, for it to work, you must:

-make the assigned macro public (yes, you can put Public procedures in ThisWorkbook module), and
-qualify the assignment with the module name. Eg:

Application.MacroOptions Macro:="ThisWorkbook.SpecialMacro", ShortcutKey:="M"

PPS, you might wonder, "why would someone assign a macro when the workbook is being DE-activated?"
Answer: if you're UN-assigning the macro. Eg:

Application.MacroOptions Macro:="'MyWorkbook.xlsb'!SpecialMacro", Description:="", ShortcutKey:=""