excel 隱藏缺省界面的vba代碼 隱藏Excel2003菜單欄和工具欄

在Excel 2003中，有時需要限制用戶的操作，例如當(dāng)用戶打開某個指定的工作簿時,，Excel 2003菜單欄和工具欄被隱藏起來,。而當(dāng)關(guān)閉該工作簿重新打開其他工作簿后，菜單欄和工具欄又恢復(fù)正常,。用VBA代碼可以實現(xiàn)這個目的，步驟如下：

1.打開需要屏蔽菜單欄和工具欄的工作簿,，按Alt+F11,，打開VBA編輯器。
2.在“工程”窗口（視圖－工程管理器）中選擇“ThisWorkBook”,，在右側(cè)的代碼窗口中輸入下面的代碼：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
showhide (bHide = True)
End Sub

Private Sub Workbook_Open()
showhide (bHide = False)
End Sub

Sub showhide(Optional bHide As Boolean)
Dim cmb As CommandBar
Static col As New Collection
If bHide Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal Then
If cmb.Visible Then
cmb.Enabled = False
If cmb.Visible Then cmb.Visible = False
col.Add cmb, cmb.Name
End If
End If
Next cmb
Else
If col Is Nothing Or col.Count = 0 Then
For Each cmb In Application.CommandBars
If cmb.Type = msoBarTypeMenuBar Or cmb.Type = msoBarTypeNormal Then
If Not cmb.Visible Or Not cmb.Enabled Then
cmb.Enabled = True
If (Not cmb.Visible) And cmb.Enabled Then cmb.Visible = True
End If
End If
Next cmb
Else
For Each cmb In col
If Not cmb.Visible Or Not cmb.Enabled Then
cmb.Enabled = True
If (Not cmb.Visible) And cmb.Enabled Then cmb.Visible = True
End If
Next cmb
End If
Set col = Nothing
End If
End Sub

3.在退出該工作簿前,，先要讓菜單欄和工具欄隱藏起來，方法是：

鼠標(biāo)在代碼
Private Sub Workbook_Open()
showhide (bHide = False)
End Sub

Sub hidebar()        
    Application.CommandBars(1).Enabled = False ' 隱藏菜單欄
    Application.DisplayFullScreen = True' 全屏顯示
    Application.CommandBars("Full Screen").Visible = False

    ActiveWindow.DisplayHorizontalScrollBar = False' 隱藏滾動條
        ActiveWindow.DisplayVerticalScrollBar = False
End Sub

Sub unhidebar()
    Application.CommandBars(1).Enabled = True
    Application.DisplayFullScreen = False
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
End Sub

隱藏視圖菜單下面工具欄上面的項目,？比如隱藏“控件工具箱”,。
Application.CommandBars(1).Controls(3).Controls(3).Enabled = False

當(dāng)試圖刪除office系統(tǒng)的菜單欄(Menu Bar)時,會收到一條錯誤信息,而使用visible=false的方法，亦只能在代碼執(zhí)行時隱藏,，為實現(xiàn)長久隱藏,，參考microsoft文檔得出以下代碼
Sub Hide()
     Application.CommandBars("Worksheet Menu Bar").Position = msoBarFloating
     Application.CommandBars("Worksheet Menu Bar").Left = -Application.CommandBars("Worksheet Menu Bar").Width + 1
     Application.CommandBars("Worksheet Menu Bar").Top = -Application.CommandBars("Worksheet Menu Bar").Height + 1
End Sub

Sub Show()
     Application.CommandBars("Worksheet Menu Bar").Position = msoBarTop
End Sub

注解：將Menu Bar停靠方式改為浮動,，修改其位置在屏幕之外,；顯示再改為頂停靠．Excel中Menu Bar為Worksheet Menu Bar,，其他office文檔中仍為Menu Bar.

把Excel中在工具欄位置點右鍵出現(xiàn)的系統(tǒng)菜單屏蔽掉
1.把下面宏加入文件中,，打開即運行。
sub auto_open()
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
end sub

2.可以把用戶的操作大大地加以限制,，只留下鼠標(biāo)單擊選擇某單元格,，其它的 功能統(tǒng)統(tǒng)屏蔽掉。連右鍵快捷菜單都沒有哦,！

Dim cmb As CommandBar
Dim cmc As CommandBarControl

Set mnuSys = Application.CommandBars("Worksheet Menu Bar")

For Each cmb In Application.CommandBars
'隱藏除系統(tǒng)菜單外的所有工具條
If cmb.Name <> "Worksheet Menu Bar" Then
If cmb.Visible Then
'colVisualCommandBars.Add cmb, cmb.Name
cmb.Visible = False '隱藏其它工具條
End If
End If
Next

For Each cmc In mnuSys.Controls
'隱藏系統(tǒng)菜單的各彈出菜單
cmc.Visible = flase
Next 
隱藏系統(tǒng)右鍵菜單
Application.CommandBars("toolbar list").Enabled = False

顯示菜單：

能實現(xiàn),，把代碼寫在相應(yīng)的
Sub zldccmx()
    With Application
        .CommandBars("Standard").Visible = True  '顯示常用工具欄
        .CommandBars("Formatting").Visible = True '顯示格式欄
        .DisplayFormulaBar = True    '顯示編輯欄
        .DisplayStatusBar = True '顯示狀態(tài)欄
        .CommandBars("Worksheet Menu Bar").Enabled = True '顯示菜單
    End With
End Sub
如果要隱藏，只需將上面代碼中的　True 改成 False　即可
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 With Application
        .CommandBars("Standard").Visible = True  '顯示常用工具欄
        .CommandBars("Formatting").Visible = True '顯示格式欄
        .DisplayFormulaBar = True    '顯示編輯欄
        .DisplayStatusBar = True '顯示狀態(tài)欄
        .CommandBars("Worksheet Menu Bar").Enabled = True '顯示菜單
    End With
End Sub

Private Sub Workbook_Open()
 With Application
        .CommandBars("Standard").Visible = False  '顯示常用工具欄
        .CommandBars("Formatting").Visible = False '顯示格式欄
        .DisplayFormulaBar = False    '顯示編輯欄
        .DisplayStatusBar = False  '顯示狀態(tài)欄
        .CommandBars("Worksheet Menu Bar").Enabled = True '顯示菜單
    End With
End Sub