Others

How to Auto Shrink Text to Fit a Report Text Box in Microsoft Access

WPS Content Manager Oct 9, 2026 868 views

Question details

The user needs to know how to automatically reduce the font size of text so that it fits within the horizontal width of a report text box in Microsoft Access.

How to Auto Shrink Text to Fit a Report Text Box in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Formatting a report where dynamic text strings occasionally exceed the pre-defined horizontal width of a text box control.
Observed behavior: Microsoft Access lacks a built-in horizontal "shrink to fit" font property, causing longer text to be truncated if it cannot wrap vertically.

Before you start

Ensure your Microsoft Access report is opened in Design View to modify control properties or add VBA code. It is highly recommended to back up your database before implementing any macro or VBA script.

Solution 1Recommended

Adjust Text Box Height using Can Grow and Can Shrink

Because Access cannot automatically shrink font size horizontally without VBA code, allowing the text box to expand vertically is the standard, built-in workaround.

Instead of shrinking the text to fit horizontally, Access offers the 'Can Grow' and 'Can Shrink' properties. When enabled, Access automatically increases the vertical height of the text box (and the surrounding report section) to wrap the text onto multiple lines, ensuring no data is cut off.

Open Report in Design View

Right-click your report in the Navigation Pane and select Design View.

Select the Text Box

Click on the text box control that contains the truncating text.

Access the Property Sheet

Press F4 on your keyboard, or click Property Sheet on the Report Design Tools ribbon.

Enable Can Grow

Navigate to the Format tab in the Property Sheet. Find the 'Can Grow' property and change its value to Yes. You may also set 'Can Shrink' to Yes if you want the box to reduce height when text is short.

Adjust Text Box Height using Can Grow and Can Shrink

Section Setting Reminder: Make sure the 'Can Grow' property of the report section (e.g., Detail section) containing your text box is also set to Yes.

Solution 2

Use VBA to Shrink Font Size Based on Text Length

If vertical expansion is not an option for your report layout, you can use a VBA script in the report's On Format event to dynamically change the font size.

Free Microsoft Office alternative

Discover WPS Office for Your Daily Document Needs

While Microsoft Access is a specialized tool for complex databases, most daily work revolves around documents, spreadsheets, and presentations. WPS Office is a fast, highly compatible, and cost-free alternative to Microsoft Office that easily handles all your standard office tasks.

1. Download the Installer: Visit the official WPS Office website and click the Free Download button.
2. Install WPS Office: Run the downloaded installation file and follow the on-screen instructions.
3. Start Creating: Launch WPS Office and immediately start opening or creating fully compatible office documents.

Free to use with a lightweight, rapid installationFully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx)Familiar tabbed interface for a seamless migration from Microsoft OfficeBuilt-in PDF editing and conversion tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Access cut off my text in Report View?

By default, Access text boxes have fixed heights and widths. If a text string is wider than the text box and the 'Can Grow' property is set to No, Access will simply truncate the overflowing text rather than expanding the control or shrinking the font.

Is there a 'Shrink to Fit' property in Access like in Excel?

No, Microsoft Access does not have a native 'Shrink to Fit' property for fonts in controls. You must rely on the 'Can Grow' vertical expansion property or write custom VBA macros to evaluate text length and dynamically adjust the FontSize property.

Where should I place the VBA code to change font size in a report?

You should place your formatting VBA code in the 'On Format' event of the specific report section (typically the Detail section) that contains your text box control. This ensures the code triggers exactly when Access is rendering that specific row of data.

Office Quick Solutions