VBA Macro Works Fine Until Applied to ActiveX Button - Solution Needed

[migos]

Essentially I have constructed a macro to update 2 pivot tables (and associated charts, on "Summary.." sheets) using data that will be periodically updated on "Activity" sheet. The macro works perfectly fine when run using the standard VBA editor Run function, also when I assign the macro to a Quick Access Toolbar at the top left of Excel. However, when I assign the macro to a command button (ActiveX) on the "Summary" sheets I suddenly receive error 1004 (See screenshots below), while the macro works perfectly fine in all other methods.
Could somebody find a solution that would allow the macro to run via the ease of clicking a simple command button (preferably a button on each of the "Summary" sheets that runs the exact same function)? Obviously, I wouldn't specifically require ActiveX Controls if you can advise an alternative. Attached is a sample example of the workbook!
Thank you in advance!

Please Login or Register  to view this content.

[MarvinP]

Hi Migos,

This sounds like a "where your code is located" type of problem. Read about Standard Modules vs other types of Modules at:

https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

It seems like your code is behind the ActiveX control so when the code is asked to select a Column, there is no column object in the control.

[migos]

Is there any way I could simply keep the macro within the workbook, and any time the workbook is opened (or saved, whatever is possible), the macro is simply run?

[MarvinP]

Sure,

Make sure the macro is in a Standard Module so it isn't limited to a single sheet or userform and then call the macro from an Event Macro behind the ThisWorkbook Module. You would call the Standard Module code when the workbook event is an Open or BeforeClose.

It is a pretty common problem for people not understanding the Scope of the code. Read also:

http://www.cpearson.com/Excel/Scope.aspx

[migos]

Thank you so much! I sorted it out!