Macro created in All open workbooks to save in This workbook

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
669
Office Version
  1. 2019
Platform
  1. Windows
Hello Everyone,
I have created a few macros in a workbook. When I open the macro in edit mode, it displays Macros in: All Workbooks. I want to save this macro in This workbook only. How is it possible to edit the macro to show This workbook always. Manually it is possible but when I close and re open the workbook again, by default it displays Macros in: All Workbooks.and every time I have to manually select This workbook.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,551
I assume you mean the Macros button. The macros button, that you can see the macros to edit or create a new macro manually:

1624550154868.png


... shows all macros in all workbooks as default as shown below.

1624550290685.png


However, if you enter a new macro name and click on the Create button then the sub procedure will be placed in a module in the active workbook's VBAProject.

If you want to "see" the macros resides in the active workbook, then I'm afraid you'll have to select the "This Workbook" option in the "Macros in" drop down.
However, you can still see that macros are prefixed by the workbook name in the macros list, i.e. Book2!Book2Macro - except active workbook's macros are not prefixed, Book1Macro. Unfortunately, this list doesn't show the active workbook's macros at the top but ordered by the macro name by ignoring the workbook name prefix.

If I desperately needed to do this, then I would do the following as I don't know any internal or registry settings to make it work.
Create the following macro in a module in the personal macro workbook (so it will be always loaded):
VBA Code:
Sub CustomShowMacros()
    Application.SendKeys "%{F8} %a"
    Application.SendKeys "T {ENTER}"
End Sub
Then put this macro on the QAT: Customize Quick Access Toolbar -> More Commands -> Select Macros -> Select CustomShowMacros.

The macro will send necessary keystrokes to Excel, open the Macros dialog, and select the "This Workbook" item in the list.
 

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
669
Office Version
  1. 2019
Platform
  1. Windows
I assume you mean the Macros button. The macros button, that you can see the macros to edit or create a new macro manually:

View attachment 41516

... shows all macros in all workbooks as default as shown below.

View attachment 41517

However, if you enter a new macro name and click on the Create button then the sub procedure will be placed in a module in the active workbook's VBAProject.

If you want to "see" the macros resides in the active workbook, then I'm afraid you'll have to select the "This Workbook" option in the "Macros in" drop down.
However, you can still see that macros are prefixed by the workbook name in the macros list, i.e. Book2!Book2Macro - except active workbook's macros are not prefixed, Book1Macro. Unfortunately, this list doesn't show the active workbook's macros at the top but ordered by the macro name by ignoring the workbook name prefix.

If I desperately needed to do this, then I would do the following as I don't know any internal or registry settings to make it work.
Create the following macro in a module in the personal macro workbook (so it will be always loaded):
VBA Code:
Sub CustomShowMacros()
    Application.SendKeys "%{F8} %a"
    Application.SendKeys "T {ENTER}"
End Sub
Then put this macro on the QAT: Customize Quick Access Toolbar -> More Commands -> Select Macros -> Select CustomShowMacros.

The macro will send necessary keystrokes to Excel, open the Macros dialog, and select the "This Workbook" item in the list.
Thanks for the information smozgur.
 

Forum statistics

Threads
1,141,869
Messages
5,709,092
Members
421,615
Latest member
RAB29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top