locked
Check a checkbox on customUI ribbon with VBA RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I am wondering how to check a checkbox on my custom ribbon if Column C is not hidden, but if it is hidden then uncheck the checkbox.

    I am able to hide and unhide column C by unchecking and checking the checkbox, but I can't set the state of the checkbox when I open the workbook.

    Any help would be greatly appreciated.

    thanks,
    Brad

    My Code:

    Public b_cbC As Boolean
    Public b_cbD As Boolean
    Public b_cbE As Boolean
    Public b_cbF As Boolean
    Public b_cbG As Boolean
    Public b_cbH As Boolean
    Public b_cbI As Boolean
    Public b_cbJ As Boolean
    Public b_cbK As Boolean
    Private Sub Workbook_Open()
    Dim wb As Workbook
    
    On Error GoTo Open_Err
        If Range(Columns(3), Columns(3)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(4), Columns(4)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(5), Columns(5)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(6), Columns(6)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(7), Columns(7)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(8), Columns(8)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(9), Columns(9)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(10), Columns(10)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(11), Columns(11)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
    Open_Exit:
        Exit Sub
    Open_Err:
        If Err.Number = 1004 Then
            Set wb = Workbooks.Add
            Resume
        Else
            MsgBox Err.Number & ", " & Err.Description
            Resume Open_Exit
        End If
    End Sub
    
    
    Function ShowCol(tCol As Integer, tShow As Boolean)
        If tShow = True Then
            Range(Columns(tCol), Columns(tCol)).EntireColumn.Hidden = False
        Else
            Range(Columns(tCol), Columns(tCol)).EntireColumn.Hidden = True
        End If
    End Function
    
    Sub cbC(control As IRibbonControl, pressed As Boolean)
        If pressed = True Then
            ShowCol 3, True
        Else
            ShowCol 3, False
        End If
    End Sub
    
    Public Sub ColumnHide_onAction(control As IRibbonControl, ByRef cancelDefault)
    Dim rng As Range
    '
    ' Code for onAction callback. Ribbon control command
    '
    If Range(Selection.Address).Column < 11 Then
        Set rng = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count)
            'MsgBox "Ribbon Column Hide"
            For x = Range(Selection.Address).Column To rng.Column
                Debug.Print x
            Next x
    End If
    cancelDefault = False
    
    End Sub
    Public Sub ColumnUnhide_onAction(control As IRibbonControl, ByRef cancelDefault)
    Dim rng As Range
    '
    ' Code for onAction callback. Ribbon control command
    '
    If Range(Selection.Address).Column < 11 Then
        Set rng = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count)
            'MsgBox "Ribbon Column Unhide"
            
            For x = Range(Selection.Address).Column + 1 To rng.Column - 1
                Debug.Print x
            Next x
    End If
    cancelDefault = False
    End Sub
    
    Public Sub getPressed(control As IRibbonControl, ByRef returnedVal)
    '
    ' Code for getPressed callback. Ribbon control checkBox
    '
        If control.ID = "cbC" Then
            returnedVal = b_cbC
        End If
        If control.ID = "cbD" Then
            returnedVal = b_cbD
        End If
        If control.ID = "cbE" Then
            returnedVal = b_cbE
        End If
        If control.ID = "cbF" Then
            returnedVal = b_cbF
        End If
        If control.ID = "cbG" Then
            returnedVal = b_cbG
        End If
        If control.ID = "cbH" Then
            returnedVal = b_cbH
        End If
        If control.ID = "cbI" Then
            returnedVal = b_cbI
        End If
        If control.ID = "cbJ" Then
            returnedVal = b_cbJ
        End If
        If control.ID = "cbK" Then
            returnedVal = b_cbK
        End If
    End Sub
    
    

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

    Thursday, April 26, 2018 4:15 AM

Answers

  • First, I would suggest you make a test with one checkbox and column.

    For your current code, b_cbC dependes on Column 11. You need to check whether Column 11 is visible or hidden.

    Second, you need to call "customRibbon.Invalidate" after you set b_cbC value.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mbrad Monday, May 14, 2018 9:37 PM
    Thursday, May 10, 2018 7:07 AM

