Disable a the SORT button or menu option

[mike.schultz]

I need to disable other users from being able to sort my spreadsheet, while still having read/write access to the sheet. I currently am using this code to disable the user from using the sort option from the worksheet menu bar, but this still allows the user to use any buttons in the standard tool bar. (need to turn both on and or off when closing the sheet.)

With Application.CommandBars("data")
.Controls.Item("Sort...").Enabled = False
End With

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim CB As CommandBar
    Dim Ctrl As CommandBarControl
    For Each CB In CommandBars
        If CB.Visible = True Then
            For Each Ctrl In CB.Controls
                If Left(Ctrl.Caption, 4) = "Sort" Then
                    Ctrl.Enabled = False
                End If
            Next Ctrl
        End If
    Next CB
End Sub