none
How do I know if a CommandBars("List Range Popup") orCommandBars("Cell") is going to or is displaying? RRS feed

  • Question

  • I am currently writing code for Excels Context Menu bars that appear on a right click.

    I listen for this event;

    BeforeRightClick(Sh As Object, Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean)

    I create the menu;

    Private Sub addToContextMenu()
            If ContextMenu Is Nothing Then
                ContextMenu = Application.CommandBars("Cell")
            End If
    
            iButton = ContextMenu.Controls.Add(Type:=MsoControlType.msoControlButton, Id:=1, Before:=1, Temporary:=True)
    
            AddHandler iButton.Click, AddressOf iButton_Click
    
            ' Add Our Ipas button
            With iButton
                .FaceId = 5596
                .Caption = "MyButton"
            End With
    
            ContextMenu.Controls(2).BeginGroup = True
        End Sub

    I cannot work out how to know when I should be adding the List Range Popup commandbar or cell commandbar?

    Monday, April 13, 2015 5:44 PM

Answers

  • OK I think I follow but unless you only want your popup for only particular specified ranges probably easier to add buttons to the popups you think you might want (either for the entire session or when given a sheet or workbook is active), but indeed it depends on your overall scenario.

    There are right click events for worksheets and charts, but no events at all for several things such as shapes. If say you only want to add (and delete) your button on a range which might also be a table or pivot table, in the right click event you could do something like this, again air-code

    ' in the right-click event

    On Error Resume Next
    Set obj = Target.ListObject
    If not obj is Nothing Then
       sBar = "List Range Popup"
    Else
       Set obj = Target.PivotCell
        If not obj Is Nothing Then
             sBar = "PivotTable Context Menu" ' double check that, there are others
    etc
    Else: sBar = "Cell"
    End If
    ' resume normal error handling
    Set cbr = CommandBars(sBar)

    ' add the button and trap its withevents, delete the button in the click event

    Personally I've never done it like that before!


    Tuesday, April 14, 2015 2:50 PM
    Moderator

