Macro To Create toolbar

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
583
Office Version
  1. 2013
Platform
  1. Windows
I am trying to create a macro that will create a custom toolbar whenever a user opens the spreadsheet. I am recording it but it does not seem to record all my actions.

This what i do

start recording
left click on toolbar
select customise
select New
add custom button
name it
assign macro

all the resulting macro does is create an empty toolbar

what am i doing wrong

Regard
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
you can't record it.

if you do a google search or a search on here you could find the code though.

theres one for a bunch of different menus/toolbars depending on what you're looking for.
 

Gives

Board Regular
Joined
Aug 22, 2006
Messages
160
Adding Toolbar Button

I tend to stay away from modifying toolbars with VBA because any customizations made to a toolbar (either built in or custom) are permanent. The changes remain in effect even when you restart excel and even if the macro is not included in the new workbook. To restore the toolbar to its original state you will need to reset it. However, you can add a button to an existing toolbar using the following macro:

Sub AddButton()
Dim NewButton as CommandBarButton
Set NewButton=CommandBars("Toolbar name you want to add Button to").controls.add (Type:=msoControlButton)
with NewButton
.FaceId =300
.OnAction = "Name of Macro to execute when NewButton clicked"
.Caption = "Message to be displayed when cursor over button"
end with
end sub

Then you'll need a procedure in the Thisworkbook module to call the new procedure when the workbook opens:

Private Sub Workbook_Open()
call AddButton
end sub
 

galileogali

Well-known Member
Joined
Oct 14, 2005
Messages
748
Have you Excel 2007?

Her the basic code
Code:
Sub ControlButtonCreate()
    
Dim mycontrol As CommandBarControl
    Set micontrol = Application.CommandBars("Standard").Controls.Add(Type:=msoControlPopup, _
        Before:=15)
mycontrol.Caption = "menu"

End Sub

GALILEOGALI
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
OK, save it has an excel addin

in "thisworkbook" part place these two items

Code:
Private Sub Workbook_Open()
Run "AddMenus"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "DeleteMenu"
End Sub

now module1 or in module use this
Code:
Sub AddMenus()
Call DeleteMenu

Dim cbpop As CommandBarControl
Dim cbctl As CommandBarControl
Dim cbsub As CommandBarControl

' Create a popup control on the main menu bar
Set cbpop = Application.CommandBars("Worksheet Menu Bar"). _
Controls.Add(Type:=msoControlPopup)
cbpop.Caption = "&Gomez Tools"
cbpop.Visible = True


' Add a button control menu item

With cbpop.Controls.Add(Type:=msoControlButton)
    .FaceId = 22
    .Visible = True
    .Caption = "Paste Values"
    .OnAction = "CopyPasteValues"
End With
    
   

End Sub


Sub DeleteMenu()
On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls("&My Menu").Delete
    On Error GoTo 0
End Sub
 

Forum statistics

Threads
1,181,373
Messages
5,929,571
Members
436,679
Latest member
helly123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top