Pages

Thursday, August 23, 2012

Add a new button on mouse right click menu and assign macro to it

If you want to add a new  button on mouse right click menu and assign macro to it.  Snapshot below -



Here is the code- 

Add below code to workbook module 
Private Sub Workbook_Open()
    On Error Resume Next
    'Delete the new button if already exists
    ' name of the new button is "New Button"
    Application.CommandBars("Cell").Controls("New Button").Delete
    'run a macro to add a new button on mouse right click
    Call add_new_button
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    ' delete the btton when workbook is closed.
    ' name of the new button is "New Button"
    Application.CommandBars("Cell").Controls("New Button").Delete
End Sub

Add below code to module1 or any new module

Option Explicit
Sub add_new_button()
    
    ' macro to add new button with name "New Button"
    Dim cBut        As CommandBarControl
    On Error Resume Next
    ' name of the new button "New Button"
    Application.CommandBars("Cell").Controls("New Button").Delete
    Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, Temporary:=True)
    'if you want to add this button at the top use
    'Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, before:=1, Temporary:=True)
    
    
    ' name of the new button is "New Button"
    cBut.Caption = "New Button"
    cBut.FaceId = 481

    ' name of macro which you want to run when u will click on it
    cBut.OnAction = "new_button_macro"
    
End Sub


Sub new_button_macro()
    MsgBox "www.excelvbamacros.com"
End Sub
 




1 comment:

  1. Also Visit

    http://www.excelvbamacros.com/2012/04/blog-post.html

    ReplyDelete