How to add an icon to a QAT for individual workbooks instead of a group of workbooks? 1

[feipezi]

Hi,

I added an icon to QAT to call a macro in a specific workbook. As I open another workbook and close the first one, the icon will still be there. When I click it, it will call the macro in the closed workbook instead of the newly opened one. My intention is to call the macro in the newly opened workbook.

I know one of the solutions is to set up Form or ActiveX inserts to make it work. Just wondering if QAT way can handle the situation.

Thanks in advance.

 

[SkipVought]

Hi,

Your QAT macro ought to resode in your PERSONAL.XLSB workbook; it's always open.

Then that macro can run based on the ActiveWorkbook.Name.

 

[SkipVought]

Reside, your macro ought to reside...

 

[feipezi]

I thought the macros in PERSONAL.XLSB are common macros for multiple workbooks. How can it run "based on the Activeworkbook.Name"?

What I want is to set up an icon either on QAT or the ribbon to run the macros in, say, Workbook "A"; as I open Workbook "B", I want to run the macros in "B", instead of calling the macros in Workbook "A".

Please advise.

Thanks Skip.

 

[PHV]

Have a look at the difference between ThisWorkbook and ActiveWorkbook.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feipezi]

PHV,

the implication of your reply is quite far-reaching. but for a layman like me, it's hard to understand. could you be slightly more specific?

thanks.

 

[SkipVought]

The PERSONAL.XLSB opens when you load Excel. It is always open as long as Excel is running. If you have procedures in your PERSONAL.XLSB workbook, that are all available any time you need to call any of them. If any of these procedures are called, ThisWorkbook refers to the PERSONAL.XLSB workbook. However the ActiveWorkbook would refer to the workbook that is active, like Workbook A or Workbook B, whichever one you have open. So your code running in the PERSONAL.XLSB workbook, need to reference the ActiveWorkbook to determine what code is appropriate whenever you click the QAT icon.

 

[feipezi]

Skip,

thanks for elaborating the idea. It's much clearer now.

but I still have problems.

Please see the attachment if you can.

The situation is that if I call a macro inside the XLSB workbook, fine; or I'll get the message like in the snapshot. That being the case, I don't need the XLSB workbook in the first place. I thought I could call some macros in XLSB workbook from a different, active, regular workbook. But I can't. I guess I did something wrong. Please advise.

Thanks again.

 

[PHV]

What is Hello ?
A Public procedure defined elsewhere ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

If Hello is a macro in you PERSONAL.XLSB workbook, then

Application.Run "PERSONAL.XLSB!Hello

 

[feipezi]

Hi Skip,

It works. Thanks a lot.

No, PHV, I defined it inside .XLSB. I guess I was not supposed to call it as a regular macro. Like what Skip said, I need to tell Excel where the macro "Hello" located.