先看自訂後的介面效果:
'自訂功能表命令組
Public Sub creatediyMenu()
For Each con In Application.CommandBars("Data").Controls
con.Delete
Next
Dim diyMenu As Object
Dim MenuItem As Variant
Dim Menusub As Variant
MenuItem = Array( _
"創建工作表目錄", _
"工作表按名稱排序", _
"設置頁眉頁腳", _
"隔行插入空行", _
"刪除空行", _
"刪除選定列空儲存格的行", _
"刪除超級連結", _
"刪除形狀(圖形、控制項、文字方塊等)", _
"設置儲存格最後一個字元為上標", _
"多條件排序(ABCD列)", _
"多條件篩選(AB列)", _
"選區字元統計")
Menusub = Array( _
"PERSONAL.XLSB!表操作.創建工作表目錄", _
"PERSONAL.XLSB!表操作.sortShtByName", _
"PERSONAL.XLSB!表操作.設置頁眉頁腳", _
"PERSONAL.XLSB!行操作.insertBlankRow", _
"PERSONAL.XLSB!行操作.DeleteBlankRow", _
"PERSONAL.XLSB!行操作.批量刪除空行_先選定列", _
"PERSONAL.XLSB!其它.刪除超級連結", _
"PERSONAL.XLSB!其它.刪除形狀", _
"PERSONAL.XLSB!其它.設置儲存格最後一個字元為上標", _
"PERSONAL.XLSB!其它.MoreKeySort", _
"PERSONAL.XLSB!其它.Filter_MoreCriteria", _
"PERSONAL.XLSB!其它.textcount" _
)
'以上注意過程引用的格式:工作簿名稱!模組名稱.過程名稱
For i = 0 To UBound(MenuItem)
Set diyMenu = Application.CommandBars("Data").Controls.Add(Type:=msoControlButton)
With diyMenu
.Caption = MenuItem(i)
.OnAction = Menusub(i)
End With
Next i
Set diyMenu = Nothing
End Sub
Sub MoreKeySort()
With ActiveSheet.Sort.SortFields
.Clear
.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlDescending
.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlDescending
.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlDescending
.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlDescending
'xlAscending
For Each rng In Range("A1:D1")
If rng = "" Then rng.Value = "添加標題"
Next rng
End With
With ActiveSheet.Sort
.SetRange Range("A1").CurrentRegion '指定排序區域
.Header = xlYes '指定排序區域包含標題
.Apply '應用工作表排序
End With
End Sub
-End-