All replies

  • What Office version are you talking about?

    The fact is that Command Bars were deprecated and not used any longer. You need to use the Fluent UI (aka Ribbon UI) controls instead. See Customizing Context Menus in Office 2010 for more information.

    Monday, April 13, 2015 7:22 PM
  • Sorry guys. Office 2007 and I'm pretty sure only command bars are available for this old software.
    Monday, April 13, 2015 7:41 PM
  • Re:  menu commands

    Office 2007 uses the Ribbon, but CommandBars can be programmed and displayed.
    Same for Office 2010.
    Office 2013 is different - don't expect commandbars to be of much use.

    Normally, custom commandbars/menus are added when the workbook opens and removed when the workbook closes.
    I see no benefit to adding/removing them on the fly.
    '---

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, November 1, 2016 12:50 AM
    Monday, April 13, 2015 9:34 PM
  • Thanks James. You are right 2007 does use both, however, I am talking about context menus which I think in 2007 was only command bars and it was only in 2010 that they changed. As to seeing no benefit to adding or removing them on the fly, I think Microsoft disagrees as they called them context menus and I also disagree, I want a menu based on what the person clicks on. Soooooo if you have a crystal ball that tells you what a person will click on before they have please let me know ;-)
    • Edited by donky73 Tuesday, April 14, 2015 4:27 AM
    Tuesday, April 14, 2015 4:26 AM
  • I have a number of workbooks where I add options to the context menus. This works fine in all versions of Excel upto 2013/365. I'm calling them context menus because their content depends on the context in which you access them.

    If what you are adding to the menu is dependent on the content of the cell, you should be able to use the BeforeRightClick method as you have shown. In that routine look at the content of the activecell and add your options as required.

    You also need to make sure you remove your option afterwards as Excel will let you add as many options with the same name as you want, so if you don't remove them, you end up with a very messy menu. One way is to use the BeforeRightClick code to first remove any of you custom options before adding the correct one for the current cell.

    Regards

    Gordon

    Tuesday, April 14, 2015 5:29 AM
  • Thanks Gordon, unfortunately Excel serves up different commandbars depending on what the user clicks on.  If it is a cell it is different to if it is a table.
    Tuesday, April 14, 2015 5:39 AM
  • As you say many of Excel's context menus are still Commnandbars, and a different ones pop up depending on the selection

    You can get a list of them and work out which you need (air code)

    For each cbr in Commandbars
    i = i + 1
    cells(i,1) = cbr.name
    next

    Then to look at one in more detail
    i = 0
    Set cbr = CommandBars("List Range Popup") ' for tables
    for each ctl in cbr.Controls
    cells(i,2) = ctl.Caption
    Next

    Your first step is to add buttons to the commandbars you want, positioned as appropriate. If using VBA you can add the full address to the macro in the OnAction property, otherwise reference each button to your WithEvents handler, each to its own event stub (can also do this in VBA). Normally you'd set a reference directly to the control at the same time it is added.

    If your buttons should only be available with given sheets or workbooks you will need to trap appropriate application level events, and delete/create or toggle Visible or Enabled to suit.

    When you add your buttons ensure the Temporary:=True, but ideally add//remove all buttons in each session. In your open event before adding new buttons best double check no buttons got left if for any reason your close event failed to remove them.

    Tuesday, April 14, 2015 1:27 PM
    Moderator
  • Thank you Peter.  Unfortunately you are missing the point.

    1. The ONLY event I know of in Excel 2007 which indicates that the user has used the right mouse on an object is the BeforeRightClick Event.  I am happy to be wrong on this.
    2. During this event a developer has the opportunity to remove / add buttons to a context menu.
    3. There are different context menu commandbars depending on what the user has clicked on. There are more than 2 but right now I would be happy to work out "List Range Popup" and "Cell"
    4. if the user clicks on a named table then adding a button to CommandBars("Cell") is pointless as a CommandBars("List Range Popup") will be shown.

    So the question is how do I know what Excel is going to show?

    1. BeforeRightClick event does not indicate it. The command bar is not visible so I cannot use that property
    2. I thought about adding the button to all bars and then only one would be visible of course but then the event of the button fires multiple times.  (also the command bar is not visible anymore by the time the button event fires so I again cannot check the visible property of the commandbar.)
    Tuesday, April 14, 2015 2:08 PM
  • OK I think I follow but unless you only want your popup for only particular specified ranges probably easier to add buttons to the popups you think you might want (either for the entire session or when given a sheet or workbook is active), but indeed it depends on your overall scenario.

    There are right click events for worksheets and charts, but no events at all for several things such as shapes. If say you only want to add (and delete) your button on a range which might also be a table or pivot table, in the right click event you could do something like this, again air-code

    ' in the right-click event

    On Error Resume Next
    Set obj = Target.ListObject
    If not obj is Nothing Then
       sBar = "List Range Popup"
    Else
       Set obj = Target.PivotCell
        If not obj Is Nothing Then
             sBar = "PivotTable Context Menu" ' double check that, there are others
    etc
    Else: sBar = "Cell"
    End If
    ' resume normal error handling
    Set cbr = CommandBars(sBar)

    ' add the button and trap its withevents, delete the button in the click event

    Personally I've never done it like that before!


    Tuesday, April 14, 2015 2:50 PM
    Moderator
  • Thanks Peter that works.
    Tuesday, April 14, 2015 4:24 PM
  • Thanks Gordon, unfortunately Excel serves up different commandbars depending on what the user clicks on.  If it is a cell it is different to if it is a table.

    Since the only one you care about is the Cell commandbar, Add or remove the custom items to it in the Before Right click routine. If the user right-clicked something that triggers a different commandbar, it won't matter that you've added an option to the Cell bar as they won't see it.

    If you want the same behaviour in a Table, modify the Table commandbar in the Before Right Click routine as well.

    What I'm saying is that you need one routine that looks after all the commandbars you want to modify. On each call, work out what options should be on the commandbar, remove all your custom items, then add the correct ones to each one.

    Wednesday, April 15, 2015 12:53 AM