VBA & Macro Problems

How to Fix Excel Macros Not Showing in the Assign Macro Dialog

Nimra Malik Oct 20, 2026 869 views

Question details

The user is unable to find their macros when attempting to assign them to a button in Excel.

How to Fix Excel Macros Not Showing in the Assign Macro Dialog

Product: Excel
Device & OS: not provided
Scenario: Trying to assign a macro to a button in a workbook, typically one using the Data Model and Power Pivot, but the macro list appears empty.
Observed behavior: The macros do not appear in the Assign Macro dialog box because they are stored in a worksheet or ThisWorkbook code module instead of a standard VBA module.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in the ribbon to access the VBA editor.

Solution 1Recommended

Move the Macro to a Standard VBA Module

Macros must be stored in a standard module and declared properly to be visible in the Assign Macro list.

Procedures stored in the workbook or worksheet code modules are considered private to those objects and cannot normally be assigned to a button.

To resolve this, you need to migrate your VBA code into a general standard module and ensure it is structured correctly.

Open the VBA Editor

Press the keyboard shortcut 'ALT + F11' to open the Visual Basic for Applications (VBA) editor.

Insert a Standard Module

In the Project Explorer pane on the left, right-click on your VBAProject, hover over 'Insert', and select 'Module'. This will create a new standard module (e.g., Module1).

Move Your Code

Locate your macro code in the Worksheet or ThisWorkbook module. Cut the code and paste it into the newly created standard module.

Verify Procedure Type

Ensure your macro is declared as a 'Public Sub' (e.g., Public Sub MyMacro()) and that it does not contain any arguments inside the parentheses.

Assign the Macro

Return to your Excel worksheet, right-click the button or shape, choose 'Assign Macro', and your macro should now be visible in the list.

Macro Visibility Rules: Only 'Public Sub' procedures without arguments placed in standard modules will automatically populate in the standard Assign Macro dialog.

Efficient Spreadsheet Editor

Create and Assign Macros Easily with WPS Spreadsheet

WPS Office provides excellent support for VBA macros in its advanced versions, allowing you to automate repetitive tasks just like in Microsoft Excel. You can seamlessly write, edit, and assign macros to buttons using a highly familiar interface.

1. Enable Developer Tools: Open WPS Spreadsheet, go to the Options menu, and enable the Developer tab to access macro features.
2. Write the Macro: Click 'Visual Basic' on the Developer tab to open the VBA editor and insert a standard Module.
3. Create a Public Sub: Write your macro as a Public Sub without arguments inside the new standard module.
4. Insert a Button: Go to the 'Insert' tab, choose 'Shapes', and draw a button or object on your worksheet.
5. Assign the Macro: Right-click the shape, select 'Assign Macro', choose your macro from the list, and click OK.

Highly compatible with Microsoft Excel files including .xlsm formatsFamiliar VBA editor interface for seamless migrationRobust formula and data processing capabilitiesLightweight installation and smooth performance

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro work perfectly in the VBA editor but won't show in the Assign Macro list?

This happens when the macro is stored in a private module (such as a Worksheet or ThisWorkbook module) or if it requires arguments. Moving it to a standard module and removing arguments makes it visible.

Can I assign a macro that requires arguments to a button?

Macros with arguments are not visible in the Assign Macro dialog. To fix this, you can write a separate 'wrapper' macro without arguments that calls the original macro and passes the required arguments to it.

Does a Private Sub appear in the macro list?

No, if a macro is declared as a 'Private Sub', it is explicitly hidden from the macro list. You must change the declaration to 'Public Sub' for it to be assignable via the dialog box.

Excel & Data Problems