replace excel submenu with my own
 

I would like to replace the sort function with my own built-in sort function.
Is it possible to overwrite the default sort with my own when my worksheet is
active? Will the sort icon on the tool bar acts the same way as the "sort"
submenu if I get to overwrite.
Can you point to a website where I can find tips on creating one.

Thanks

replace excel submenu with my own
 

The toolbar sort icons and the submenu item work independently.
You would have to do something to all three.
Also, you would have to have code to change them when the sheet was
activated and then change them back when the sheet was deactivated.
Also, when the workbook was closed/opened the same thing would
have to be done. For instance, if someone closed the workbook with
the sheet in question active, then all the other open workbooks would have
no way to do a normal sort.

Somewhere in there something is going to get screwed up.

Your best bet might be to add a custom toolbar to the workbook and or
a custom button to the worksheet.
There are instructions here to make a custom Toolbar...
http://www.contextures.com/xlToolbar02.html

Note that none of the above applies to XL2007. With that you have to
use a different programming language to manipulate the "ribbon".
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"matelot"
wrote in message
I would like to replace the sort function with my own built-in sort function.
Is it possible to overwrite the default sort with my own when my worksheet is
active? Will the sort icon on the tool bar acts the same way as the "sort"
submenu if I get to overwrite.
Can you point to a website where I can find tips on creating one.

Thanks

replace excel submenu with my own
 

"Jim Cone" wrote...
The toolbar sort icons and the submenu item work independently.
You would have to do something to all three.
Also, you would have to have code to change them when the sheet was
activated and then change them back when the sheet was deactivated.
Also, when the workbook was closed/opened the same thing would
have to be done. For instance, if someone closed the workbook with
the sheet in question active, then all the other open workbooks would have
no way to do a normal sort.

Somewhere in there something is going to get screwed up.

Your best bet might be to add a custom toolbar to the workbook and or
a custom button to the worksheet.
....

Another alternative is to put the code into an XLA add-in that
includes an Excel application events object. The code in the add-in
could be written so that it checked whether ActiveSheet in
ActiveWorkbook contained a particular worksheet-level defined name,
like __RESERVED.UseMySort, and if so set a state variable and replace
the standard bits with the custom bits. That way the special handling
code would be available to any workbook/worksheet controlable from
each worksheet but with the code only in the add-in. The downside is
creating a class of reserved range names.

replace excel submenu with my own
 

Place all of the following code in the ThisWorkbook module.
"TestSheet" should be changed to the correct sheet name.
Msgbox display can be replaced with custom sort code.
Save and close the file and reopen.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


Private WithEvents objCtrl As Office.CommandBarButton
Private WithEvents objSortUp As Office.CommandBarButton
Private WithEvents objSortDown As Office.CommandBarButton

Private Sub objCtrl_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
If Me Is ActiveWorkbook Then
If Me.Worksheets("TestSheet") Is ActiveSheet Then
MsgBox "Sort Menu Clicked ", vbInformation, "With Events "
CancelDefault = True
End If
End If
End Sub

Private Sub objSortDown_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
If Me Is ActiveWorkbook Then
If Me.Worksheets("TestSheet") Is ActiveSheet Then
MsgBox "Sort Down Clicked ", vbInformation, "With Events "
CancelDefault = True
End If
End If
End Sub

Private Sub objSortUp_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
If Me Is ActiveWorkbook Then
If Me.Worksheets("TestSheet") Is ActiveSheet Then
MsgBox "Sort Up Clicked ", vbInformation, "With Events "
CancelDefault = True
End If
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set objCtrl = Nothing
Set objSortUp = Nothing
Set objSortDown = Nothing
End Sub

Private Sub Workbook_Open()
Set objCtrl = Excel.Application.CommandBars.FindControl(ID:=928) 'Data | Sort
Set objSortUp = Excel.Application.CommandBars.FindControl(ID:=210) 'Toolbar Sort button
Set objSortDown = Excel.Application.CommandBars.FindControl(ID:=211) 'Toolbar Sort button
End Sub
'----------


"Harlan Grove"
wrote in message
Another alternative is to put the code into an XLA add-in that
includes an Excel application events object. The code in the add-in
could be written so that it checked whether ActiveSheet in
ActiveWorkbook contained a particular worksheet-level defined name,
like __RESERVED.UseMySort, and if so set a state variable and replace
the standard bits with the custom bits. That way the special handling
code would be available to any workbook/worksheet controlable from
each worksheet but with the code only in the add-in. The downside is
creating a class of reserved range names.