none
Triggering Outline Hide Detail event (clicking minus sign on Outline Symbols bar) RRS feed

  • Question

  • I'd like to trigger VBA macro every time user clicks on minus sign that hides grouped columns.

    As far as i know it is not possible using build-in Excel events. However, is it doable with Custom UI XML in a manner similar to Hide/Unhide Column presented here?: https://social.msdn.microsoft.com/Forums/office/en-US/a5d44c36-1953-49b5-9f22-e80fa06a1175/is-there-any-way-to-trigger-an-event-when-a-column-is-hidden

    If so, which idMso corresponds to a command described? Please note that I'm not interested in OutlineHideDetail command, as it is only triggered when "Hide Detail" on Data Ribbon is clicked, and NOT when minus sign is clicked.

    I'm using Office 2010.

    Wednesday, July 13, 2016 11:55 AM

Answers

  • >>>If so, which idMso corresponds to a command described? Please note that I'm not interested in OutlineHideDetail command, as it is only triggered when "Hide Detail" on Data Ribbon is clicked, and NOT when minus sign is clicked.

    According to your description, I have searched over Office 2010 Help Files: Office Fluent User Interface Control Identifiers, and find that there are PlusSign and MinusSign idMso. I have tried to create Ribbon(XML) and use OnAction callback, unfortunately, I am fail. This is not right idMso.

    Ribbon(Xml) code:
    <commands>
      <command idMso="PlusSign" onAction="OnPlus"/>
      <command idMso="MinusSign" onAction="OnMinus"/>
      <command idMso="ColumnsUnhide" onAction="ColumnUnhide_onAction"/>
    </commands>

    Ribbon callback code:
    public void OnPlus(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }
    
    public void OnMinus(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }
    
    public void ColumnUnhide_onAction(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }

    You will find that MessageBox show when you click ColumnUnhide, but OnPlus and OnMinus then nothing happen.

    Download Office 2010 Help Files: Office Fluent User Interface Control Identifiers

    In addition if you have any feedback for Excel, please feel free to submit them to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding. 
    Thursday, July 14, 2016 5:27 AM

All replies

  • >>>If so, which idMso corresponds to a command described? Please note that I'm not interested in OutlineHideDetail command, as it is only triggered when "Hide Detail" on Data Ribbon is clicked, and NOT when minus sign is clicked.

    According to your description, I have searched over Office 2010 Help Files: Office Fluent User Interface Control Identifiers, and find that there are PlusSign and MinusSign idMso. I have tried to create Ribbon(XML) and use OnAction callback, unfortunately, I am fail. This is not right idMso.

    Ribbon(Xml) code:
    <commands>
      <command idMso="PlusSign" onAction="OnPlus"/>
      <command idMso="MinusSign" onAction="OnMinus"/>
      <command idMso="ColumnsUnhide" onAction="ColumnUnhide_onAction"/>
    </commands>

    Ribbon callback code:
    public void OnPlus(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }
    
    public void OnMinus(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }
    
    public void ColumnUnhide_onAction(Microsoft.Office.Core.IRibbonControl control, ref bool cancelDefault)
    {
        System.Windows.Forms.MessageBox.Show("Click me");
        cancelDefault = false;
    }

    You will find that MessageBox show when you click ColumnUnhide, but OnPlus and OnMinus then nothing happen.

    Download Office 2010 Help Files: Office Fluent User Interface Control Identifiers

    In addition if you have any feedback for Excel, please feel free to submit them to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding. 
    Thursday, July 14, 2016 5:27 AM
  • >>>You will find that MessageBox show when you click ColumnUnhide, but OnPlus and OnMinus then nothing happen.

    David, thanks for your reply.

    Just to clarify my answer, this is a picture of a button that I'm interested in:

    http://i.imgur.com/ikMXFHi.png


    I'm not sure if I understood your answer correctly: did you manage to trigger ColumnUnhide event by clicking on minus sign on Outline Symbols bar? If so, how did you do that? I've tried to reproduce your idea but with no success (ColumnUnhide is not triggered).

    Code used:

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" >
    	<commands>
      		<command idMso="ColumnsUnhide" onAction="ColumnsUnhide_onAction"/>
    	</commands>
    </customUI >
    Option Explicit
    
    'Callback for ColumnsUnhide onAction
    Sub ColumnsUnhide_onAction(control As IRibbonControl, ByRef cancelDefault)
        MsgBox "ColumnsUnhide_onAction triggered."
        cancelDefault = False
    End Sub

    Here, one can download a sample .xlsm file containing code above:

    https://db.tt/aZxSL1U8


    Tuesday, July 19, 2016 9:45 AM
  • >>>did you manage to trigger ColumnUnhide event by clicking on minus sign on Outline Symbols bar? If so, how did you do that? I've tried to reproduce your idea but with no success (ColumnUnhide is not triggered).

    According to your description, I didn't manage to trigger ColumnUnhide event by clicking on minus sign on Outline Symbols bar. I have searched over Excel 2010 Fluent User Interface Control Identifiers, unfortunately, I am not able to find idMso of minus sign on Outline Symbols bar.

    So I suggest that you could submit any feedback to Excel UserVoice:

    https://excel.uservoice.com/

    Thanks for your understanding.

    • Proposed as answer by David_JunFeng Thursday, July 21, 2016 2:41 AM
    Wednesday, July 20, 2016 8:36 AM
  • At the suggestion of David_JunFeng, an idea to add functionality discussed was added to Excel UserVoice.

    One can vote and comment on idea here:

    Excel UserVoice petition

    Monday, August 22, 2016 8:20 AM