Saving button on all workbooks

[Todd Huttenstine]

The below code creates a button on the Worksheet Menu Bar
at the top and assigns the macro ImporrtData to it.

The problem is when I save the workbook and then reopen
it, the button is no longer there, nor is it there on any
other workbook I open. Is there a way I can save it there
permanently?


Sub testaddbutton()
Dim newMenu As CommandBarPopup
Dim ctrlPopUp As CommandBarControl
Dim ctrlButton As CommandBarControl
Dim nButtonPos
Dim nButtons
Dim lpszButtonName$

If MsgBox("Are you sure?", vbYesNo) = vbNo Then
Exit Sub
Else
End If

nButtonPos = 0
nButtons =
Application.CommandBars.ActiveMenuBar.Controls.Count

' see if the button is already placed
If nButtons >= 0 Then
For i = 1 To nButtons
lpszButtonName$ =
Application.CommandBars.ActiveMenuBar.Controls(i).Caption
If lpszButtonName$ = "Import Data" Then Exit Sub
Next
End If

Set newMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)

newMenu.Caption = "Import Data"

Set ctrlPopUp = newMenu.Controls.Add
(Type:=msoControlPopup, ID:=1)
ctrlPopUp.Caption = "Please Select..."
Set ctrlButton = ctrlPopUp.Controls.Add
(Type:=msoControlButton, ID:=1)
ctrlButton.Caption = "Import Data..."
ctrlButton.Style = msoButtonCaption
ctrlButton.OnAction = "ImporrtData"

' This will add the Button Import Data to the Menu Bar
' Under this button will be a button called "Import
Data..."
' this button will run the macro called ImporrtData
End Sub


Thanks

Todd Huttenstine

 

[Greg Wilson]

Remove the argument: "Temporary:=True" in the below code
or set it to False.

Set newMenu = CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)

Regards,
Greg

 

[Bob Phillips]

Todd,

I would suggest that you call that code in the Workbook_Open event in the
ThisWorkbook code module, rather than remove the Temporary:=True.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[COM]

It may be obvious why you would not want to set the temporary:=true, but let's spell it out.

If you set the value to false, then unless you successfully remove the button from the toolbar, by the program that calls it, or that is the only program that needs that button, then every excel file that you open will have that button present, and will cause errors if the same program code is not present.

So, say you have a button that calculates the totals for a given set of values in your "Accounting.xls" file by running "Module1.Total", then you do *not* remove the button (and it is a "permanent" button). Now, say you close "Accounting.xls" and go to open your "Payroll.xls" file, then the button will be there. And of course, if you select the button the Payroll.xls file will look for code called "Module1.Total". If that code is there, then it will run and do whatever it is programmed to do, but most likely that code would not be there, and an error would result.

Now, I'm not saying that I was totally clear, but I felt that an explanation like this was warranted. Now however if you have created a button that does something you find useful and is able to access the appropriate code from any open worksheet, then you may want to make it a permanent addition. It is also useful if you wish to control/restrict users ability to do certain actions. But as all things, users are likely to find ways around things.