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

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
End If

nButtonPos = 0
nButtons =

' see if the button is already placed
If nButtons >= 0 Then
For i = 1 To nButtons
lpszButtonName$ =
If lpszButtonName$ = "Import Data" Then Exit Sub
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
' this button will run the macro called ImporrtData
End Sub


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)


Bob Phillips


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



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




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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question