How to Fix Excel VBA CommandButton Sort Criteria Mismatches

In Excel VBA, the button is meant to cycle through Fiber, Distance, and Stat, then pass the selected caption into a sorting routine. The sort logic itself is valid, but the procedure call fails because the arguments sent to SortData do not match the procedure definition.

CommandButton with revolving caption passing criteria to case statement sort field

What the button should do

• Read the current caption.
• Move to the next criterion in the array.
• Update the CommandButton caption.
• Sort the active sheet by the matching column.

Criteria in the array

The line SortData SortCriteria(NextIndex - 1) triggers a ByRef argument type mismatch because SortData is declared with two parameters, not one.

Why the call fails

The click macro sends only the selected criterion, but the procedure expects both a worksheet object and a string. Because the signature and the call do not line up, Excel VBA raises the mismatch before the sort can run.

This passes one value only: the selected text from the array.

This expects two arguments: ws and criteria.

Use one of these exact fixes

• Option 1: keep the existing procedure signature and call it as SortData ActiveSheet, CStr(SortCriteria(NextIndex - 1)).
• Option 2: remove the worksheet parameter from the procedure if it always uses ActiveSheet internally.
• Best fit for this code: pass both arguments, because the current procedure already declares them.

How the sort routine maps each caption

The Select Case block converts the button caption into a column number, then sorts the range A3:Q through the last detected row. The source mapping should stay exactly aligned with the caption text.

The comment for Stat says Column E, but the code uses 8, which is Column H. The numeric value controls the actual sort.

The macro finds the last used row within A3:Q1000 and then sorts A3:Q[lastRow] with .Header = xlNo.

What is not supported here

There is no source-backed evidence for a built-in Excel control that auto-rotates captions. The caption change must be handled by VBA exactly as shown.

Match the SortData arguments and the revolving button works as intended

The real break point is the procedure call, not the sort logic. Pass both ActiveSheet and the selected caption value, then verify that each caption change triggers the correct column sort for Fiber, Distance, and Stat.

Use WPS Office as a Free Microsoft Office Alternative

Use Word, Excel, and PPT for FREE

Free Download

WPS Office cannot guarantee that Microsoft Excel VBA and ActiveX CommandButton code will run unchanged. Complete that Microsoft-specific step in the original Microsoft application or account.

For everyday local files, WPS Office combines Writer, Spreadsheets, Presentation, PDF editing, and WPS AI in a free, lightweight interface. Test macros, add-ins, protected files, and cloud-only integrations before replacing a critical workflow.

Fix Excel VBA CommandButton Sort Criteria Mismatches FAQs

Should the SortData procedure keep the worksheet parameter if it already sets ws = ActiveSheet?

It can work either way, but the current code already declares ws As Worksheet . The least disruptive fix is to pass ActiveSheet in the call and keep the procedure signature consistent.

Why does the Stat case comment not match the actual sorted column?

The code uses SortColumn = 8 , which points to column H. If the comment says column E, the comment is wrong; Excel sorts by the numeric column value, not the note beside it.

Should SortData keep the worksheet parameter if the code always uses ActiveSheet?

No, not for this version of the macro. If you always set ws = ActiveSheet inside the procedure, remove the worksheet parameter so the call and declaration stay consistent.

How can I verify the revolving caption and sort field both work after the fix?

Click the button repeatedly and confirm the caption rotates through Fiber, Distance, and Stat. After each click, check that the rows in A3:Q[lastRow] reorder by columns C, G, and H in the same sequence.