VBA & Macro Problems

Format a VBA Date as Text Before Writing It to Excel

Phi Hung Vo Oct 15, 2026 868 views

Question details

The user wants to write a date from a VBA UserForm (TextBox) to a worksheet cell as a plain text string so that the formula bar displays the exact formatted string (e.g., '23-May') rather than the underlying regional date value.

How to Format a VBA Date as Text Before Writing to Excel

Product: Excel
Device & OS: not provided
Scenario: Transferring date inputs from a VBA UserForm TextBox to a worksheet cell while ensuring the formula bar matches the visual cell format exactly.
Observed behavior: Applying a custom number format to the cell changes its visual appearance, but the formula bar continues to display the underlying date format (e.g., '5/5/23').

Before you start

Verify that your target worksheet is unprotected and confirm the date format currently being captured by your UserForm TextBox before running the macro.

Solution 1Recommended

Use the VBA Format Function to Write the Date as Text

This is the recommended approach to force the spreadsheet to treat the incoming date as a literal text string, ensuring the formula bar and cell display identical values.

By using the VBA Format function, you can convert a date value into a literal string before it gets written to the worksheet. To ensure Excel doesn't automatically convert the formatted string back into a serial date, it is sometimes necessary to prepend an apostrophe to the string or explicitly format the destination cell as Text beforehand.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor and locate the macro or UserForm code that writes data to your worksheet.

Modify the range assignment

Update your code to wrap the TextBox value in the Format function. Use code similar to: Range("B" & NewRow).Value = Format(DateValue(TextBox1.Value), "mmm-yy")

Prevent auto-conversion (if necessary)

If the formula bar still reverts to a serial date, force it as text by prepending an apostrophe: Range("B" & NewRow).Value = "'" & Format(DateValue(TextBox1.Value), "dd-mmm-yy")

Use the VBA Format Function to Write the Date as Text

Formula Bar Matched: Because the output is now purely text, the spreadsheet will no longer store a hidden serial date value, resolving the mismatch in the formula bar.

Solution 2

Keep the Underlying Date but Apply a Custom Number Format

Use this method if you need to perform calculations on the dates later, even though the formula bar will continue to show the standard regional date.

Write and Edit VBA Macros in WPS Office

Use WPS Spreadsheet to Manage Macros and Date Formats

WPS Spreadsheet offers an excellent built-in VBA editor (in supported versions) and is highly compatible with Excel macros. You can seamlessly run your UserForm codes, format dates as text, and manage complex automated workflows.

1. Open the Developer tab: Launch WPS Spreadsheet and navigate to the 'Developer' tab on the top ribbon.
2. Access the VBA Editor: Click on 'Visual Basic' or 'Macros' to open the VBA editing environment.
3. Update your macro code: Locate your UserForm module and insert the string formatting code to convert your TextBox dates to text.
4. Run your macro: Execute the macro to seamlessly write your perfectly formatted text dates into the target worksheet cells.

Fully compatible with Microsoft Excel VBA and macro scriptsEasily format dates as text or apply custom number formats via codeLightweight, fast, and free to downloadFamiliar ribbon interface requires no learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula bar show a different date format than the cell?

Spreadsheets store dates as serial numbers. When you apply a cell format (like 'mmm-yy'), it only changes how that serial number is visually rendered in the cell grid. The formula bar always ignores visual formatting and reveals the underlying stored serial value based on your system's regional date settings.

How do I stop the spreadsheet from auto-converting my text date back to a real date?

If you pass a string like '23-May' from VBA, the software might intelligently recognize it as a date and convert it back to a serial number automatically. To strictly enforce text formatting, prepend an apostrophe in your VBA code like this: Range("A1").Value = "'" & Format(MyDate, "mmm-yy").

Can I format a VBA Date as text but still use it in formulas later?

Once a date is written purely as text to force the formula bar display, standard date calculations (like adding days) may fail. You would need to use the DATEVALUE function inside your worksheet formulas to convert the string back into a serial number for math. If calculations are frequent, it is better to use the .NumberFormat method instead.

Excel & Data Problems