What Happens to VBA User Forms When You Convert .xls to .xlsx?

VBA UserForms are one of the most powerful features in Excel workbooks — and one of the most fragile after migration. The good news: when you convert .xls to .xlsm correctly, UserForms survive. The form structure, controls layout, and event code all migrate with the VBA project.

The less good news: forms can still break in specific ways that aren't obvious until someone tries to open one. This guide covers exactly what can fail and how to diagnose and fix each issue.

The First Thing to Know: .xlsx vs .xlsm

This trips up a lot of migrations. If you convert a macro-containing .xls file to .xlsx, everything is stripped — macros, UserForms, class modules, all of it. .xlsx is the macro-free XML format. It cannot contain VBA.

Files with UserForms must be converted to .xlsm — the macro-enabled workbook format. This is where the VBA project lives after migration.

LegacyLeaps detects the presence of VBA during scan and automatically converts macro-containing files to .xlsm rather than .xlsx. If you're using PowerShell or a cloud converter, verify you're using format constant 52 (xlOpenXMLWorkbookMacroEnabled), not 51.

What Survives the Migration

When you convert .xls to .xlsm correctly, the following UserForm elements survive intact:

• Form structure and dimensions
• Native VBA controls: TextBox, Label, CommandButton, ListBox, ComboBox, CheckBox, OptionButton, Frame, ScrollBar, SpinButton, TabStrip, MultiPage, Image
• Control property settings (captions, colors, font, tab order, etc.)
• All event procedure code (Click, Change, Initialize, etc.)
• Form-level VBA variables and procedures

If your UserForms use only native VBA controls, they will very likely work after migration with no changes needed. The main risks are in the next section.

What Can Break and Why

Issue 1: ActiveX Controls in Forms

Some older .xls workbooks embed non-native ActiveX controls in UserForms — third-party calendar pickers, ListView/TreeView controls (MSComCtl.ocx), progress bars (MSComCtl2.ocx), or data grid controls.

These controls are registered in the Windows registry as COM objects. If the OCX file isn't registered on the migration target machine, the form fails to load.

Diagnosis: Open the VBE (Alt+F11), double-click the UserForm, and look at which controls are on the canvas. Right-click a form, select Properties, and look for controls with a small triangle icon — these are COM-based controls that need external registration.

Fix options:

• Register the missing OCX: regsvr32 C:\Windows\SysWOW64\MSCOMCTL.OCX (32-bit on 64-bit Windows)
• Replace the control with a native equivalent — TreeView → custom ListBox with indentation logic, Calendar → manual date-picker with spin buttons
• LegacyLeaps audits ActiveX controls during migration and flags those that need manual replacement

Issue 2: Broken References in the VBA Project

UserForms that use features from referenced libraries (ADO, DAO, Microsoft Scripting Runtime, etc.) break if those references are marked "MISSING" after migration. References become missing when the library version changes between Office versions.

Diagnosis: In the VBE, go to Tools → References. Look for items marked "(MISSING)". Note the library name and version.

Fix: Uncheck the missing reference. Find and check the same library at the correct version for the target Office installation. Or rewrite the code to use late binding instead of early binding — this removes the reference requirement entirely:

' Early binding (requires reference) — breaks when library version changes
Dim rs As ADODB.Recordset

' Late binding (no reference needed) — survives across Office versions
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

Issue 3: 32-bit vs 64-bit API Calls in Form Code

UserForms that include Declare statements in their module (direct Windows API calls) need PtrSafe updates for 64-bit Office. This is the same issue as standard modules, but it can also appear in form code.

Typical pattern (old code):

' Breaks in 64-bit Excel
Private Declare Function GetTickCount Lib "kernel32" () As Long

Fixed:

' Works in both 32-bit and 64-bit
#If VBA7 Then
    Private Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Private Declare Function GetTickCount Lib "kernel32" () As Long
#End If

LegacyLeaps scans all VBA modules — including UserForm code modules — for Declare statements and updates them for 64-bit compatibility during migration.

Issue 4: Form Initialization Code That Depends on Sheet Structure

Many UserForms populate ListBoxes, ComboBoxes, or other controls from sheet ranges in their UserForm_Initialize event. If the migration changed sheet names, range addresses, or named range definitions, the initialization code silently fails — the form opens but controls are empty.

Diagnosis: Add error handling to the Initialize event temporarily, or step through it in the VBE debugger to see where population fails.

Fix: Update range references in the Initialize event to match the new sheet/range structure. Use named ranges instead of cell addresses where possible — they're more resilient to structural changes.

Post-Migration Form Testing Checklist

Run this checklist for every workbook with UserForms after migration:

1. Open the workbook without errors — no "could not load object" dialogs on open
2. Open each form via VBE — double-click in the Project Explorer, confirm the designer opens without errors
3. Run the form from the workbook — trigger it from its normal call path (button, menu, Auto_Open)
4. Check all controls populate — ListBoxes, ComboBoxes, and other dynamic controls should have data
5. Test primary workflow — complete the form's normal workflow (enter data, click OK/Submit/Process)
6. Verify the form's output — check that values written to cells, databases, or files are correct
7. Check error handling — test invalid input paths to confirm error handling still works

Auditing Forms Before Migration

The best time to identify UserForm risks is before migration, not after. The VBE immediate window can list all forms and their control types:

Sub AuditUserForms()
    Dim vbComp As VBIDE.VBComponent
    Dim ctrl As MSForms.Control
    Dim output As String

    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        If vbComp.Type = 3 Then ' vbext_ct_MSForm
            output = output & vbComp.Name & ":" & vbCrLf
            For Each ctrl In vbComp.Designer.Controls
                output = output & "  " & ctrl.Name & " (" & TypeName(ctrl) & ")" & vbCrLf
            Next ctrl
        End If
    Next vbComp

    MsgBox output
End Sub

This lists every form and control type. Any control that shows a TypeName that isn't a standard MSForms type (TextBox, Label, CommandButton, etc.) is likely a COM control that needs attention.

Related Reading

• How to Convert .xls to .xlsx Without Losing Macros
• Excel VBA Audit Before Migration
• Excel ActiveX Controls and Windows 11 Compatibility
• VBA Macros Stopped Working — Complete Troubleshooting Guide

Part of the Complete Guide to Legacy Excel Migration.

← Back to all posts