上次提到 CommandBars("Cell") 有兩個,但要如何區別這兩個,其實有方法
以Xp來說
CommandBars(33) '代表 一般模式的 CommandBars("Cell")
CommandBars(36) '代表 分頁預覽模式的 CommandBars("Cell")
早期我是用這個方式處理的,在Xp用沒問題,但在2000及97以後的版本
它的 Index 值都是不同的
所以如果程式碼是要給其他不同平台的使用者使用,就之能用我之前的方法了
如果要 "讓點右鍵加入的選單只在分頁預覽生效" 我只有一個辦法,那就是以CommandBars("Cell") 出現的順序決定,也就是第一個是一般模式而第二個就是分頁預覽模式
所以我只取第二個CommandBars("Cell") 作處理,程式如下
Sub AddIT()
Dim ShN
Dim CN
Dim n As Integer, i As Integer, w As Integer
Dim cb As CommandBar
ShN = Array("A1-A10", "A11-A20", "A21-A30", "A31-A40", "A41-A50", "A51-A60", "A61", "B1-B3")
CN = Array("A", "B")
i = 0
For Each cb In Application.CommandBars
If cb.Type = msoBarTypePopup And = "Cell" Then
i = i + 1
If i = 2 Then
With cb
With .Controls
i = 0: w = 1 For n = 0 To UBound(ShN)
Set MyShBut = .Add(Type:=msoControlPopup, Temporary:=True)
With MyShBut
.Caption = ShN(n)
With .Controls
Do
Set JAMPBUT = .Add(Type:=msoControlButton, Temporary:=True)
With JAMPBUT
.Caption = CN(i) & w
'.OnAction = "選取" & CN(i) & w
End With
If w Mod 10 = 0 Or w = 61 Or (w = 3 And i = 1) Then w = w + 1: Exit Do
w = w + 1
Loop
End With
End With
If w = 62 Or (i = 1 And w = 4) Then
i = i + 1
w = 1
End If
If i = 2 Then Exit For
Next End With
End With
End If
End If
Next cb
End Sub
Sub DeleteMe()
Dim cmb As CommandBarControl
i = 0
For Each cb In Application.CommandBars
If cb.Type = msoBarTypePopup And = "Cell" Then
i = i + 1
If i = 2 Then
For Each cmb In cb.Controls
cmb.Delete
Next
End If
End If
Next cb
End Sub
