locked
How to access Ribbon controls from other procedures? RRS feed

  • Question

  • Hello,

    I am trying to make an editBox control for Excel which would show the .NumberFormat property of the selected cell. For this, I was going to process SheetSelectionChange event. But from this procedure, I don't know how to access text value of my editBox! I was also trying to save this value as global variable but failed as well (see below). I will be very grateful for any advices!

    Thanks in advance.

     

     

    So, my .xlsm file includes:

    1) customUI\customUI.xml with the following:

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
      <ribbon>
        <tabs>
          <tab idMso="TabHome" >
            <group id="customGroup1" label="Group 1" insertAfterMso="GroupEditingExcel" >
              <editBox id="EditBox1" getText="myGetText" label="My EditBox"/>
            </group>
          </tab>
        </tabs>
      </ribbon>
    </customUI>
    

    and VBA project:

    2) ThisWorkbook:

    Option Explicit
    Dim wbTxt
    
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     If Not IsNull(wbTxt) Then
      wbTxt = "Succeed!"
     End If
    End Sub
    
    Public Sub defineVar(ByRef inpTxt)
     wbTxt = inpTxt
    End Sub
    


    3) Module1:

    Option Explicit
    
    Sub myGetText(control As IRibbonControl, ByRef Text)
     ThisWorkbook.defineVar Text
    End Sub
    


    Thursday, December 8, 2011 4:15 PM

Answers

  • Try this,

     

    Standard code module.

    Option Explicit
    
    Public g_rbxUI As IRibbonUI
    Public g_strNumberformat As String
    Public Sub rbx_onLoad(ribbon As IRibbonUI)
    '
    ' Code for onLoad callback. Ribbon control customUI
    '
        Set g_rbxUI = ribbon
    
    End Sub
    Sub myGetText(control As IRibbonControl, ByRef Text)
        Text = g_strNumberformat
    End Sub
    
    


    Thisworkbook class

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
        If Not g_rbxUI Is Nothing Then
            g_strNumberformat = Target.Cells(1, 1).NumberFormat
            g_rbxUI.InvalidateControl "EditBox1"
        End If
        
    End Sub
    
    


    ribbonx

    <customUI 
    	xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    	onLoad="rbx_onLoad">
    	<ribbon >
    		<tabs >
    			<tab  idMso="TabHome" >
    				<group 
    					id="customGroup1"
    					label="Group 1">
    					<editBox 
    						id="EditBox1"
    						label="My EditBox"
    						getText="myGetText"/>
    				</group >
    			</tab >
    		</tabs >
    	</ribbon >
    </customUI >
    
    


     


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by comp3v Sunday, December 11, 2011 7:10 PM
    Thursday, December 8, 2011 4:59 PM

All replies

  • Try this,

     

    Standard code module.

    Option Explicit
    
    Public g_rbxUI As IRibbonUI
    Public g_strNumberformat As String
    Public Sub rbx_onLoad(ribbon As IRibbonUI)
    '
    ' Code for onLoad callback. Ribbon control customUI
    '
        Set g_rbxUI = ribbon
    
    End Sub
    Sub myGetText(control As IRibbonControl, ByRef Text)
        Text = g_strNumberformat
    End Sub
    
    


    Thisworkbook class

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
        If Not g_rbxUI Is Nothing Then
            g_strNumberformat = Target.Cells(1, 1).NumberFormat
            g_rbxUI.InvalidateControl "EditBox1"
        End If
        
    End Sub
    
    


    ribbonx

    <customUI 
    	xmlns="http://schemas.microsoft.com/office/2006/01/customui"
    	onLoad="rbx_onLoad">
    	<ribbon >
    		<tabs >
    			<tab  idMso="TabHome" >
    				<group 
    					id="customGroup1"
    					label="Group 1">
    					<editBox 
    						id="EditBox1"
    						label="My EditBox"
    						getText="myGetText"/>
    				</group >
    			</tab >
    		</tabs >
    	</ribbon >
    </customUI >
    
    


     


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by comp3v Sunday, December 11, 2011 7:10 PM
    Thursday, December 8, 2011 4:59 PM
  • Thank you very much Andy, it works!
    Sunday, December 11, 2011 7:10 PM