[VBA] How to Modify and Reformat ListBox Values with a Macro

Introduction

When displaying a table from a sheet in a UserForm ListBox, you often want to format the displayed content. For example, you might want to “zero-pad numbers in a specific column to 3 digits” or “add a specific string after a product name.”

You can directly rewrite the data loaded into a ListBox using VBA. This allows you to adjust the visual presentation on the form to make it easier to understand, without changing the original data on the worksheet.

In this article, I will explain practical techniques to loop through ListBox items and modify (reformat) the displayed values directly.

VBA Sample Code: Changing ListBox Values

This macro formats the content displayed in a ListBox named DataTableBox when the “Format Execute” button is clicked.

• 1st Column (ID): Zero-pad to 4 digits (e.g., 12 → 0012).
• 2nd Column (Item Name): Add ” [Done]” to the end.
• 3rd Column (Quantity): Add comma separators for thousands (e.g., 1500 → 1,500).

UserForm Code

' Process when the "FormatButton" is clicked
Private Sub FormatButton_Click()

    '== Define variable ==
    Dim i As Long
    
    ' Specify the target ListBox (DataTableBox) using a With block
    With Me.DataTableBox
    
        '== Loop through all rows (Assuming row 0 is a header, start from row 1) ==
        For i = 1 To .ListCount - 1
        
            '--- Format 1st column (Index 0) to 4-digit zero-padding ---
            .List(i, 0) = Format(.List(i, 0), "0000")
            
            '--- Append string to 2nd column (Index 1) ---
            .List(i, 1) = .List(i, 1) & " [Done]"
            
            '--- Format 3rd column (Index 2) with comma separators ---
            .List(i, 2) = Format(.List(i, 2), "#,##0")
            
        Next i
        
    End With
    
    MsgBox "Formatted the list display.", vbInformation

End Sub

Code Explanation

For i = 1 To .ListCount - 1

This For loop is the basic structure for processing ListBox items sequentially.

• .ListCount: Returns the total number of rows in the ListBox.
• i = 1 To ...: ListBox indexes start at 0. Here, we assume the first row (Index 0) is a header row, so processing starts from the second row (Index 1). If there is no header row, use i = 0 To .ListCount - 1.

.List(i, 0) = Format(...)

This line is the core part that directly rewrites the list value.

• .List(i, 0): This is used on both the left and right sides.
  • The right side .List(i, 0) is used to get the current value (e.g., 12).
  • The left side .List(i, 0) is used to assign (set) the new formatted value (e.g., “0012”).

Format(...)

The VBA Format function converts numbers or dates into strings with a specified format.

• "0000": Formats the number with 4-digit zero padding.
• "#,##0": Formats the number with comma separators every 3 digits.

By combining the For loop with the .List(row, col) property, you can freely manipulate the data inside the ListBox.

Summary

In this article, I explained how to modify the content displayed in a UserForm ListBox using VBA.

• Use the .ListCount property to determine the number of loops.
• Use the .List(RowIndex, ColumnIndex) property to get or set values for specific cells.
• Combine this with the Format function to clean up number and date formats.

This technique is extremely effective when you want to organize the display on a form to make it user-friendly while keeping the original data on the sheet unchanged.