All replies

  • Hi mbrad,

    For your request, you could try “OnLoad” event.

    Here is a test demo code:

    Dim customRibbon As IRibbonUI
    Public b_cbC As Boolean
    
    Function ShowCol(tCol As Integer, tShow As Boolean)
        If tShow = True Then
            Range(Columns(tCol), Columns(tCol)).EntireColumn.Hidden = False
        Else
            Range(Columns(tCol), Columns(tCol)).EntireColumn.Hidden = True
        End If
    End Function
    
    Sub cbC(control As IRibbonControl, pressed As Boolean)
        If pressed = True Then
            ShowCol 3, True
        Else
            ShowCol 3, False
        End If
    End Sub
    
    Public Sub ColumnHide_onAction(control As IRibbonControl, ByRef cancelDefault)
    Dim rng As Range
    '
    ' Code for onAction callback. Ribbon control command
    '
    If Range(Selection.Address).Column < 11 Then
        Set rng = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count)
            'MsgBox "Ribbon Column Hide"
            For x = Range(Selection.Address).Column To rng.Column
                Debug.Print x
            Next x
    End If
    cancelDefault = False
    
    End Sub
    Public Sub ColumnUnhide_onAction(control As IRibbonControl, ByRef cancelDefault)
    Dim rng As Range
    '
    ' Code for onAction callback. Ribbon control command
    '
    If Range(Selection.Address).Column < 11 Then
        Set rng = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count)
            'MsgBox "Ribbon Column Unhide"
            
            For x = Range(Selection.Address).Column + 1 To rng.Column - 1
                Debug.Print x
            Next x
    End If
    cancelDefault = False
    End Sub
    
    Public Sub getPressed(control As IRibbonControl, ByRef returnedVal)
    '
    ' Code for getPressed callback. Ribbon control checkBox
    '
        If control.ID = "cbC" Then
            returnedVal = b_cbC
        End If
    End Sub
    
    Public Sub onLoad(ribbon As IRibbonUI)
        Set customRibbon = ribbon
        If Range(Columns(3), Columns(3)).EntireColumn.Hidden = True Then b_cbC = False Else b_cbC = True
        customRibbon.Invalidate
    End Sub
    
    

    UI

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onLoad">
    	<commands>
    		<command idMso="ColumnsHide" onAction="ColumnHide_onAction"/>
    		<command idMso="ColumnsUnhide" onAction="ColumnUnhide_onAction"/>
    	</commands>
    	<ribbon>
    		<tabs>
    			<tab id="FTFB" label="FindTop FindBottom" insertAfterMso="TabView">
    				<group id="FTFBg" label="Find Top Find Bottom">
    					<button id="customButton1" label="FindTop FindBottom" size="large" onAction="AddFindTopFindBottom" image="ftfb" />
    				</group>
    				<group id="HuH" label="Show Hide">
    					<checkBox id="cbC" label="Show/Hide C" getPressed="getPressed" onAction="cbc" />
    				
    				</group>
    			</tab>
    		</tabs>
    	</ribbon>
    </customUI>

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 6:41 AM
  • Hi Tao,

    Thank you for your reply. So sorry mine is so slow.

    I have added your code into my XML and into my .xlam file, however when I open a new worbook none of the checkboxes are checked.

    If I check them all manually and then uncheck "Show/Hide C" then column C is hidden

    If I change to Sheet2, column C is not hidden but the checkbox "Show/Hide C" is still unchecked.

    How do I set the checkboxes for the active sheet? or if I manually hide column C then Excel should "know" and uncheck "Show/Hide C".

    Thanks again for all your help.

    Brad

    Friday, May 4, 2018 12:02 PM
  • >>How do I set the checkboxes for the active sheet?

    You need to set the "b_cbC" in "Workbook_SheetChange".

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     If Range(Columns(3), Columns(3)).EntireColumn.Hidden = True Then b_cbC = False Else b_cbC = True
     customRibbon.Invalidate
    End Sub


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 7, 2018 6:42 AM
  • Thanks for your reply once again :-)

    I forgot to add that I do have code at the workbook level, I just did not have it on the Sheet Change, which I've now added:

    Public b_cbC As Boolean
    Public b_cbD As Boolean
    Public b_cbE As Boolean
    Public b_cbF As Boolean
    Public b_cbG As Boolean
    Public b_cbH As Boolean
    Public b_cbI As Boolean
    Public b_cbJ As Boolean
    Public b_cbK As Boolean
    Private Sub Workbook_Open()
    Dim wb As Workbook
    
    On Error GoTo Open_Err
        If Range(Columns(3), Columns(3)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(4), Columns(4)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(5), Columns(5)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(6), Columns(6)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(7), Columns(7)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(8), Columns(8)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(9), Columns(9)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(10), Columns(10)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(11), Columns(11)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
    Open_Exit:
        Exit Sub
    Open_Err:
        If Err.Number = 1004 Then
            Set wb = Workbooks.Add
            Resume
        Else
            MsgBox Err.Number & ", " & Err.Description
            Resume Open_Exit
        End If
    End Sub
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        If Range(Columns(3), Columns(3)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(4), Columns(4)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(5), Columns(5)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(6), Columns(6)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(7), Columns(7)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(8), Columns(8)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(9), Columns(9)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(10), Columns(10)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
        If Range(Columns(11), Columns(11)).EntireColumn.Hidden = True Then b_cbC = False Else c_cbC = True
    End Sub

    Is that correct? My checkboxes are still not checked. I suspect that's because the active workbook is "Book1.xlsx"?

    Do I need to make a Class or something for it to apply to all open workbooks?

    Thanks again for ALL YOUR HELP. I truly appreciate it.

    Brad

    Tuesday, May 8, 2018 4:39 PM
  • Will it work if there is only one workbook?

    If you want to apply for Excel application instead of specific workbook, I think you could try Excel VSTO solution isntead of WorkBook macro.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 9, 2018 3:15 AM
  • Hi Tao,

    I don't think so.(?)

    Unfortunately my only option is macros. I have this in a .xlam (add in) file.

    thanks,
    Brad

    Thursday, May 10, 2018 3:06 AM
  • First, I would suggest you make a test with one checkbox and column.

    For your current code, b_cbC dependes on Column 11. You need to check whether Column 11 is visible or hidden.

    Second, you need to call "customRibbon.Invalidate" after you set b_cbC value.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mbrad Monday, May 14, 2018 9:37 PM
    Thursday, May 10, 2018 7:07 AM
  • Hi Tao,

    Thank you for all your replies. I now have all of my checkboxes checking when the file opens! It checks each column and then acts accordingly.

    I truly appreciate your help.

    Brad

    Monday, May 14, 2018 9:37 PM