- 帖子
- 56
- 主題
- 8
- 精華
- 0
- 積分
- 81
- 點名
- 0
- 作業系統
- Window7
- 軟體版本
- OFFICE2010
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2011-6-25
- 最後登錄
- 2016-1-17
|
6#
發表於 2011-7-7 20:09
| 只看該作者
曾收集到類似分享，以VBA 寫於 Workbook 開啟自定義按鈕，於關閉檔案時刪除
但未詳細研究其寫法，只更改自己欲定義的地方
可開啟附件自行研究即可，若對 VBA 有基礎應該可輕易知道該更改地方
若想知道詳細寫法含意，需其他高手協助釋疑，
我只知道某些區塊，希望有幫助
 測試個人表單巨集.rar (8.79 KB)
方式
主要VBA 如下
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("測試").Select
On Error Resume Next
Application.CommandBars("MyMenu").Delete
Application.CommandBars("ply").Controls("個人操作表單").Delete
End Sub
Private Sub Workbook_Open()
Sheets("測試").Select
On Error Resume Next
Application.CommandBars("MyMenu").Delete
Application.CommandBars("ply").Controls("個人操作表單").Delete
Dim MyMenuBar As CommandBar
Dim MyButton As CommandBarButton
Set MyMenuBar = Application.CommandBars.Add
With MyMenuBar
.Visible = True
.Name = "MyMenu"
.Position = msoBarTop
Set MyButton = .Controls.Add
With MyButton
.Visible = True
.Caption = "個人操作表單(&P)"
.Style = msoButtonIconAndCaption
.FaceId = 988
.OnAction = "測試個人表單巨集"
End With
End With
Set MyButton = Application.CommandBars("ply").Controls.Add(msoControlButton, , , 1)
With MyButton
.Caption = "個人操作表單"
.Visible = True
.FaceId = 456
.OnAction = "測試個人表單巨集"
End With
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
If Workbooks.Count = 1 Then
Application.CommandBars("MyMenu").Controls("個人操作表單(&P)").Enabled = False
End If
End Sub |
|
