View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default Volatile Macro for Adding Controls When Opening Workbook

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!