Preserve value in xml ribbon edit box when workbook is saved

[tiredofit]

This is my xml code to add a custom ribbon and an edit box:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> 
  <ribbon startFromScratch="true">  
    <tabs> 
      <tab id="CustomTab" label="My Tab"> 
        <group id="Group1" label="MyGroup"> 
          <editBox id="MyEditBox" 
                   label="enter text" 
                   onChange="EditBox_OnChange"/> 
        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>

What I want is if I put a value, say 10 into the edit box, then save and close the workbook, the next time I open the workbook, I want the value of 10 to remain in the edit box.

Is that possible?

 

[RoryA]

Only if you save it somewhere and then read it back in using the onLoad callback.

 

[tiredofit]

Only if you save it somewhere and then read it back in using the onLoad callback.

You mean if the user puts in a value into the edit box, that value needs to be saved somewhere in the workbook and then upon opening the workbook, the Workbooks_Open event gets fired and assigns that value into the edit box?

If so, can you show me some code to do that?

 

[RoryA]

No, not quite. It needs to be saved somewhere (doesn't have to be in the workbook necessarily, though that probably makes most sense) but you need the ribbon's onLoad procedure not the Workbook open event. You don't appear to have that set up in your example, so you'll need to add it. You will also need a getText callback for the editbox so that you can set the value. The onload will need to invalidate the control so that it calls the getText to assign the value to the control. I'm afraid I don't have time currently to put together an example of all of that, but that should be enough terms to search for. (e.g. here)

 

[tiredofit]

No, not quite. It needs to be saved somewhere (doesn't have to be in the workbook necessarily, though that probably makes most sense) but you need the ribbon's onLoad procedure not the Workbook open event. You don't appear to have that set up in your example, so you'll need to add it. You will also need a getText callback for the editbox so that you can set the value. The onload will need to invalidate the control so that it calls the getText to assign the value to the control. I'm afraid I don't have time currently to put together an example of all of that, but that should be enough terms to search for.

Thanks, I'll give it a try.

I do know what you mean by onLoad, I think!

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

and in VBA:

Dim MyRibbon As IRibbonUI

Public Sub Initialise(ByRef ribbon As IRibbonUI)

    Set MyRibbon = ribbon

End Sub

 

[RoryA]

Yes - I just added a link to my previous post that may be useful.

 

[tiredofit]

Yes - I just added a link to my previous post that may be useful.

Thanks again.

 

[tiredofit]

EDIT SORTED:

vba code:

Option Explicit

    Dim MyRibbon As IRibbonUI
    
    Dim abc
    
Public Sub Initialise(ByRef ribbon As IRibbonUI)

    abc = Sheet1.Cells(1, 1).Value2
    
    Set MyRibbon = ribbon
    
    Call UpdateEditBox
    
End Sub

Sub EditBox_OnChange(control As IRibbonControl, text As String)

    Sheet1.Cells(1, 1).Value2 = text
    
    Call UpdateEditBox

End Sub

Private Sub EditBox_getText(ByRef control As IRibbonControl, _
                          ByRef returnedVal)
    
    returnedVal = abc
    
End Sub

Private Sub UpdateEditBox()
    
    MyRibbon.InvalidateControl "EditBox"

End Sub

xml code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
         onLoad="Initialise"> 
  <ribbon startFromScratch="false">  
    <tabs> 
      <tab id="CustomTab" label="My Tab"> 
        <group id="Group1" label="MyGroup"> 
          <editBox id="EditBox" 
                   label="enter text" 
                   onChange="EditBox_OnChange"
             getText="EditBox_getText"/>

        </group> 
      </tab> 
    </tabs> 
  </ribbon> 
</customUI>