Excel 是一個相當好用的工具，提供了相當多的內建函數與巨集可以讓我們做出相當漂亮與豐富的報表。
但對於習慣寫程式或者是需要使用 Excel 來當作程式主體的人來說，這些 Menu Bar 可以說是擾亂程式的元兇。
所以本篇就要介紹，如何關掉 Excel 的 Menu Bar，讓 Excel 看起來乾乾淨淨。
其實只有一個指令，
Application.CommandBars("Worksheet Menu Bar").Enabled=False
這個指令是將主 Menu 關掉的意思，其他的像 ToolBar 等都還會存在。
如果您只想要關掉某些功能呢?
首先您要知道那些功能的名稱是什麼，怎麼知道呢? Google? 可能找到的都是片段，可以透過一段程式將所有的 Menu Bar 名稱 Show 出來，如下面這一小段程式碼
Dim oBar as CommandBar
For Each oBar in Application.CommandBars
Debug.Print oBar.Name
Next
這樣就可以知道有哪些 Menu Bar 存在於 Excel 中，所以將這些名稱透過 Application.CommandBars 的指令，一一把它關掉即可。
您必須在一開啟 Workbook 時就去下這一段的指令(在 ThisWorkBook當中)，如:
Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Enabled=False
End Sub
將上述的動作寫成了一個 VB Module，提供開關的方式供邦友參考。有些功能表的名稱需要對照(或Google)一下，或是測試一下關掉的是哪一個部份。也歡迎邦友修正後再分享出來。
------------------------------------------------------------------------------------------------------------------------------------
Attribute VB_Name = "mdlMenu"
Option Explicit
Public Sub MenuSwitch(onoff As Boolean)
On Error GoTo ErrorHandler
Application.CommandBars("Worksheet Menu Bar").Enabled = onoff
Application.CommandBars("Chart Menu Bar").Enabled = onoff
Application.CommandBars("Standard").Enabled = onoff
Application.CommandBars("Formatting").Enabled = onoff
Application.CommandBars("PivotTable").Enabled = onoff
Application.CommandBars("Chart").Enabled = onoff
Application.CommandBars("Reviewing").Enabled = onoff
Application.CommandBars("Forms").Enabled = onoff
Application.CommandBars("Stop Recording").Enabled = onoff
Application.CommandBars("External Data").Enabled = onoff
Application.CommandBars("Formula Auditing").Enabled = onoff
Application.CommandBars("Full Screen").Enabled = onoff
Application.CommandBars("Circular Reference").Enabled = onoff
Application.CommandBars("Visual Basic").Enabled = onoff
Application.CommandBars("Web").Enabled = onoff
Application.CommandBars("Control Toolbox").Enabled = onoff
Application.CommandBars("Exit Design Mode").Enabled = onoff
Application.CommandBars("Refresh").Enabled = onoff
Application.CommandBars("Watch Window").Enabled = onoff
Application.CommandBars("PivotTable Field List").Enabled = onoff
Application.CommandBars("Borders").Enabled = onoff
Application.CommandBars("Protection").Enabled = onoff
Application.CommandBars("Text To Speech").Enabled = onoff
Application.CommandBars("List").Enabled = onoff
Application.CommandBars("Compare Side by Side").Enabled = onoff
Application.CommandBars("Drawing").Enabled = onoff
Application.CommandBars("PivotChart Menu").Enabled = onoff
Application.CommandBars("Workbook tabs").Enabled = onoff
Application.CommandBars("Cell").Enabled = onoff
Application.CommandBars("Column").Enabled = onoff
Application.CommandBars("Row").Enabled = onoff
Application.CommandBars("Cell").Enabled = onoff
Application.CommandBars("Column").Enabled = onoff
Application.CommandBars("Row").Enabled = onoff
Application.CommandBars("Ply").Enabled = onoff
Application.CommandBars("XLM Cell").Enabled = onoff
Application.CommandBars("Document").Enabled = onoff
Application.CommandBars("Desktop").Enabled = onoff
Application.CommandBars("Nondefault Drag And Drop").Enabled = onoff
Application.CommandBars("AutoFill").Enabled = onoff
Application.CommandBars("Button").Enabled = onoff
Application.CommandBars("Dialog").Enabled = onoff
Application.CommandBars("Series").Enabled = onoff
Application.CommandBars("Plot Area").Enabled = onoff
Application.CommandBars("Floor and Walls").Enabled = onoff
Application.CommandBars("Trendline").Enabled = onoff
Application.CommandBars("Chart").Enabled = onoff
Application.CommandBars("Format Data Series").Enabled = onoff
Application.CommandBars("Format Axis").Enabled = onoff
Application.CommandBars("Format Legend Entry").Enabled = onoff
Application.CommandBars("Formula Bar").Enabled = onoff
Application.CommandBars("PivotTable Context Menu").Enabled = onoff
Application.CommandBars("Query").Enabled = onoff
Application.CommandBars("Query Layout").Enabled = onoff
Application.CommandBars("AutoCalculate").Enabled = onoff
Application.CommandBars("Object/Plot").Enabled = onoff
'Application.CommandBars("Title Bar(Charting)").Enabled = onoff
Application.CommandBars("Layout").Enabled = onoff
Application.CommandBars("Pivot Chart Popup").Enabled = onoff
Application.CommandBars("Phonetic Information").Enabled = onoff
Application.CommandBars("Auto Sum").Enabled = onoff
Application.CommandBars("Paste Special Dropdown").Enabled = onoff
Application.CommandBars("Find Format").Enabled = onoff
Application.CommandBars("Replace Format").Enabled = onoff
Application.CommandBars("List Range Popup").Enabled = onoff
Application.CommandBars("List Range Layout Popup").Enabled = onoff
Application.CommandBars("XML Range Popup").Enabled = onoff
Application.CommandBars("List Range Layout Popup").Enabled = onoff
Application.CommandBars("WordArt").Enabled = onoff
Application.CommandBars("Picture").Enabled = onoff
Application.CommandBars("Shadow Settings").Enabled = onoff
Application.CommandBars("3-D Settings").Enabled = onoff
Application.CommandBars("Drawing Canvas").Enabled = onoff
Application.CommandBars("Organization Chart").Enabled = onoff
Application.CommandBars("Diagram").Enabled = onoff
Application.CommandBars("Ink Drawing And Writing").Enabled = onoff
Application.CommandBars("Ink Annotations").Enabled = onoff
Application.CommandBars("Borders").Enabled = onoff
Application.CommandBars("Borders").Enabled = onoff
Application.CommandBars("Draw Border").Enabled = onoff
Application.CommandBars("Chart Type").Enabled = onoff
Application.CommandBars("Pattern").Enabled = onoff
Application.CommandBars("Font Color").Enabled = onoff
Application.CommandBars("Fill Color").Enabled = onoff
Application.CommandBars("Line Color").Enabled = onoff
Application.CommandBars("Drawing and Writing Pens").Enabled = onoff
Application.CommandBars("Annotation Pens").Enabled = onoff
Application.CommandBars("Drawing and Writing Pens").Enabled = onoff
Application.CommandBars("Annotation Pens").Enabled = onoff
Application.CommandBars("Order").Enabled = onoff
Application.CommandBars("Nudge").Enabled = onoff
Application.CommandBars("Align or Distribute").Enabled = onoff
Application.CommandBars("Rotate or Flip").Enabled = onoff
Application.CommandBars("Lines").Enabled = onoff
Application.CommandBars("Connectors").Enabled = onoff
Application.CommandBars("AutoShapes").Enabled = onoff
Application.CommandBars("Callouts").Enabled = onoff
Application.CommandBars("Flowchart").Enabled = onoff
Application.CommandBars("Block Arrows").Enabled = onoff
Application.CommandBars("Stars & Banners").Enabled = onoff
Application.CommandBars("Basic Shapes").Enabled = onoff
Application.CommandBars("Insert Shape").Enabled = onoff
Application.CommandBars("Shapes").Enabled = onoff
Application.CommandBars("Inactive Chart").Enabled = onoff
Application.CommandBars("Excel Control").Enabled = onoff
Application.CommandBars("Curve").Enabled = onoff
Application.CommandBars("Curve Node").Enabled = onoff
Application.CommandBars("Curve Segment").Enabled = onoff
Application.CommandBars("Pictures Context Menu").Enabled = onoff
Application.CommandBars("OLE Object").Enabled = onoff
Application.CommandBars("ActiveX Control").Enabled = onoff
Application.CommandBars("WordArt Context Menu").Enabled = onoff
Application.CommandBars("Rotate Mode").Enabled = onoff
Application.CommandBars("Connector").Enabled = onoff
Application.CommandBars("Script Anchor Popup").Enabled = onoff
Application.CommandBars("Canvas Popup").Enabled = onoff
Application.CommandBars("Organization Chart Popup").Enabled = onoff
Application.CommandBars("Diagram").Enabled = onoff
Application.CommandBars("Select").Enabled = onoff
Application.CommandBars("Layout").Enabled = onoff
Application.CommandBars("符號表").Enabled = onoff
Application.CommandBars("Task Pane").Enabled = onoff
Application.CommandBars("Add Command").Enabled = onoff
Application.CommandBars("Built-in Menus").Enabled = onoff
Application.CommandBars("Clipboard").Enabled = onoff
Application.CommandBars("Envelope").Enabled = onoff
Application.CommandBars("Online Meeting").Enabled = onoff
Application.CommandBars("Microsoft Office Live Add-in").Enabled = onoff
ExitSub:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume Next
End Sub
留言列表
