我想在excel 打开一个文件时在“Standard”菜单中添加自己的菜单,在关闭文件时删除菜单,以下的代码总是提示不行。但是,以下的代码单独运行时是可以添加、删除菜单的,请各位高手指教。
Private Sub Workbook_Open()
Dim NM
Set NM = CommandBars("Standard").Controls.Add(Type:=msoControlPopup)
With NM
'.BeginGroup = True
.Caption = "打印证明"
.OnAction = "run_sub"
End WithEnd Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("Standardr").Controls("打印证明").DeleteEnd Sub
Private Sub Workbook_Open()
Dim NM
Set NM = CommandBars("Standard").Controls.Add(Type:=msoControlPopup)
With NM
'.BeginGroup = True
.Caption = "打印证明"
.OnAction = "run_sub"
End WithEnd Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CommandBars("Standardr").Controls("打印证明").DeleteEnd Sub
CommandBars("Standard").Controls("打印证明").Delete ‘这里多了各r。
End Sub请各位写出解决的方法,谢谢。
Set NM = CommandBars("Standard").Controls.Add(Type:=msoControlPopup)
With NM
'.BeginGroup = True
.Caption = "打印证明"
.OnAction = "run_sub"
End WithEnd Sub
CommandBars("Standardr").Controls("打印证明").Delete等代码在类中再建两个个public function,才能执行,但是为什么了?
Dim NM
Set NM = Application.CommandBars("Standard").Controls.Add(Type:=msoControlPopup)
With NM
'.BeginGroup = True
.Caption = "打印证明"
.OnAction = "run_sub"
End With
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Standard").Controls("打印证明").Delete
End Sub
Private Sub Command1_Click()
Dim Exl As New Excel.Application
Dim myBar As Office.CommandBar
Dim myButton As Office.CommandBarButton
Dim IsExist As Boolean
IsExist = False
Exl.Workbooks.Add
Exl.Visible = True
'如果存在则显示
For Each myBar In Exl.CommandBars
If myBar.Name = "打印证明" Then
myBar.Visible = True
IsExist = True
End If
Next
'不存在就创建
If Not IsExist Then
Set myBar = Exl.CommandBars.Add(Name:="打印证明", Position:=msoBarTop, Temporary:=False)
Set myButton = CommandBars("打印证明").Controls.Add
With myButton
.Caption = "打印证明"
.ToolTipText = "打印证明"
' .Picture =LoadPicture("f:\cd.ICO")
'指定表面图片序号
.FaceId = 10
.Visible = True
.Enabled = True
'指定宏
.OnAction = "打印证明"
End With
myBar.Visible = True
End If
End Sub
对象中的语句,如果用简化写法(前面省去对象),则默认为省去Me
CommandBars("Standard").Controls.Add
相当于Me.CommandBars("Standard").Controls.Add
也就是ThisWorkbook.CommandBars("Standard").Controls.Add而在模块中就不同了。(模块中有点智能匹配)