Single Use Comand Button

[JamesT1]

Hi

Is it possible to have a button you can only use once.

e.g. a button to sort a list of people into alphabetial order, once used any new staff added at the bottom would not be sorted

Thanks

[broro183]

Hi James,

Yes, it's possible. The next question could be how bulletproof do you want it to be (ie do you want to stop people from manually completing a Sort as well*)?
* someone else would need to help you with this.

Here is one approach for a single push button

Private Sub CommandButton1_Click() '
ActiveSheet.Range("a5:c100").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

With CommandButton1
    .Enabled = False
    .WordWrap = True
    .Caption = "The list has been sorted & this button is now deactivated."
End With
End Sub

hth
Rob

[Leith Ross]

Hello broro183,

Adding on to Rob's code, this macro will enable/disable the Sort... menu item. It will be re-enabled when you close the workbook.

Macro to Enable/Disable the Sort Menu Selection

Sub ActivateSort(ByVal Status As Boolean)

   Dim CmdBar As CommandBar
   Dim BarCtrl As CommandBarControl
   Dim PopupBar As CommandBarPopup
   
     Set CmdBar = CommandBars("Worksheet Menu Bar")
     Set PopupBar = CmdBar.Controls("&Data")
     Set BarCtrl = PopupBar.Controls("&Sort...")
     
       BarCtrl.Enabled = Status
   
End Sub

Rob's code with call to New Macro

Private Sub CommandButton1_Click() '
ActiveSheet.Range("a5:c100").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

With CommandButton1
    .Enabled = False
    .WordWrap = True
    .Caption = "The list has been sorted & this button is now deactivated."
End With

ActivateSort False

End Sub

Add this macro to ThisWorkbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  ActivateSort True
End Sub

Sincerely,
Leith Ross

[JamesT1]

Thankyou both for your help,, now workd fine,,, much appreciated

[broro183]

Very nice Leith :-)

Thanks for the feedback