Menubar to Sort

[Guest]

I want to create a menubar item to sort by specific columns only certain
rows of information. I need it to have a drop-down section outlining the
information to be sorted.
ex.
Menubar........................................Sort
............................................................by region
............................................................by district
............................................................by volume


I need each sorted by volume inside of the sort chosen. And a Separate
sort by volume only. Volume is in column BK
The rows I need to sort are rows 55 through 250.

I have created columns A -F
and listed my data to fit into categories in these columns
Region|District|Ad|BRAC|Proj|Closed
0|3|X|0|0|0
0|2|X|0|0|0

Can someone help me in creating a menubar item that will provide me a
dropdown for the above mentioned sorts?

Matt

 

[Leith Ross]

Hello Matt,

These macros will add a ComboBox control to the Cell Context Menu
(right-click) with the three choices you described. I left the code for
the sorting routines blank because I wasn't clear on how the data was to
be sorted and whether you are useing named ranges on the worksheet.

Installing the Macros:
1) Copy all the macro code here using CTRL+C
2) Open the Workbook you want to the macros in.
3) Press ALT+F11 to launch the VBA Editor.
4) Press ALT + I to display the Insert menu.
5) Press M to insert a Module into your Workbook.
6) Press CTRL+V to paste the macros into the module.
7) Press CTRL+S to Savve the macros.

Adding/Removing the Sort ComboBox:
The ComboBox can be Added or Removed manually at anytime by running the
desired macro form the Macro List. To see the list of available macros,
press ALT+F8. Select either AddMenuComboBox or RemoveMenuComboBox.

The ComboBox can be added to the menu automatically by adding the
following code to your Workbooks Open event procedure.
1) Copy the Event Code using CTRL+C
2) Press CTRL+R to move the cursor back to the Project Viewer.
3) Use the Up or Down Arrow keys to select ThisWorkbook.
4) Press the Enter key.
5) Press Ctrl+V to Paste the event code.
6) Press Ctrl+S to Save the code.

'Event Code
Private Sub Workbook_Open()
Call AddMenuComboBox
End Sub


'Menu Macros...
Sub AddMenuComboBox()

Dim CmdBar As CommandBar
Dim CmdBarCombo As CommandBarComboBox

Set CmdBar = Excel.CommandBars("cell")
With CmdBar.Controls
Add Type:=msoControlComboBox, Temporary:=True
End With

Set CmdBarCombo = CmdBar.Controls(CmdBar.Controls.Count)
With CmdBarCombo
Caption = "Sort by"
BeginGroup = True
AddItem "Region"
AddItem "District"
AddItem "Volume"
OnAction = "MySortMacro"
End With

End Sub

Sub RemoveMenuComboBox()

Dim CmdBar As CommandBar
Dim CmdBarCtrl As CommandBarControl

Set CmdBar = Excel.CommandBars("Cell")

On Error Resume Next
CmdBar.Controls("Sort by").Delete

End Sub

Sub MySortMacro()

Dim Choice As String

With Excel.CommandBars("Cell").Controls("Sort by")
Choice = .Text
Text = ""
End With

Select Case Choice
Case Is = "Region"
'Sorting procedure code goes here
Case Is = "District"
'Sorting procedure code goes here
Case Is = "Volume"
'Sorting procedure code goes here
End Select

End Sub
'End Macros

Sincerely,
Leith Ross