Changing a command button by VBA

[IcarusHallsorts]

Is it possible to have a single button on an Excel worksheet for sorting a range alternatively by two different columns, say Reference and Name?

Ideally, I would like the button to read "Sort by Reference", then when clicked, a macro would sort the data by reference number, and then change the button to read "Sort by Name". When clicking the button in this mode, the data would be resorted, and then the button's properties would change back to how it was.

What code needs to sit before and after the code which does the sorting?

All help and pointers would be much appreciated.

 

[combo]

You need to store sorted column indicator somewhere (button's caption, name, or cell in worksheet). All can be processed in single procedure assigned to button, executed after clicking it: read settings, set new setting rule, change button's caption.

Wouldn't be more readible to have 'Sort' button with two (or more) option buttons 'by Reference' and 'by Name'? In both solutions the details depend on control type you plan to use: activex of forms command button.

combo

 

[strongm]

>Sort' button with two (or more) option buttons 'by Reference' and 'by Name'?

Yep, that's how I'd likely do it as well

 

[Andrzejek]

I would assume the initial sort of the data in your Excel worksheet would be 'By Name'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

Hi,

On the beginning I would try something like this:

Create a module with the following code.
Insert one form button and assign to it the subroutine btn_on_click()

Option Explicit

Public Const sort_by_reference_text As String = "Sort by Reference"
Public Const sort_by_name_text As String = "Sort by Name"

Private Sub auto_open()
    'run automatically when Excel starts and set the initial button text
    ActiveSheet.Buttons(1).Caption = sort_by_reference_text
End Sub

Private Sub sort_by_name()
    MsgBox ("Sorting by Name done.")
End Sub

Private Sub sort_by_reference()
    MsgBox ("Sorting by Reference done.")
End Sub

Sub btn_on_click()
    If ActiveSheet.Buttons(1).Caption = sort_by_reference_text Then
        'run specific sorting and then change the button text
        Call sort_by_reference
        ActiveSheet.Buttons(1).Caption = sort_by_name_text
    Else
        'run specific sorting and then change the button text
        Call sort_by_name
        ActiveSheet.Buttons(1).Caption = sort_by_reference_text
    End If
End Sub

 

[Andrzejek]

To built on mikrom's approach...

Starting with:

Option Explicit

Public Const sort_by_reference_text As String = "Sort by Reference"
Public Const sort_by_name_text As String = "Sort by Name"
Dim strEndOfRange As String

Private Sub auto_open()[green]
    'run automatically when Excel starts and set the initial button text[/green]
    ActiveSheet.Buttons(1).Caption = sort_by_reference_text[green]
    'Last Column[/green]
    strEndOfRange = Split(Columns(Cells(1, Columns.Count).End(xlToLeft).Column).Address(False, False), ":")(0)[green]
    'LastRow[/green]
    strEndOfRange = strEndOfRange & Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Sub btn_on_click()[green]
'run specific sorting and then change the button text[/green]

With ActiveSheet.Buttons(1)
    If .Caption = sort_by_reference_text Then
        Call MySort("[red]C[/red]")
        .Caption = sort_by_name_text
    Else
        Call MySort("[red]B[/red]")
        .Caption = sort_by_reference_text
    End If
End With

End Sub

Private Sub MySort(ByRef strCol As String)

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=Range(strCol & "2:" & strCol & "4") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:" & strEndOfRange)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Your Columns and Rows references may be different.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

I've done this just using shapes on the sheet and assigning macros to the shapes.

 

[Andrzejek]

There’s more than one way to skin a cat
And why would you want to 'skin the cat'...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mikrom]

It looks like OP has now got the complete solution. Let's see if he's still interested.