Cell Menu

G

GregR

I have the following code which adds the macros to the cell menu. How do I
begin a group for the array or assign the array to a submenu. TIA

Greg
 
G

GregR

Might be nice if I added the code:
Sub AddtoCellMenuAbs()

Dim iCtr As Long
Dim myMacros As Variant
Dim myCaptions As Variant

Dim cb As CommandBar
Set cb = Application.CommandBars("Cell")

myMacros = Array("Absolute.Absolute", "Relative", "AbsoluteRow",
"AbsoluteCol")
myCaptions = Array("Absolute", "Relative", "Absolute Row", "Absolute
Col")

With cb.Controls

For iCtr = LBound(myMacros) To UBound(myMacros)
With .Add(Type:=msoControlButton, temporary:=True)
.Caption = myCaptions(iCtr)
.OnAction = ThisWorkbook.Name & "!" & myMacros(iCtr)
' .FaceId = 103
' .BeginGroup = True
End With
Next iCtr
End With
'in your workbook_open/auto_open code, you can just have a
'Call AddToCellMenu
End Sub

Greg
 
N

Norman Jones

Hi GregR,

One way:

Sub AddtoCellMenuAbs()

Dim iCtr As Long
Dim myMacros As Variant
Dim myCaptions As Variant
Dim blBeginGroup As Boolean

Dim cb As CommandBar
Set cb = Application.CommandBars("Cell")

myMacros = Array("Absolute.Absolute", "Relative", _
"AbsoluteRow", "AbsoluteCol")
myCaptions = Array("Absolute", "Relative", _
"Absolute Row", "AbsoluteCol ")

With cb.Controls
For iCtr = LBound(myMacros) To UBound(myMacros)
With .Add(Type:=msoControlButton, temporary:=True)
.Caption = myCaptions(iCtr)
.OnAction = ThisWorkbook.Name & "!" & myMacros(iCtr)
.FaceId = 103
If Not blBeginGroup Then
.BeginGroup = True
blBeginGroup = True
End If
End With
Next iCtr
End With
'in your workbook_open/auto_open code, you can just have a
'Call AddToCellMenu
End Sub
 
G

GregR

Norman, thank you very much.

Greg
Norman Jones said:
Hi GregR,

One way:

Sub AddtoCellMenuAbs()

Dim iCtr As Long
Dim myMacros As Variant
Dim myCaptions As Variant
Dim blBeginGroup As Boolean

Dim cb As CommandBar
Set cb = Application.CommandBars("Cell")

myMacros = Array("Absolute.Absolute", "Relative", _
"AbsoluteRow", "AbsoluteCol")
myCaptions = Array("Absolute", "Relative", _
"Absolute Row", "AbsoluteCol ")

With cb.Controls
For iCtr = LBound(myMacros) To UBound(myMacros)
With .Add(Type:=msoControlButton, temporary:=True)
.Caption = myCaptions(iCtr)
.OnAction = ThisWorkbook.Name & "!" & myMacros(iCtr)
.FaceId = 103
If Not blBeginGroup Then
.BeginGroup = True
blBeginGroup = True
End If
End With
Next iCtr
End With
'in your workbook_open/auto_open code, you can just have a
'Call AddToCellMenu
End Sub
Click to expand...
 
Top