Disable a the SORT button or menu option

mike.schultz

Board Regular
Joined
Sep 22, 2002
Messages
57
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
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
 

Forum statistics

Threads
1,148,140
Messages
5,745,036
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top