none
Know when a column is hidden or unhidden RRS feed

  • Question

  • Thanks for taking the time to read my question.

    Is there a way to know when a user hides or unhides a column in a worksheet when they do it?

    Thanks,
    Brad

    Thursday, April 26, 2018 2:00 AM

Answers

  • I took a chance....

    <commands> needs to go before the <ribbon> part.

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    	<commands>
    		<command idMso="ColumnsHide" onAction="Column_Hide_Macro"/>
    		<command idMso="ColumnsUnhide" onAction="Column_UnHide_Macro"/>
    	</commands>
    	<ribbon>
            ...
            </ribbon>
    </customUI>
    

    • Marked as answer by mbrad Thursday, April 26, 2018 2:32 AM
    Thursday, April 26, 2018 2:32 AM

All replies

  • Update:

    I found this page which seems the way to go: https://stackoverflow.com/questions/24777074/trigger-event-in-excel-vba-when-row-or-column-is-hidden

    However when I put that into my xml it fails.

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    	<ribbon>
    		<tabs>
    			<tab id="FTFB" label="FindTop FindBottom" insertAfterMso="TabView">
    				<group id="FTFBg" label="Comp And Stats">
    					<button id="customButton1" label="FindTop FindBottom" size="large" onAction="AddFindTopFindBottom" image="ftfb" />
    				</group>
    				<group id="HuH" label="Comp And Stats">
    					<checkBox id="cbC" label="(un)Hide C" onAction="cbC" />
    					<checkBox id="cbD" label="(un)Hide D" onAction="cbD" />
    					<checkBox id="cbE" label="(un)Hide E" onAction="cbE" />
    					<checkBox id="cbF" label="(un)Hide F" onAction="cbF" />
    					<checkBox id="cbG" label="(un)Hide G" onAction="cbG" />
    					<checkBox id="cbH" label="(un)Hide H" onAction="cbH" />
    					<checkBox id="cbI" label="(un)Hide I" onAction="cbI" />
    					<checkBox id="cbJ" label="(un)Hide J" onAction="cbJ" />
    					<checkBox id="cbK" label="(un)Hide K" onAction="cbK" />
    				</group>
    			</tab>
    		</tabs>
    	</ribbon>
    	<commands>
    		<command 
    			idMso="ColumnsHide"
    			onAction="ColumnHide_onAction" />
    		<command 
    			idMso="ColumnsUnhide"
    			onAction="ColumnUnhide_onAction" />
    	</commands>
    </customUI>

    The <commands> part does not seem to work when I check the XML in CustomUI Editor.

    Any help would be great.

    Thanks,
    Brad

    Thursday, April 26, 2018 2:27 AM
  • I took a chance....

    <commands> needs to go before the <ribbon> part.

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    	<commands>
    		<command idMso="ColumnsHide" onAction="Column_Hide_Macro"/>
    		<command idMso="ColumnsUnhide" onAction="Column_UnHide_Macro"/>
    	</commands>
    	<ribbon>
            ...
            </ribbon>
    </customUI>
    

    • Marked as answer by mbrad Thursday, April 26, 2018 2:32 AM
    Thursday, April 26, 2018 2:32 AM
  • Yes that works, and also with the 2007 schema.

    Can also do it the old school way

    ''' code in a normal module
    Private mcColHide As clsColHide
    
    Sub Auto_Open()
        StartColumnButtonEvents
    End Sub
    
    Sub StartColumnButtonEvents()
        Set mcColHide = New clsColHide
    End Sub
    
    ''' code in a class named clsColHide
    Option Explicit
    Public WithEvents cbHide As CommandBarButton
    Public WithEvents cbUnHide As CommandBarButton
    
    Private Sub Class_Initialize()
        With CommandBars("Column")
            Set cbHide = .FindControl(ID:=886)
            Set cbUnHide = .FindControl(ID:=887)
        End With
    End Sub
    
    Private Sub cbHide_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
        Debug.Print "Hide", Selection.Columns.Address
    End Sub
    
    Private Sub cbUnHide_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
        Debug.Print "UnHide", Selection.Columns.Address
    End Sub
    

    Thursday, April 26, 2018 7:35 AM
    Moderator