CommandButton with Revolving Caption Passing Criteria to Case Statement Sort Field

Question

The Sorting is working, but I need to make the Sort Key change, and the CommandButton's Caption to Rotate/Revolve between the criteria. By Ref Argument Type Mismatch on this: SortData SortCriteria(NextIndex - 1)

Sub CommandButton2_Click()

        Dim CommandButton As Shape 

        Dim SortCriteria As Variant 

        Dim CurrentCaption As String 

        Dim NextIndex As Integer 

        SortCriteria = Array("Fiber", "Distance", "Stat") 

        ' Ensure the button is correctly identified 

        On Error Resume Next 

        Set CommandButton = ActiveSheet.Shapes("CommandButton2") 

        On Error GoTo 0 

        If CommandButton Is Nothing Then 

            MsgBox "CommandButton2 not found on the active sheet.", vbCritical 

            Exit Sub 

        End If 

        With CommandButton.OLEFormat.Object.Object 

            CurrentCaption = .Caption 

            NextIndex = Application.Match(CurrentCaption, SortCriteria, 0) 

            If IsError(NextIndex) Then 

                NextIndex = 1 ' Default to the first criteria if not found 

            Else 

                NextIndex = NextIndex Mod UBound(SortCriteria) + 1 

            End If 

            .Caption = SortCriteria(NextIndex - 1) 

        End With 

        SortData SortCriteria(NextIndex - 1) 

    End Sub 

    Public Sub SortData(ws As Worksheet, criteria As String) 

        Dim SortColumn As Integer 

        Dim SortOrder As XlSortOrder 

        Dim lastRow As Long 

        Set ws = ActiveSheet 

        lastRow = ws.Range("A3:Q1000").Find(What:="\*", \_ 

            After:=ws.Range("A3"), \_ 

            LookAt:=xlPart, \_ 

            LookIn:=xlFormulas, \_ 

            SearchOrder:=xlByRows, \_ 

            SearchDirection:=xlPrevious, \_ 

            MatchCase:=False).Row 

        Set SortRange = ws.Range("A3:Q" & lastRow) 

        Select Case criteria 

            Case "Fiber" 

                SortColumn = 3 ' Column C 

            Case "Distance" 

                SortColumn = 7 ' Column G 

            Case "Stat" 

                SortColumn = 8 ' Column E 

            Case Else 

                MsgBox "Invalid sort criteria.", vbCritical 

                Exit Sub 

        End Select 

        With ws.Sort 

            .SortFields.Clear 

            .SortFields.Add Key:=ws.Cells(3, SortColumn), Order:=xlAscending 

            .SetRange SortRange 

            .Header = xlNo 

            .MatchCase = False 

            .Orientation = xlTopToBottom 

            .SortMethod = xlPinYin 

            .Apply 

        End With 

    End Sub

Answer 1

Could you share a test file with some dummy data to reproduce your issue? Then I can give suggestions on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.

You can create a new thread on Stack Overflow which is special channel to handle VBA questions.

Refer to this article: Office VBA support and feedback | Microsoft Docs