Volatile Macro for Adding Controls When Opening Workbook

[Guest]

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!

 

[Bob Phillips]

Put you add_Controls macro back where it was, in a standard code module.

Add another one there

Sub Delete_Controls()
Dim i As Long
Dim caption_names As Variant
caption_names = Array("caption 1", "caption 2", "caption 3")
With Application.CommandBars("Cell")
For i = LBound(onaction_names) To UBound(onaction_names)
.Controls(caption_names(i)).Delete
Next i
End With
End Sub

Replace the Workbook_Open code with this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Delete_Controls
End Sub

Private Sub Workbook_Open()
Call delet_controls
Call Add_Controls
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ron de Bruin]

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

 

[Bob Phillips]

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)

 

[Guest]

Bob and Ron:

Thank you both! Because of my own stupidity, I had a bit of difficulty
getting it to work, but I finally did. Your suggestions were just what was
needed.

May I ask a follow-up question? Is it possible to modify the code so that
the controls appear in and delete from the menu like they do now, but so
that, if I have multiple workbooks open, that the controls only appear on the
menu if the window for the workbook in question is selected? The macros the
controls activate were written with only the one workbook in mind. I don't
expect it will be much of an issue for users to have the controls available
on their right-click menus if they are in another active window, but if I
could make them go away in those cases, that would be spiffy. Is this
possible?

Also, I have copies of Walkenbach's two books on VBA programming
requistitioned so that I don't continue to be so stupid. Could you recommend
any other choices for the novice VBA programmer, or any websites with a good
overview?

You guys are the greatest! Thanks again!

 

[Ron de Bruin]

LOL

 

[Bob Phillips]

In Thisworkbook code module

Private Sub Workbook_Activate()
Call Add_Controls
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Delete_Controls
End Sub

Private Sub Workbook_Deactivate()
Call Delete_Controls
End Sub

Private Sub Workbook_Open()
Call Delete_Controls
Call Add_Controls
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)