VBA & Macro Problems

How to Use the Windows Color Picker in Microsoft Access VBA

Guest Writer Sep 28, 2026 869 views

Question details

The user needs to implement a color picker in Microsoft Access using VBA because the native Excel color picker code is not supported in Access.

How to Use the Windows Color Picker in Microsoft Access VBA

Product: Microsoft Access
Device & OS: Windows
Scenario: Adding a color selection tool to a Microsoft Access form using VBA.
Observed behavior: Microsoft Access does not expose the built-in color-picker dialog natively available in Excel, requiring developers to rely on the Windows ChooseColor API instead.

Before you start

Verify whether you are using a 32-bit or 64-bit installation of Microsoft Office, as this determines how you must declare the Windows API functions in your VBA module.

Solution 1Recommended

Implement the ChooseColor Windows API in a Standard VBA Module

Use the Windows ChooseColor API function to create a custom color selection dialog that works reliably across both 32-bit and 64-bit Microsoft Access environments.

Since Access does not have the Application.Dialogs collection found in Excel, the most reliable method for color selection is invoking the native Windows ChooseColor API. This requires defining a custom Type for the color structure and declaring the API function with appropriate PtrSafe keywords for 64-bit compatibility.

Open the VBA Editor

In Microsoft Access, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a new standard module for your API declarations.

Add the API Declarations

Define the CHOOSECOLOR structure and declare the ChooseColorA API function. Ensure you use the '#If VBA7 Then' compiler directive to include the 'PtrSafe' keyword and 'LongPtr' data types for 64-bit Office compatibility.

Create a Wrapper Function

Write a public VBA function (e.g., 'GetColorFromPicker') that initializes the CHOOSECOLOR structure, calls the API, and returns the selected Long color value. If the user cancels, handle the zero or default return value.

Call the Function from a Form Event

Open your Access form in Design View, add a command button, and attach code to its 'On Click' event to call your wrapper function and apply the returned color to a control, such as 'Me.TextBox1.BackColor = GetColorFromPicker()'.

64-bit Compatibility Required: Failing to use the PtrSafe keyword and LongPtr data types in a 64-bit Office environment will cause Microsoft Access to crash when the ChooseColor API is called.

Free Microsoft Office alternative

Try WPS Office for Seamless Data and Spreadsheet Management

While advanced Microsoft Access customizations often require complex Windows API calls, you can handle extensive data formatting, visualization, and VBA macro tasks efficiently with WPS Spreadsheet. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office.

1. Download the Installer: Visit the official WPS Office website and click 'Download WPS Office Free'.
2. Install WPS Office: Run the downloaded setup file and follow the quick on-screen instructions to install the suite on your Windows device.
3. Open Your Office Files: Launch WPS Spreadsheet or WPS Writer and open your existing Microsoft Office files seamlessly to continue your work.

Excellent compatibility with Microsoft Excel formats (.xlsx, .xls, .xlsm)Built-in support for VBA macros in advanced versionsFamiliar user interface requiring zero learning curveLightweight application that loads instantly without consuming heavy system resources

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use Application.Dialogs(xlDialogEditColor) in Access?

Microsoft Access and Microsoft Excel have completely different object models. Access does not possess the Application.Dialogs collection natively, meaning Excel-specific dialog commands will trigger an error in Access VBA.

What happens if a user clicks 'Cancel' in the Windows Color Picker?

When the user clicks 'Cancel', the ChooseColor API returns 0 (or a designated failure flag depending on your implementation). You should include a check in your VBA code to retain the original color if the function returns 0 or indicates a cancellation.

Can I save custom colors selected via the ChooseColor API?

Yes. The CHOOSECOLOR structure includes a 'lpCustColors' parameter, which points to an array of 16 Long integers. You can populate and read from this array in VBA to save custom colors for the duration of the application session.

Excel & Data Problems