Help with personal.xlsb workbook

[pingpongplaya10]

Hello all,

I placed the following code in the Personal.xlsb file however it does not seem to be working (it worked in a normal spreadsheet). It's supposed to create a menu item with right-clicking on a cell. Can someone please take a look and explain what i'm doing wrong? Thanks in advance.

Code in the "ThisWorkbook" file of Personal.xlsb:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    'remove out custom menu before we leave
    Run ("DeleteCustomMenu")
    
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    
    Run ("DeleteCustomMenu")   'remove possible duplicates
    Run ("BuildCustomMenu")    'build new menu
    
End Sub

Code in the "Module1" file of Personal.xlsb:

Option Explicit
Private Sub BuildCustomMenu()
Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer
    
    'add a 'popup' control to the cell commandbar (menu)
    Set ctrl = Application.CommandBars("Cell").Controls.Add _
                (Type:=msoControlButton, Before:=1)
    ctrl.Caption = "Sum Cells"
    ctrl.Tag = "test"
    ctrl.OnAction = "SumCells"
End Sub
Private Sub DeleteCustomMenu()
Dim ctrl As CommandBarControl
    
    'go thru all the cell commandbar controls and delete our menu item
    For Each ctrl In Application.CommandBars("Cell").Controls
        If ctrl.Caption = "Sum Cells" Then ctrl.Delete
    Next
    
End Sub
Private Sub SumCells()
Dim EndCell As String
Dim StartCell As String, StartCell2 As String
StartCell = ActiveCell.Address
StartCell2 = ActiveCell.Offset(-1, 0).Address
Range(ActiveCell.Address, Selection.End(xlUp)).Select
EndCell = Selection.End(xlUp).Address
    
Range(StartCell).Value = "=sum(" & EndCell & ":" & StartCell2 & ")"
Range(StartCell).Select
End Sub

 

[Andrew Poulsom]

The Workbook_SheetBeforeRightClick event procedure will fire when you right click in your Personal.xlsb not the active workbook, and that can't happen because it's hidden. You need Application level events:

http://www.cpearson.com/Excel/AppEvent.aspx

 

[pingpongplaya10]

Ahh, that makes sense. Thanks for the link, I will check it out.