How to Create Rounded Borders and Shadows on an Excel VBA UserForm
Question details
The user wants to customize the appearance of an Excel VBA UserForm by adding rounded corners and a drop shadow, ensuring the code is compatible with 64-bit Office versions.
- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Customizing the user interface of a VBA UserForm to make it look more modern and visually appealing.
- Observed behavior
- By default, VBA UserForms have standard square corners and lack modern UI elements like drop shadows. The user needs to apply custom window regions using Windows API calls without causing crashes in 64-bit environments.
Ensure you have saved your workbook as a Macro-Enabled Workbook (.xlsm) before modifying VBA code, and familiarize yourself with accessing the code-behind module of your UserForm in the VBA Editor.
Apply Rounded Corners Using Windows API Functions
Use the CreateRoundRectRgn and SetWindowRgn API functions to modify the UserForm's shape during initialization.
To change the shape of a UserForm from a standard rectangle to one with rounded corners, you must call Windows API functions. For modern 64-bit Office versions, it is crucial to include the PtrSafe keyword and use LongPtr data types in your API declarations to prevent memory errors and crashes.
Press Alt + F11 to open the Visual Basic for Applications Editor, and double-click your UserForm in the Project Explorer to view its code.
At the very top of the UserForm code module, declare the necessary API functions. Use: Private Declare PtrSafe Function CreateRoundRectRgn Lib "gdi32" (ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long, ByVal X3 As Long, ByVal Y3 As Long) As LongPtr
Below the first declaration, add: Private Declare PtrSafe Function SetWindowRgn Lib "user32" (ByVal hWnd As LongPtr, ByVal hRgn As LongPtr, ByVal bRedraw As Boolean) As Long. You will also need FindWindow to get the UserForm handle: Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
Select the 'UserForm_Initialize' event from the dropdowns at the top of the code window. Write code to find the window handle using FindWindow, create a rounded region using CreateRoundRectRgn (passing the UserForm's width and height), and apply it using SetWindowRgn.
Implement a Drop Shadow via Class Style Modification
Modify the window's class style using SetClassLongPtr to enable the default Windows drop shadow effect.
Create and Run VBA Macros Seamlessly with WPS Spreadsheet
WPS Office provides robust support for VBA macros, allowing you to create custom UserForms, automate tasks, and utilize Windows API calls just like in Microsoft Excel.
- 1. Download and Install WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your existing .xlsm file.
- 3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'Visual Basic' to open the built-in VBA Editor.
- 4. Design and Run Your UserForm: Insert or modify your UserForm, paste your Windows API code for rounded corners, and run the macro seamlessly.
Frequently Asked Questions
Why does my Excel crash when I run the API code for rounded corners?
This usually happens if you are running 64-bit Office but forgot to include the PtrSafe keyword and use LongPtr data types for handles and pointers in your Windows API declarations. Ensure your declarations are strictly updated for 64-bit compatibility.
Can I add rounded corners without using the Windows API?
No, standard VBA UserForms do not have built-in properties for rounded corners or drop shadows. The Windows API is strictly required to modify the window region at the operating system level.
Will this VBA code work on a Mac?
No, the gdi32 and user32 libraries are specific to the Windows operating system. Attempting to execute this Windows API code in Excel for Mac will result in a compilation or runtime error.
How do I remove the title bar from the UserForm to make it look like a modern popup?
You can remove the title bar by modifying the window style using the GetWindowLong and SetWindowLong API functions. You need to remove the WS_CAPTION style flag during the UserForm initialization process.
