xl2007 ribbon editbox update

[thevoid]

hi

I'm trying to customize the ribbon, in order to include my macros
already joined in an addin-workbook.

My problem is the following: how can I copy/imitate the behaviour of the
editbox displaying the fontformat found in the built-in Home tab. I mean the
feature that the value in it updates when changing between worksheets.

I'm familiar with the events and customUI basics, I just haven't found a solution.

Thank you in anticipation
istvan

[Andy Pope]

You will need to use the Callbacks to update the ribbon controls. Also you will need to monitor workbook level events to detect worksheet change.

Are you trying to mimic the font control or was that just an example?

[thevoid]

My macro's purpose is to have a control in the ribbon to ease freezing panes, 2 editboxes, one for the column, one for the row, and by changing between sheets, these editboxes should represent the freezed row/column.

I thought that would be the way to make things work as you wrote.

But I couldn't find the right event.

My idea was catch the change of worksheet with event's callback, then trigger the InvalidateControl method of the ribbon.
And this way the getText method of the runs and so the editbox is updated.

The event I've found for catching the change of a sheet was 'sheet_activate()' but as far as I know it should be defined in the eventually activated sheet's code.

My question is which event should be used, an event that the callbakc procedure could be situated somewhere in the addin-workbook's code.

thanks in advance
istvan

[thevoid]

Hi

I just got the event: workbook_sheetactivate
It works for sheets in other open workbooks as well, if this is added to an addin's thisworkbook's code.

istvan

[thevoid]

Hi

I've just made it

in the addin's module:
Public myRibbon As IRibbonUI

Sub Ribbon_customization_OnLoad(ByVal ribbon As Office.IRibbonUI)
Set myRibbon = ribbon
End Sub

Sub set_freezerow(control As IRibbonControl, Text As String)
If IsNumeric(Text) Then
with activewindow
.splitrow = CInt(Text)
.FreezePanes = True
end with
End If
End Sub

sub get_freezerow(control As IRibbonControl, ByRef Text)
If Not ActiveWindow Is Nothing Then
Text = ActiveWindow.SplitRow
Else
Text = -1
End If
End Sub

in the addin's workbook object:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
myRibbon.InvalidateControl "ebox_set_row"
End Sub

in the customUI.xml:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_customization_onLoad">
<tabs>
...

<editBox id="ebox_set_row"
label="Freeze rows"
maxLength="2"
onChange="set_freezerow"
getText="get_freezerow"
sizeString="2"/>
...
</tabs>


I have one final problem. That is the myribbon variable could be easily cleared (by an 'end' statement, error in a macro, stc.), is there a workaround so that the ribbon (and the controls) could be accessed without this intermediate variable?

yours
istvan

[thevoid]

Hi

I've just made it

in the addin's module:

Public myRibbon As IRibbonUI

Sub Ribbon_customization_OnLoad(ByVal ribbon As Office.IRibbonUI)
     Set myRibbon = ribbon
End Sub

Sub set_freezerow(control As IRibbonControl, Text As String)
     If IsNumeric(Text) Then
     with activewindow
          .splitrow = CInt(Text)
          .FreezePanes = True
     end with
     End If
End Sub

sub get_freezerow(control As IRibbonControl, ByRef Text)
     If Not ActiveWindow Is Nothing Then
        Text = ActiveWindow.SplitRow
     Else
          Text = -1
     End If
End Sub

in the addin's workbook object:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
     myRibbon.InvalidateControl "ebox_set_row"
End Sub

in the customUI.xml:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="Ribbon_customization_onLoad">
<tabs>
...

<editBox id="ebox_set_row"
     label="Freeze rows"
     maxLength="2"
     onChange="set_freezerow"
     getText="get_freezerow"
     sizeString="2"/>
...
</tabs>

I have one final problem. That is the myribbon variable could be easily cleared (by an 'end' statement, error in a macro, stc.), is there a workaround so that the ribbon (and the controls) could be accessed without this intermediate variable?

yours
istvan

[Andy Pope]

Avoid the use of END statement.

Here is an article on handling the potential lose of the ribbon object
http://blogs.office.com/b/microsoft-...on-object.aspx

You can also store values to a temporary sheet. But if lose of variable scope is a regular problem I would investigate the cause rather than code around it.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[thevoid]

Hi

thanks for the solution, nice workaround.

It will do.

istvan