Adding a command button to the toolbar or menu bar in Excel?

I'm wondering is it possible to add a command button to the toolbar or menu bar in Excel using VBA I came across an excel file before in work that somebody had setup this way but not sure how to go about it! I basically have two buttons that I would like to have added to the toolbar or menu to make the sheet tidier and enable the user to see all the data without having to move the buttons etc.

http://i102.photobucket.com/albums/m82/sc0ttb_2006/excel_buttons.jpg

Any ideas folks

Thanks,

Scott

Answer this question

Adding a command button to the toolbar or menu bar in Excel?

coconut113651

You could add it to the same procedure that generates the commandbar (CreateToolbar). Make sure you declare all variables before they are used.

Sub CreateToolbar()
'called from Workbook_Open event procedure

Dim cbBar As CommandBar
Dim cbCtl As CommandBarControl
Dim cbMenuItem as CommandBarControl

' delete existing toolbar
DeleteToolbar

'Create the new toolbar
With Application.CommandBars.Add(Name:=sToolbarName)

'Add a toolbar command button to show the form
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!ShowForm"
.FaceId = 343
.TooltipText = "Show the Form."
.Caption= "Show Form."
.Style = msoButtonIconAndCaption
End With

'Add a toolbar command button to hide the form
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!HideForm"
.FaceId = 342
.TooltipText = "Hide the Form."
.Caption= "Hide Form."
.Style = msoButtonIconAndCaption
End With

.Visible = True
.Position = msoBarTop

End With

'Create new items on Tools menu
Set cbMenuItem = CommandBars(1).FindControl(Id:=30007).Controls.Add(Type:=msoControlButton)
With cbMenuItem
.Caption = "Make Report"
.OnAction = "'" & ThisWorkbook.Name & "'!ProcessReport"
.TooltipText = "Make Quarterly Report."
.Style = msoButtonIconAndCaption
End With

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
rod_r
Thanks Jon, forgot about the customize menu item! The final task i'm trying to get sorted is to import data from a different Excel file and insert it into the main sheet in my Excel file. I got the following code snippet of my shared drive in work but im not sure what parameter i have to use when calling the ImportData function Any ideas

Function ImportData(sCaption) As String

Application.ScreenUpdating = False

Dim FileToOpen As String
FileToOpen = ""

Do While FileToOpen = ""
FileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls),*.XLS", 1, "Import " & sCaption)
If FileToOpen = "" Or LCase(FileToOpen) = "false" Then
myResult = MsgBox("File is required for Import!" & vbCrLf & vbCrLf & "Press OK to select a file, or cancel to halt the Macro", vbOKCancel, "No File chosen")
If myResult = 2 Then
End
Else
FileToOpen = ""
End If
End If
Loop

Workbooks.Open Filename:=FileToOpen
ImportData = ActiveWorkbook.Name

End Function
Santhosh Pallikara
Hi Derek, thanks for the help again!

Just need some clarification...

You said when calling the sub that it needs to be public and in a module so I changed my code and added the following from this:

Sheet 1
'code to show form
Private Sub cmdShowForm_Click()
ServiceAwardMenu.Show
End Sub

'code to close form
Private Sub cmdHideForm_Click()
ServiceAwardMenu.Hide
End Sub

to this:

Module 1
'code to show form
Public Sub cmdShowForm_Click()
ServiceAwardMenu.Show
End Sub

'code to close form
Public Sub cmdHideForm_Click()
ServiceAwardMenu.Hide
End Sub

With these changes the buttons wouldn't work I didn't have the other code inserted yet though - would this make a difference

Where will I insert the code for building the command bar and inserting the buttons etc. Module 1, UserForm, This Workbook or Sheet1

Thanks :)
amb_lew
There's code below that shows you how to do it. There are a couple of points worth noting.

1. When the workbook opens you need to build the command bar and add it to Excel's bars.

2. When your workbook closes you need to remove the command bar.

