excel 隐藏缺省界面的vba代码 隐藏Excel2003菜单栏和工具栏

excel 隐藏缺省界面的vba代码 隐藏Excel2003菜单栏和工具栏

在Excel 2003中，有时需要限制用户的操作，例如当用户打开某个指定的工作簿时，Excel 2003菜单栏和工具栏被隐藏起来。而当关闭该工作簿重新打开其他工作簿后，菜单栏和工具栏又恢复正常。用VBA代码可以实现这个目的，步骤如下：

1.打开需要屏蔽菜单栏和工具栏的工作簿，按Alt+F11，打开VBA编辑器。
2.在“工程”窗口（视图－工程管理器）中选择“ThisWorkBook”，在右侧的代码窗口中输入下面的代码：

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.在退出该工作簿前，先要让菜单栏和工具栏隐藏起来，方法是：

鼠标在代码
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

当试图删除office系统的菜单栏(Menu Bar)时,会收到一条错误信息,而使用visible=false的方法，亦只能在代码执行时隐藏，为实现长久隐藏，参考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中在工具栏位置点右键出现的系统菜单屏蔽掉
1.把下面宏加入文件中，打开即运行。
sub auto_open()
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Standard").Visible = False
end sub

2.可以把用户的操作大大地加以限制，只留下鼠标单击选择某单元格，其它的 功能统统屏蔽掉。连右键快捷菜单都没有哦！

Dim cmb As CommandBar
Dim cmc As CommandBarControl

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

For Each cmb In Application.CommandBars
'隐藏除系统菜单外的所有工具条
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
'隐藏系统菜单的各弹出菜单
cmc.Visible = flase
Next 
隐藏系统右键菜单
Application.CommandBars("toolbar list").Enabled = False

 

显示菜单：

能实现，把代码写在相应的
Sub zldccmx()
    With Application
        .CommandBars("Standard").Visible = True  '显示常用工具栏
        .CommandBars("Formatting").Visible = True '显示格式栏
        .DisplayFormulaBar = True    '显示编辑栏
        .DisplayStatusBar = True '显示状态栏
        .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 '显示状态栏
        .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  '显示状态栏
        .CommandBars("Worksheet Menu Bar").Enabled = True '显示菜单
    End With
End Sub