none
How to add a checkbox to the ribbon that by deafult is checked? RRS feed

  • Question

  • Hi,

    I would like to add to Excel 2010 ribbon a checkbox that by default is checked.

    For example by pressing the check box the refrence style is changing to R1C1, By unchecking it the reference style is A1C1.

    By default, the checkbox should be checked (means reference style is A1C1).

    Thanks,


    Guy Zommer

    Sunday, September 23, 2012 5:35 AM

All replies

  • You want to do that from ExcelVBA or in VSTO .Net?

    I'm asking because any modification on the ribbon from VBA, you can make in Menu\addin only


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, September 23, 2012 11:42 PM
    Answerer
  • Hello,

    From VBA.

    Thanks


    Guy Zommer

    Monday, September 24, 2012 4:48 AM
  • Take look on this: http://support.microsoft.com/kb/830502

    I use MsoControlButtonDropdown for this (pressed as mark).


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Monday, September 24, 2012 7:51 AM
    Answerer
  • If you want your control to be part of the ribbon, rather than a commandbar in the add-ins tab, then you need to use a combination on ribbon xml and VBA.

    ribbon xml in file. To be available for all workbooks this will need to be an addin file.

    <customUI  xmlns="http://schemas.microsoft.com/office/2009/07/customui" >
    	<ribbon >
    		<tabs >
    			<tab 
    				id="Tab1"
    				label="Tab1">
    				<group 
    					id="Group1"
    					label="Group1">
    					<checkBox 
    						id="Checkbox1"
    						label="Reference Style R1C1"
    						getPressed="Checkbox1_getPressed"
    						onAction="Checkbox1_onAction"/>
    				</group >
    			</tab >
    		</tabs >
    	</ribbon >
    </customUI >
    


    Thisworkbook object code.

    Private Sub Workbook_Open()
    
        g_blnCheckboxState = True
        Application.ReferenceStyle = xlR1C1
        
    End Sub

    Standard code module code

    Public g_blnCheckboxState As Boolean
    Public Sub Checkbox1_getPressed(control As IRibbonControl, ByRef returnedVal)
    '
    ' Code for getPressed callback. Ribbon control checkBox
    '
        returnedVal = Application.ReferenceStyle
    
    End Sub
    Public Sub Checkbox1_onAction(control As IRibbonControl, pressed As Boolean)
    '
    ' Code for onAction callback. Ribbon control checkBox
    '
        If pressed Then
            Application.ReferenceStyle = xlR1C1
            g_blnCheckboxState = True
        Else
            Application.ReferenceStyle = xlA1
            g_blnCheckboxState = False
        End If
        
    End Sub



    Cheers,

    Andy
    www.andypope.info

    Monday, September 24, 2012 8:35 AM
  • Thanks a lot!


    Guy Zommer

    Monday, September 24, 2012 8:43 AM
  • Andy is showing you a way, works only with file, where you extract Ziped XLSx/m code
    If you want to do it from VBA code pressed button is seems to be only way.

    With Application.CommandBars("MenuBarName").Controls("Caption")
            If .State = msoButtonUp Then
               .State = msoButtonDown
            Else
               .State = msoButtonUp
            End If
        End With
    
    '...check
    If Application.CommandBars("MenuBarName").Controls("Caption") _
        .State = msoButtonDown Then MsgBox "Pressed", vbInformation, APPNAME


    Also you can modyf caption after pressing and use normal button.

    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Monday, September 24, 2012 9:37 AM
    Answerer
  • Hi,

    I checked the code, what I need is by default the reference style will be XlA1, means when you add a new sheet the check box is checked (selected) and the reference style is xlA1. I tried to change the code to do the opposite but when I add a new worksheet the checkbox is unchecked.

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    g_blnCheckboxState = True
    Application.ReferenceStyle = xlA1

    End Sub

    Public g_blnCheckboxState As Boolean

    Public Sub Checkbox1_getPressed(control As IRibbonControl, ByRef returnedVal)
    '
    ' Code for getPressed callback. Ribbon control checkBox
    '
        returnedVal = Application.ReferenceStyle

    End Sub
    Public Sub Checkbox1_onAction(control As IRibbonControl, pressed As Boolean)
    '
    ' Code for onAction callback. Ribbon control checkBox
    '
        If pressed Then
            Application.ReferenceStyle = xlA1
            g_blnCheckboxState = True
        Else
            Application.ReferenceStyle = xlR1C1
            g_blnCheckboxState = False
        End If
       
    End Sub


    Guy Zommer

    Monday, September 24, 2012 12:56 PM
  • Not 100% sure I understand how you want this to work as the setting will apply to all sheets in the workbook. So if the setting xlR1C1 is applied it will apply to the new sheet.

    Anyway if you need to alter the state, other than at the Workbook_Open event, you will need to invalidate the control.

    Extra code in Thisworkbook object

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    
        g_blnCheckboxState = True
        Application.ReferenceStyle = xlR1C1
        g_rbxUI.InvalidateControl "Checkbox1"
        
    End Sub
    

    Extra code in standard code module

    Public g_rbxUI As IRibbonUI
    
    Public Sub rbx_onLoad(ribbon As IRibbonUI)
    '
    ' Code for onLoad callback. Ribbon control customUI
    '
        Set g_rbxUI = ribbon
    End Sub

    Change to ribbon xml

    <customUI 
    	xmlns="http://schemas.microsoft.com/office/2009/07/customui"
    	onLoad="rbx_onLoad">


    Cheers,

    Andy
    www.andypope.info

    Monday, September 24, 2012 3:06 PM
  • Thanks I will test it

    Guy Zommer

    Tuesday, October 2, 2012 6:38 AM
  • thanks, this really helped me
    Wednesday, August 28, 2013 5:16 AM
  • Thanks. Does anyone know the signature for getPressed in c#, please?

    VB: Public Sub Checkbox1_getPressed(control As IRibbonControl, ByRef returnedVal)

    What is the C# equivalent?

    I have tried the following 4 with no joy:

    public bool checkBoxIsOnline_getPressed(Office.IRibbonControl control, ref bool pressed)

    public void checkBoxIsOnline_getPressed(Office.IRibbonControl control, ref bool pressed)

    public bool checkBoxIsOnline_getPressed(Office.IRibbonControl control)

    public void checkBoxIsOnline_getPressed(Office.IRibbonControl control)

    Any ideas? Thanks

    Friday, November 24, 2017 9:13 AM
  • Aah..found the answer...

    https://stackoverflow.com/questions/8039187/how-to-access-backstage-checkbox-value-in-an-office-addin

    c# getPressed CheckBox signature:

    public bool markAsRead_GetPressed(Office.IRibbonControl control)

    Which I had, but I had multiple signatures, which probably confused the system.

    thanks


    APetersen786

    Friday, November 24, 2017 9:20 AM