3. The OnAction property points to the name of the sub thats called when the button is pressed. The sub must be in a module and must be public. Use the example below but be sure to change "!CellsToText" to a sub in your workbook, keep the ! at the beginning.

Const sToolbarName As String = "myToolbar"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'deletes the toolbar when the addin is closed
Application.CommandBars(sToolbarName).Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
'set up toolbar to use the addin's functions
CreateToolbar
End Sub

Private Sub CreateToolbar()
'called from Workbook Open event procedure
Dim cbBar As CommandBar
Dim cbCtl As CommandBarControl

'Get rid of any existing toolbar
On Error Resume Next
Application.CommandBars(sToolbarName).Delete
On Error GoTo 0

'Create the new toolbar
With Application.CommandBars.Add(Name:=sToolbarName)

'Add a toolbar command button
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!CellsToText"
.FaceId = 1611
.TooltipText = "Description."
End With

.Visible = True
.Position = msoBarTop

End With
End Sub
Derek at Potters Clay

I should have added code to remove the new Tools menu item:

Sub DeleteToolbar()
' Called from Workbook_BeforeClose and from CreateToolbar

'Get rid of any existing toolbar (error trap in case it doesn't exist)
On Error Resume Next
Application.CommandBars(sToolbarName).Delete
Application.CommandBars(1).FindControl(Id:=30007).Controls("Make Report").Delete
On Error GoTo 0

End Sub()

To remove existing items, go to Tools menu > Customize. While the Customize dialog is showing, you can simply select menu items from a menu and drag them off, which will delete them. To delete a commandbar, find it in the list on the Toolbars tab and click Delete.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Barry Kwok
Thanks Derek. I'm not sure where this fits into the code I've tried it in a new module as part of a sub routine but it brings up errors such as variable not defined etc. can you point me in the right direction

Cheers
St&#233;phane Beauchemin
From the VB Editor's Object Browser:

Function GetOpenFilename([FileFilter], [FilterIndex], [Title], [ButtonText], [MultiSelect])

Inside the procedure you posted, "Import " & sCaption is passed as the third parameter, [Title], to GetOpenFileName. So sCaption is some string that is appended to "Import " to make the caption of the GetOpenFileName dialog.
```
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
```
Noel Muhleisen

Hi Scott,

Yeah you can do it. Custom Menus and Toolbars are called command bars. They both work the same way.

Set newItem = CommandBars("Tools").Controls.Add(Type:=msoControlButton)
With newItem
.BeginGroup = True
.Caption = "Make Report"
.FaceID = 0
.OnAction = "qtrReport"
End With

This adds an item to the Tools menu. The OnAction points to the macro that does your loading external data.
T E N
I actually managed to get it working after many hours of trying! But much more satisfaction when u are able to do it on your own! :)

So i added this code into one module...

'code to import file to excel - called from ModGLData
's = ImportData() etc...

Function ImportData() As String

Application.ScreenUpdating = False

Dim FileToOpen As String
FileToOpen = ""

Do While FileToOpen = ""
FileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls),*.XLS", 1, "Import ")
If FileToOpen = "" Or LCase(FileToOpen) = "false" Then
myResult = MsgBox("File is required for Import!" & vbCrLf & vbCrLf & "Press OK to select a file, or cancel to halt the Macro", vbOKCancel, "No File chosen")
If myResult = 2 Then
End
Else
FileToOpen = ""
End If
End If
Loop

Workbooks.Open Filename:=FileToOpen
ImportData = ActiveWorkbook.Name

End Function

and then put the following code into another module and called the function from the command button i created earlier on the toolbar.

Sub subImportGLData()

Sheets("ServiceAwardData").Select
Cells.Select
Selection.ClearContents

' Import the data
Dim ws As Worksheet
Dim s As String
s = ImportData()
Set ws = Workbooks(s).Sheets(1)

' Now copy and paste data
ws.Cells.Copy
Sheet1.Activate
Sheet1.Range("A1").Select
Sheet1.Paste

