I am in charge of supporting a multiuser xls worksheet, where different users
need to conduct a sort in data. From time to time a sort is made only on
parts of rows and new data is inserted in some fields. This results in
disruption of the data when the sheet is resorted back to original sort. What
I would like to do is for this worksheet alone to maybe make a macro that
disables sorting by default, and only sorts by using my macro to make sure
that a sort only can be made on the entire worksheet. Once the data is
disrupted the dept. in question has to use 20+ workhours to restore the data.
Please lend me a hand
Hopefull

Kim

This macro snippet will disable the 'Sort' button.
Change 'False' to 'True' to enable the 'Sort' Button.

'/=========================================/
Dim ctrl As Object, ctrlMenuItem As Object

For Each ctrl In CommandBars("Worksheet Menu Bar").Controls
If ctrl.Caption = "&Data" Then
For Each ctrlMenuItem In ctrl.Controls
If ctrlMenuItem.Caption = "&Sort..." Then
ctrlMenuItem.Enabled = False
Exit For
End If
Next ctrlMenuItem
Exit For
End If
Next ctrl
'/=========================================/
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Kim Melkane" wrote:

I am in charge of supporting a multiuser xls worksheet, where different users
need to conduct a sort in data. From time to time a sort is made only on
parts of rows and new data is inserted in some fields. This results in
disruption of the data when the sheet is resorted back to original sort. What
I would like to do is for this worksheet alone to maybe make a macro that
disables sorting by default, and only sorts by using my macro to make sure
that a sort only can be made on the entire worksheet. Once the data is
disrupted the dept. in question has to use 20+ workhours to restore the data.
Please lend me a hand
Hopefull

Kim

thanks a bundle, great help!

"Gary Brown" wrote:

This macro snippet will disable the 'Sort' button.
Change 'False' to 'True' to enable the 'Sort' Button.

'/=========================================/
Dim ctrl As Object, ctrlMenuItem As Object

For Each ctrl In CommandBars("Worksheet Menu Bar").Controls
If ctrl.Caption = "&Data" Then
For Each ctrlMenuItem In ctrl.Controls
If ctrlMenuItem.Caption = "&Sort..." Then
ctrlMenuItem.Enabled = False
Exit For
End If
Next ctrlMenuItem
Exit For
End If
Next ctrl
'/=========================================/
HTH,
--
Gary Brown

Please rate this posting if it is helpful to you.


"Kim Melkane" wrote:

I am in charge of supporting a multiuser xls worksheet, where different users
need to conduct a sort in data. From time to time a sort is made only on
parts of rows and new data is inserted in some fields. This results in
disruption of the data when the sheet is resorted back to original sort. What
I would like to do is for this worksheet alone to maybe make a macro that
disables sorting by default, and only sorts by using my macro to make sure
that a sort only can be made on the entire worksheet. Once the data is
disrupted the dept. in question has to use 20+ workhours to restore the data.
Please lend me a hand
Hopefull

Kim