Macro To Create toolbar

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
586
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top