VBA & Macro Problems

How to Create Dynamic Excel VBA Code for Sorting a Table with ActiveX Buttons

Elise Williams Sep 30, 2026 868 views

Question details

The user needs VBA code to dynamically sort an entire Excel table using four ActiveX command buttons, accounting for rows added or removed starting from row 8.

How to Create Dynamic Excel VBA Code for Sorting a Table with ActiveX Buttons

Product: Excel
Device & OS: not provided
Scenario: Developing a dynamic spreadsheet where users can click ActiveX buttons to sort data by different columns, even as the dataset size changes.
Observed behavior: The user needs a shared sorting procedure that references the table's current region to ensure newly added or removed rows are correctly included in the sort without encountering compile errors.

Before you start

Ensure that the Developer tab is enabled in your ribbon to access ActiveX controls, and remember to save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve your VBA code.

Solution 1Recommended

Implement a Shared Sorting Procedure Using CurrentRegion

Create a single sorting subroutine that dynamically adjusts to the table's size using the CurrentRegion property, triggered by individual ActiveX button click events.

Instead of writing redundant sorting code for each button, you can create one shared procedure (SortOn) that accepts the column letter and sort order. The Range.CurrentRegion property ensures that the code dynamically selects the entire contiguous dataset, perfectly handling any rows added or removed later.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Navigate to the specific worksheet module

In the Project Explorer pane on the left, double-click the specific worksheet module where your ActiveX buttons are located (e.g., Sheet1). ActiveX code must reside in the worksheet module, not a standard module.

Insert the shared sorting procedure

Paste the following shared subroutine code to define the dynamic sort: Private Sub SortOn(col As String, ord As XlSortOrder) Range("A7").CurrentRegion.Sort Key1:=Cells(7, col), Order1:=ord, Header:=xlYes End Sub

Add the ActiveX button click events

Add the click events for each button to call the shared procedure. For example: Private Sub cmdSortCustomer_Click() SortOn "C", xlAscending End Sub. Repeat this for cmdSortDueDate, cmdSortFP, and cmdSortOrder, updating the column letter and XlSortOrder accordingly.

Test the ActiveX buttons

Close the VBA editor, navigate back to your worksheet, and turn off 'Design Mode' in the Developer tab. Click your ActiveX buttons to verify the table sorts dynamically across all rows.

Implement a Shared Sorting Procedure Using CurrentRegion

Avoid Compile Errors: Ensure that the header row reference (e.g., row 7) and button names (like cmdSortDueDate) exactly match the layout and ActiveX control properties in your actual worksheet.

Advanced Data Management

Sort Data Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools, including advanced sorting, filtering, and excellent compatibility with Microsoft Excel's VBA macros (in supported versions). You can build and run dynamic VBA scripts seamlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your .xlsm workbook.
2. Access Developer Tools: Navigate to the Developer tab to insert ActiveX buttons and open the built-in VBA Editor.
3. Run Dynamic Macros: Execute your dynamic sorting macros quickly and efficiently, exactly as you would in Microsoft Excel.

Full compatibility with Microsoft Excel .xlsx and .xlsm formatsBuilt-in Developer tools for macro recording and VBA editingLightweight application with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a compile error when clicking the ActiveX button?

A compile error usually occurs if the button name in the VBA code doesn't exactly match the Name property of the ActiveX control on the worksheet, or if there is a syntax error in the macro. Check the Properties window of your ActiveX button to ensure the (Name) field matches your code.

How does CurrentRegion work in Excel VBA?

The CurrentRegion property selects a range bounded by any combination of blank rows and blank columns. It functions similarly to pressing Ctrl+A inside a data block, making it ideal for dynamic tables where rows are frequently added or removed.

Where should I place the VBA code for ActiveX buttons?

Code for ActiveX button click events must be placed directly in the specific Worksheet module (e.g., Sheet1) where the buttons are located, rather than in a standard Module (like Module1). Placing it in a standard module will prevent the click events from triggering.

Excel & Data Problems