VBA 操作excel菜单
来源:互联网 发布:数据监控系统 编辑:程序博客网 时间:2024/05/16 01:54
在thisworkbook中加入:
Option Explicit
Private Sub Workbook_Activate()
Call myTools
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Call DelmyTools
End Sub
Private Sub Workbook_Deactivate()
Call DelmyTools
End Sub
Private Sub Workbook_Open()
'Call myTools
End Sub
在新建模块中加入:
Option Explicit
Sub myTools()
Dim myTools As CommandBarPopup
Dim myCap As Variant
Dim myid As Variant
Dim i As Byte
myCap = Array("基础应用", "VBA程序开发", "函数与公式", "图表与图形", "数据透视表")
myid = Array(281, 283, 285, 287, 292)
With Application.CommandBars("Worksheet menu bar")
.Reset
Set myTools = .Controls("帮助(&H)").Controls.Add(Type:=msoControlPopup, Before:=1)
With myTools
.Caption = "Excel Home 技术论坛"
.BeginGroup = True
For i = 1 To 5
With .Controls.Add(Type:=msoControlButton)
.Caption = myCap(i - 1)
.FaceId = myid(i - 1)
.OnAction = "myC"
End With
Next
End With
End With
Set myTools = Nothing
End Sub
Public Sub myC()
MsgBox "您选择了: " & Application.CommandBars.ActionControl.Caption
End Sub
Sub DelmyTools()
Application.CommandBars("Worksheet menu bar").Reset
End Sub
-------------------------------------------------------------------------
自定义整个菜单:
在thisworkbook里加入:
Option Explicit
Private Sub Workbook_Activate()
Call AddNowBar
End Sub
Private Sub Workbook_Deactivate()
Call DelNowBar
End Sub
在新建立模块中加入:
Option Explicit
Sub AddNowBar()
Dim NewBar As CommandBar
On Error Resume Next
With Application
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Stop Recording").Visible = False
.CommandBars("toolbar list").Enabled = False
.CommandBars.DisableAskAQuestionDropdown = True
.DisplayFormulaBar = False
.CommandBars("NewBar").Delete
End With
Set NewBar = Application.CommandBars.Add(Name:="NewBar", Position:=msoBarTop, MenuBar:=True, Temporary:=True)
With NewBar
.Visible = True
With .Controls.Add(Type:=msoControlPopup)
.Caption = "系统设置(&X)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "保存(&S)"
.BeginGroup = True
.FaceId = 1975
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "备份(&B)"
.BeginGroup = True
.FaceId = 747
End With
End With
With .Controls.Add(Type:=msoControlPopup)
.Caption = "会计凭证(&P)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "录入(&L)"
.BeginGroup = True
.FaceId = 197
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "审核(&S)"
.BeginGroup = True
.FaceId = 714
End With
End With
With .Controls.Add(Type:=msoControlPopup)
.Caption = "会计账簿(&Z)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "记账(&L)"
.BeginGroup = True
.FaceId = 65
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "结账(&S)"
.BeginGroup = True
.FaceId = 47
End With
End With
With .Controls.Add(Type:=msoControlPopup)
.Caption = "会计报表(&B)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlPopup)
.Caption = "资产负债表(&Y)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "月报(&M)"
.BeginGroup = True
.FaceId = 1180
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "年报(&Y)"
.BeginGroup = True
.FaceId = 1188
End With
End With
With .Controls.Add(Type:=msoControlPopup)
.Caption = "损益表(&S)"
.BeginGroup = True
With .Controls.Add(Type:=msoControlButton)
.Caption = "月报(&M)"
.BeginGroup = True
.FaceId = 1180
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "年报(&Y)"
.BeginGroup = True
.FaceId = 1188
End With
End With
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "退出系统(&C)"
.BeginGroup = True
.Style = msoButtonCaption
End With
End With
Set NewBar = Nothing
End Sub
Sub DelNowBar()
On Error Resume Next
With Application
.CommandBars("Standard").Visible = True
.CommandBars("Formatting").Visible = True
.CommandBars("Stop Recording").Visible = True
.CommandBars("toolbar list").Enabled = True
.CommandBars.DisableAskAQuestionDropdown = False
.DisplayFormulaBar = True
.CommandBars("NewBar").Delete
End With
End Sub
移除工作表最大化与最小化图标:
可以先定义菜单,然后将功能赋予菜单,一个为禁用,一个为恢复:
直接在sheet中加入:
Option Explicit
Private Sub CommandButton1_Click() '移除工作表左上角图标和右上角最小化/最大化/关闭按钮
ActiveWorkbook.Protect , , True
End Sub
Private Sub CommandButton2_Click() '恢复工作表左上角图标和右上角最小化/最大化/关闭按钮
ActiveWorkbook.Protect , , False
End Sub
---------------------------------------------------
屏蔽工作表的复制功能:
在thisworkbook中加入:
Option Explicit
Private Sub Workbook_Activate()
Call ProCopy
End Sub
Private Sub Workbook_Deactivate()
Call StaCopy
End Sub
在新建立模块中加入:
Option Explicit
Dim CmdCtrls As CommandBarControls
Dim Cmd As CommandBarControl
Sub ProCopy()
Set CmdCtrls = Application.CommandBars.FindControls(ID:=19)
For Each Cmd In CmdCtrls
Cmd.Enabled = False
Next
Application.CellDragAndDrop = False
Application.OnKey ("^c"), ""
End Sub
Sub StaCopy()
Set CmdCtrls = Application.CommandBars.FindControls(ID:=19)
For Each Cmd In CmdCtrls
Cmd.Enabled = True
Next
Application.CellDragAndDrop = True
Application.OnKey ("^c")
End Sub
- VBA 操作excel菜单
- VBA 操作excel菜单
- vba活用excel右键菜单
- Excel-VBA文件操作
- excel VBA 简单操作
- EXCEL 操作汇总&&VBA
- Excel vba 操作
- Excel VBA高效办公应用-第十六章-VBA高效文件操作(使用级联菜单管理多个工作簿)
- [VB/VBA]SQL操作EXCEL
- Excel常用VBA操作命令
- Excel-VBA文件操作1
- Excel-VBA文件操作2
- Excel-VBA文件操作3
- Excel-VBA文件操作4
- Excel-VBA文件操作5
- VBA操作Excel代码收集
- VBA Excel宏常用操作
- VBA Excel宏操作示例
- 浅析UPnP协议
- android recovery sda 设备号被占用,sdb无法再recovery中使用问题解决。
- 辣味就直往眼睛的专业o2o资讯
- 黑马程序员_day016_String类和StringBuffer类
- Android学习-----Android组件通信PendingIntent
- VBA 操作excel菜单
- c++软件开发第三方库大全
- UPnP基本原理以及在NAT中的应用
- SQLServer索引的四个高级特性 .
- 信息化十年有感
- linux route add
- 无光驱无U盘 硬盘安装操作系统 NT6
- java 将远程共享的文件复制到本地
- tomcat在win7下面启动的问题。 郁闷了半天。 多亏了这位大哥。