VBA & Macro Problems

How to Create an Excel UserForm Menu (DoCmd.AddMenu Alternative)

Maira Mehtab Sep 28, 2026 873 views

Question details

The user needs to create a custom menu in Excel VBA but is incorrectly trying to use DoCmd.AddMenu and struggling with variable initialization.

How to Create a Custom Menu in Excel VBA (Alternative to Access DoCmd.AddMenu)

Product: Microsoft Excel
Device & OS: not provided
Scenario: Rewriting an Excel VBA menu where the user mistakenly declared the menu object as a Variant and attempted to use Access-specific commands.
Observed behavior: The VBA code fails to execute because DoCmd.AddMenu is exclusive to Microsoft Access, and initializing the form incorrectly causes runtime errors.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon so you can access the Visual Basic for Applications (VBA) editor to create and manage your UserForms.

Solution 1Recommended

Create a Custom UserForm Menu in Excel VBA

Since DoCmd.AddMenu is exclusively for Microsoft Access, you must build a custom menu in Excel by creating a UserForm with a ListBox and correctly initializing it as a specific object.

In Excel VBA, custom dialogs are handled through UserForms rather than the DoCmd object. To replicate a menu structure, you can add a ListBox to a UserForm and populate it with your menu options.

Avoid declaring your menu as a generic Variant type. Declaring it explicitly using the UserForm's name ensures proper memory allocation and enables IntelliSense in the VBA editor.

Open the VBA Editor and Insert a UserForm

Press Alt + F11 to open the VBA Editor. Go to the top menu, click Insert, and select UserForm. In the Properties window on the left, change the (Name) property of the form to frmMenu.

Add a ListBox Control

From the Toolbox window, select the ListBox control and drag it onto your UserForm. In the Properties window, change the (Name) property of this ListBox to OptionsBox.

Declare the Form Object

Insert a new Standard Module (Insert > Module). Instead of declaring your variable as a Variant, declare it specifically as your UserForm type by typing: Dim Menu As frmMenu.

Initialize and Display the Menu

Add the initialization code to instantiate the form, populate the ListBox, and show the dialog. Type: Set Menu = New frmMenu, followed by Menu.Caption = "A40infobahn Diary". Add an item using Menu.OptionsBox.AddItem "A40 Admin", and display it with Menu.Show.

Create a Custom UserForm Menu in Excel VBA

Best Practice: Using the 'New' keyword during initialization creates a fresh instance of the UserForm, preventing data overlap if the macro is run multiple times during a single session.

Solution 2

Using DoCmd.AddMenu in Microsoft Access

If your actual goal is to build a menu inside a Microsoft Access database instead of Excel, you can proceed with the DoCmd.AddMenu method as intended.

Seamless VBA Integration

Create Custom VBA Menus Seamlessly with WPS Spreadsheet

WPS Spreadsheet features a built-in, robust VBA editor that is highly compatible with Microsoft Excel. You can easily build custom UserForms, write macros, and automate your workflows without needing to modify your existing VBA scripts.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the settings to enable Developer Tools, and navigate to the newly added Developer tab.
2. Launch the VBA Editor: Click the Visual Basic icon or press Alt + F11 to open the highly compatible VBA editing environment.
3. Design Your UserForm: Click Insert > UserForm, then use the built-in toolbox to add ListBoxes, Buttons, and customized menu options.
4. Execute Your Code: Write your initialization code and click the Run button to test your new interactive menu instantly.

Fully compatible with Microsoft Excel VBA macros and UserFormsLightweight architecture for fast macro execution and loadingIntuitive Developer tab for easy script management and debugging

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when using DoCmd.AddMenu in Excel VBA?

The DoCmd object and its AddMenu method are strictly part of the Microsoft Access object model. Excel VBA does not recognize this command, which is why it triggers a runtime or compilation error. You must use UserForms or Ribbon XML customization in Excel instead.

Why shouldn't I declare my Excel UserForm as a Variant?

While a Variant can technically hold any data type, declaring your UserForm explicitly by its actual name (e.g., Dim Menu As frmMenu) allows the VBA compiler to allocate memory efficiently. It also enables IntelliSense, providing you with a helpful dropdown of the form's properties and methods as you type.

Can I add drop-down menus to an Excel UserForm instead of a ListBox?

Yes. If you prefer a compact drop-down style menu rather than a flat list, you can add a ComboBox control from the Toolbox to your UserForm. You populate it using the same .AddItem method you would use for a ListBox.

Excel & Data Problems