' making sure the correct file is imported
If Sheet1.Range("A1") <> "WWID" Then
Sheets("ServiceAwardData").Select
Cells.Select
Selection.ClearContents
MsgBox "Please Import the GL Non Cash Award File", vbOKOnly
' Close Import Workbook
' ClearClipboard
Workbooks(s).Close XlSaveAction.xlDoNotSaveChanges
Else
' Show Success Message
MsgBox "Non Cash Data Imported Successfully", vbOKOnly, "Import Complete"

' Close Import Workbook
' ClearClipboard
Application.CutCopyMode = False
Workbooks(s).Close XlSaveAction.xlDoNotSaveChanges

End If

End Sub

Many Thanks for all the help over the past few days - much appreciated!
Scott

Will Buchanan

You seem to be confusing UserForms with CommandBars. I think I can tell what you're trying to do. I've adjusted and clarified Derek's code.

This code goes into the ThisWorkbook code module:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 'deletes the toolbar when the addin is closed
 DeleteToolbar

End Sub


Private Sub Workbook_Open()

 'set up toolbar to use the addin's functions
 CreateToolbar

End Sub

This code goes into a regular code module (for example, Module1):

Option Explicit

Const sToolbarName As String = "My Toolbar"

Sub CreateToolbar()
 'called from Workbook_Open event procedure

 Dim cbBar As CommandBar
 Dim cbCtl As CommandBarControl

 ' delete existing toolbar
 DeleteToolbar

 'Create the new toolbar
 With Application.CommandBars.Add(Name:=sToolbarName)

  'Add a toolbar command button to show the form
  With .Controls.Add(Type:=msoControlButton)
   .OnAction = "'" & ThisWorkbook.Name & "'!ShowForm"
   .FaceId = 343
   .TooltipText = "Show the Form."
   .Style = msoButtonIconAndCaption
  End With

  'Add a toolbar command button to hide the form
  With .Controls.Add(Type:=msoControlButton)
   .OnAction = "'" & ThisWorkbook.Name & "'!HideForm"
   .FaceId = 342
   .TooltipText = "Hide the Form."
   .Style = msoButtonIconAndCaption
  End With

  .Visible = True
  .Position = msoBarTop

 End With

End Sub


Sub DeleteToolbar()
 ' Called from Workbook_BeforeClose and from CreateToolbar

 'Get rid of any existing toolbar (error trap in case it doesn't exist)
 On Error Resume Next
 Application.CommandBars(sToolbarName).Delete
 On Error GoTo 0

End Sub()


Sub ShowForm()

 ServiceAwardMenu.Show

End Sub


Sub HideForm()

 ServiceAwardMenu.Hide

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

dr.xaml
Many Thanks John (and Derek) for the help - its working perfect now! :) You've introduced me to a whole new area within Excel/VBA that i have never used before - custom toolbars!

Is it possble to do the same sort of thing and add to the menu bar For example to laod external data from another excel sheet into the current one
ga2006
That's great - thanks a million! I was playing about with it and had basically something similair but in a new module which was a lot of unnecessary code. One problem though - i can't get rid of all the tool menu items i added when trying to get it working!!! :)

I couldn't get the delete bit to work properly but its working now - but still left with loads of entries!

http://i102.photobucket.com/albums/m82/sc0ttb_2006/addingmenuexcel.jpg

Thanks
MichaelL

Derek -

>> Set newItem = CommandBars("Tools").Controls.Add(Type:=msoControlButton)

Careful, there's (probably) no "Tools" toolbar. The Tools menu on the main menubar is referenced like this:

Set newItem = CommandBars(1).Controls("Tools").Controls.Add(Type:=msoControlButton)

or more properly, allowing for internationalization:

Set newItem = CommandBars(1).FindControl(Id:=30007).Controls.Add(Type:=msoControlButton)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______
Adding a command button to the toolbar or menu bar in Excel?

Answer this question