Novel approach Ron, add them when you close the workbook, delete them when
you open it <vbg
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Ron de Bruin" wrote in message
...
From
http://www.rondebruin.nl/menuid.htm#Add
Keep the macro's in a normal module and in the Thisworkbook event only
call the macro
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Add_Controls
End Sub
Private Sub Workbook_Open()
Call Delete_Controls
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Wuddus" wrote in message
...
A helpful group member here gave me the following macro, which adds three
selections to the pop-up menu which appears when you right-click on the
spreadsheet:
Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")
With Application.CommandBars("Cell")
For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i
End With
End Sub
It works really well. I've tried modifying it as follows, however, so
that
it runs automatically when the workbook opens:
Private Sub Workbook_Open()
Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")
With Application.CommandBars("Cell")
For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i
End With
End Sub
And now it is extremely volatile. Sometimes it adds just one control
when I
open the workbook, but more usually it does nothing at all. Sometimes it
crashes while trying to open. Where have I gone wrong? (If it's useful
to
know, I'm running this with Excel for Mac 2004 (11.2) on the Tiger OS.)
If anyone can help, I'd be very appreciative!