Can't Assign Macro in Excel - Solved

[KJaques]

I spent at least 30 minutes struggling with my new computer because the path name of my macros had changed. Turns out, you must manually change every item in your Excel palettes that calls a macro. That's easy, you control click on the palette icon, select 'assign macro', and choose the macro.

But although that command was not greyed-out, when I selected it, nothing would happen. There was no error message.

I found no solutions on the internet, and in the course of the 30 minutes, tried various desperate things involving new taskbars, searching for mysterious excel.xlb files, VBA scripts to build taskbars. In the course of that, I broke a bunch of things on the palette. But I found the solution, and joined this group, just so I could tell the world.

You have to have some worksheet open.

Yes, that's it. It makes no sense, but that's it.

 

[exegete77]

Glad you solved it.

But it does make sense. The script operates when certain conditions are met in the Excel. If a workbook is not open, then VBA is trying to do something that can't happen. Thus any dependent action (greyed out button attached to it) can't happen.

Then only way to get around that is to develop a major project in which you create the environment entirely in VBA and use the workbook as the display and input. Most of that kind of work is far beyond even very competent Excel/VBA users. And most of those actions can't even be done on the Mac side because it uses VB 5 (same as Office 97 on Windows) rather than VB 6 (Office 2000/2002/2003).