VBA & Macro Problems

How to Display AM/PM Times in Excel VBA Text Boxes

Muhammad Talha Oct 10, 2026 868 views

Question details

The user wants to display time values in Excel VBA UserForm text boxes using a 12-hour AM/PM format.

How to Display AM/PM Times in Excel VBA Text Boxes

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Assigning time values from spreadsheet cells into VBA UserForm text boxes during initialization.
Observed behavior: The UserForm text boxes display the time in a 24-hour format (e.g., 13:00) by default, rather than the desired 12-hour format (e.g., 1:00 PM).

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have access to the VBA Editor via the Developer tab.

Solution 1Recommended

Use the VBA Format Function in the Initialization Event

The most reliable way to enforce the 12-hour AM/PM time format is to use the VBA Format function when assigning cell values to the text box.

By default, VBA reads the underlying decimal value of a time cell and applies standard system formatting, which often defaults to a 24-hour clock. Wrapping the cell value in the VBA Format function allows you to explicitly define how the time should appear in the UserForm.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor in your spreadsheet.

Access UserForm Code

In the Project Explorer on the left, right-click your UserForm and select 'View Code'.

Apply the Format Function

Locate the UserForm_Initialize() procedure and assign the formatted cell value to your text boxes. Use this syntax: Me.txtStart = Format(Range("B10").Value, "h:mm AM/PM")

Run the UserForm

Save your code and run the UserForm. Your text boxes will now display times like 9:00 AM and 1:00 PM instead of 9:00 and 13:00.

Use the VBA Format Function in the Initialization Event

Pro Tip: Make sure your text box names in the code (e.g., txtStart, txtEnd) exactly match the Name property of the text boxes in your UserForm.

Solution 2

Use the .Text Property to Mirror Cell Formatting

If your worksheet cells are already formatted to display 12-hour AM/PM times, you can pull exactly what is displayed on the sheet by using the .Text property instead of .Value.

Powerful Macro Support in WPS

Handle VBA Macros and UserForms Seamlessly with WPS Office

WPS Spreadsheet offers excellent compatibility with Microsoft Excel macros, allowing you to run, edit, and format VBA UserForms without rewriting your existing code.

1. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your .xlsm file containing the UserForm.
2. Access the Developer Tools: Go to the 'Developer' tab on the top ribbon and click 'Macro Editor' (or simply press Alt + F11).
3. Edit the UserForm: Double-click your UserForm in the Project window and implement the Format(..., "h:mm AM/PM") function as needed.
4. Run and Test: Click the Run button or press F5 to launch your UserForm and verify the AM/PM time formatting displays perfectly.

Fully compatible with Microsoft Excel .xlsm and .xlsb formatsBuilt-in Macro Editor for creating and editing VBA scriptsSupports UserForm controls and properties including custom time formattingLightweight, fast, and free to download

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA text box default to a 24-hour time format?

VBA reads the underlying serial number of the time value from the spreadsheet cell. When converting this number to a string for the text box without specific formatting, it often defaults to the system's short time format, which is frequently set to a 24-hour clock.

Can I format the text box input directly as the user types?

Yes. You can use the text box's 'AfterUpdate' or 'Exit' event in VBA to capture what the user typed and instantly reformat it. For example: Private Sub txtStart_AfterUpdate() Me.txtStart = Format(Me.txtStart.Value, "h:mm AM/PM") End Sub.

What is the difference between Range("A1").Value and Range("A1").Text?

Range("A1").Value retrieves the raw data (like the decimal number representing a time or date). Range("A1").Text retrieves the exact formatted string as it appears visually in the cell on the spreadsheet.

Excel & Data Problems