Macro for creating custom excel menu

M

manekankit

Board Regular
Joined
Feb 1, 2019
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Need help create custom Excel menu/tab similar to home, data and review tab etc.

This new menu will have buttons /icons similar to other excel menu. Those buttons will be assigned individual macros.

This excel file should be in addin format.

Requesting community members to provide guidance / share macro to do this.
 
Sort by date Sort by votes
VBA Code: 
Option Explicit
Sub CreateMyTool()
'Makes a toolbar called "cbMyTool"

Dim cbMyTool As CommandBar
Dim cbbMyButton As CommandBarButton

'Make the toolbar
Set cbMyTool = CommandBars.Add

'Now we add a button to the toolbar. FaceId is the button's icon,
'OnAction is the macro to run, if the button is clicked, and
'ToolTipText is the text that will show when the mouse hovers.
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 645
   .TooltipText = "Do magic with numbers"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 940
   .TooltipText = "Show a message box"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 385
   .TooltipText = "Functions"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 1662
   .TooltipText = "Constraints"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 225
   .TooltipText = "Lock cells"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 154
   .TooltipText = "Delete all"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 155
   .TooltipText = "Return"
End With

'Before we finish, the toolbar gets a name, width and
'is put on the screen.
With cbMyTool
   .Name = "Shortcuts"
   .Left = Application.ActiveWindow.Width
   .Top = Application.ActiveWindow.Height
   .Visible = True
   .Width = 300
End With

BeforeExit:
Set cbMyTool = Nothing
Set cbbMyButton = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " CreateMyTool", vbOKOnly + vbCritical, "Error"
Resume BeforeExit
End Sub

Sub DeleteMyTool()
'Removes the toolbar "Shortcuts".
'If it doesn't exist we get an error,
'and that is why we use On Error Resume Next.

On Error Resume Next

CommandBars("Shortcuts").Delete

On Error GoTo 0

End Sub

Sub RemoveToolBar()
'Removes all not-built-in menus and
'toolbars and restores Excel's standard
'menu. This is an alternative to
'DeleteMyTool, that only removes
'the named toolbar Shortcuts.

Dim cbBar As CommandBar

On Error GoTo ErrorHandle

For Each cbBar In Application.CommandBars
    If Not cbBar.BuiltIn Then cbBar.Delete
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description & " RemoveMenu", vbOKOnly, "Error"
End Sub

Download example workbook : Menu Bar Add Custom Remove All.xlsm
 
Upvote 0
Solution
Logit said:
VBA Code: 
Option Explicit
Sub CreateMyTool()
'Makes a toolbar called "cbMyTool"

Dim cbMyTool As CommandBar
Dim cbbMyButton As CommandBarButton

'Make the toolbar
Set cbMyTool = CommandBars.Add

'Now we add a button to the toolbar. FaceId is the button's icon,
'OnAction is the macro to run, if the button is clicked, and
'ToolTipText is the text that will show when the mouse hovers.
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 645
   .TooltipText = "Do magic with numbers"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 940
   .TooltipText = "Show a message box"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 385
   .TooltipText = "Functions"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 1662
   .TooltipText = "Constraints"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 225
   .TooltipText = "Lock cells"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 154
   .TooltipText = "Delete all"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 155
   .TooltipText = "Return"
End With

'Before we finish, the toolbar gets a name, width and
'is put on the screen.
With cbMyTool
   .Name = "Shortcuts"
   .Left = Application.ActiveWindow.Width
   .Top = Application.ActiveWindow.Height
   .Visible = True
   .Width = 300
End With

BeforeExit:
Set cbMyTool = Nothing
Set cbbMyButton = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " CreateMyTool", vbOKOnly + vbCritical, "Error"
Resume BeforeExit
End Sub

Sub DeleteMyTool()
'Removes the toolbar "Shortcuts".
'If it doesn't exist we get an error,
'and that is why we use On Error Resume Next.

On Error Resume Next

CommandBars("Shortcuts").Delete

On Error GoTo 0

End Sub

Sub RemoveToolBar()
'Removes all not-built-in menus and
'toolbars and restores Excel's standard
'menu. This is an alternative to
'DeleteMyTool, that only removes
'the named toolbar Shortcuts.

Dim cbBar As CommandBar

On Error GoTo ErrorHandle

For Each cbBar In Application.CommandBars
    If Not cbBar.BuiltIn Then cbBar.Delete
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description & " RemoveMenu", vbOKOnly, "Error"
End Sub

Download example workbook : Menu Bar Add Custom Remove All.xlsm
Click to expand...
Hi Logit,

Thanks for revert.

The macro you suggested in creating one section under the menu "Add ins"

Can we create separte menu altogether? New menu not to come under "Add Ins" but to be a separate menu similar to File menu, Insert Menu etc.

Further is it possible to have description/text against each icons? (e.g. against 1st icon in the custom menu, i have macro called 'print all'. so it it possible to have text called 'print all' against face id of that icon in menu?

I am using office 365 64 bit, screenshot of newly added menu attached.
 
Upvote 0
Logit said:
See if this resource helps :

How to add a custom Ribbon tab using VBA?
Click to expand...
Hi Logit,

thanks for the revert.

I referred that page you suggested.
I am not much expert with macros and so I am not clear as to what is exactly to be done / which code is to be copied.

Also that page dates back to 9 years, so not sure if it is relevant with office 365.

thanks.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Dynamic Email Generation via VBA - Extracting & Sending Data Across Multiple Sheets
Replies
0
Views
166
Jess1234556
J
B
  • Question Question
assigning excel add-in macro to command button creates a workbook connection to the addin
Replies
1
Views
84
jkpieterse
jkpieterse
M
  • Question Question
Custom Format of Slicer on Excel for MAC
Replies
0
Views
197
Macca1962
M
T
  • Question Question
Excel Scripts Permissions Issue - Macro Workaround?
Replies
1
Views
40
Sam_D_Ben
Sam_D_Ben
M
  • Solved
How to move custom VBA/ribbons
2
Replies
12
Views
903
missingsc
M

Share this page

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