How to Remove the Close Button from an Excel VBA UserForm
Question details
The user wants to remove or hide the Minimize and Close buttons on a customized Excel VBA UserForm, but needs the Maximize button to remain visible and functional.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Customizing the title bar window controls of a UserForm via Windows API calls for a specific macro application interface.
- Observed behavior
- Windows architecture links the visibility of the Close button to the Maximize/Restore buttons. Completely hiding the Close button also hides the Maximize button, making standard properties ineffective.
Ensure you save a backup of your Excel workbook before executing custom Windows API calls, as incorrect memory allocations or API parameters can cause the application to crash unexpectedly.
Disable the Close Button Using Windows API Functions
Since hiding the Close button visually breaks the Maximize button, the best workaround is to disable the Close button's functionality using system menu commands.
The Windows operating system does not reliably support completely hiding the Close button while retaining the Maximize or Restore button on a UserForm. By leveraging Windows API functions, you can remove the close command from the system menu, which greys out the Close button so it cannot be clicked.
Press Alt + F11 in Excel to open the Visual Basic for Applications (VBA) editor, and double-click your UserForm in the Project Explorer.
At the very top of your UserForm code module, declare the FindWindow, GetWindowLong, SetWindowLong, GetSystemMenu, and DeleteMenu API functions.
In the UserForm_Initialize event, use GetSystemMenu to retrieve the window's menu handle, then apply DeleteMenu to remove the SC_CLOSE command.
Use the SetWindowLong function to add the WS_MAXIMIZEBOX attribute to the window style, ensuring the Maximize button remains fully visible and active.
Hide All Title-Bar Buttons and Create Custom Controls
If you must completely remove the Close button visually, hide the entire control box and draw standard CommandButtons directly onto your form.
Try WPS Office for Seamless VBA Compatibility
When dealing with complex VBA projects and custom UserForms, you need a robust and reliable spreadsheet application. WPS Office is a free, lightweight alternative to Microsoft Excel that fully supports macros, custom VBA code, and Windows API calls, allowing you to seamlessly migrate and run your existing projects.
- 1. Download and install WPS Office: Visit the official WPS Office website to download the free suite and follow the straightforward installation instructions.
- 2. Open your macro workbook: Launch WPS Spreadsheets and open your existing .xlsm file containing the custom UserForms and API scripts.
- 3. Enable Developer tools: Navigate to the Developer tab, enable macros, and access the VBA Editor to continue managing your UserForm controls seamlessly.
Frequently Asked Questions
Why does the Maximize button disappear when I hide the Close button in VBA?
In the Windows windowing architecture, the Close, Maximize, and Minimize buttons share the same title bar control array (WS_SYSMENU). Attempting to completely remove the Close button visually breaks this array, forcing the operating system to hide the remaining buttons as well.
Can I intercept the UserForm Close button click instead of using APIs?
Yes. If you simply want to prevent the user from closing the form with the 'X' button, you can use the UserForm_QueryClose event. By writing 'If CloseMode = 0 Then Cancel = True', the form will ignore clicks on the default Close button without needing complex API calls.
Will these Windows API calls work if I run my macro on a Mac?
No. Windows API functions like FindWindow, GetSystemMenu, and SetWindowLong communicate directly with the Windows Operating System kernel. If you attempt to run these macros in Excel for Mac, the code will result in runtime errors. You must use conditional compilation (#If Mac Then) to bypass these calls on macOS.
