I am working with Excel 2007 and VSTO.

I have added several buttons to the ribbon which format cells. I want to
disable these buttons on protected worksheets, in the same way that the MS
"Wrap Text" button behaves.

I have a "getEnabled" callback which works fine but I don't know how to
invalidate my buttons when the protection is changed.

Any help would be appreciated.

There is no protection change event but it is easy to trap the user changing
sheet protection through the ribbon UI.

First enhance your RibbonX to repurpose the SheetProtect control:

<?xml version="1.0" encoding="utf-8" ?
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
<commands
<command idMso="SheetProtect" onAction="mySheetProt"/
</commands
...other stuff


Then add the mySheetProt callback to your VB code:

Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
cancelDefault = False ''Allow normal Sheet Protection dialog
End Sub

Sub InvalidateRibbon()
Beep ''Test - Add ribbon invalidate code here
End Sub

--
Jim
"Alice Graham" wrote in message
...
|I am working with Excel 2007 and VSTO.
|
| I have added several buttons to the ribbon which format cells. I want to
| disable these buttons on protected worksheets, in the same way that the MS
| "Wrap Text" button behaves.
|
| I have a "getEnabled" callback which works fine but I don't know how to
| invalidate my buttons when the protection is changed.
|
| Any help would be appreciated.

Jim,

That sounds like it would be a good solution. However, I am working with a
C# addin so I don't know how to pass a method to OnTime rather than a
procedure.

I don't suppose you know how to do that, do you?!

Anyway,
Thanks for your help.

"Jim Rech" wrote:

There is no protection change event but it is easy to trap the user changing
sheet protection through the ribbon UI.

First enhance your RibbonX to repurpose the SheetProtect control:

<?xml version="1.0" encoding="utf-8" ?
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
<commands
<command idMso="SheetProtect" onAction="mySheetProt"/
</commands
...other stuff


Then add the mySheetProt callback to your VB code:

Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
cancelDefault = False ''Allow normal Sheet Protection dialog
End Sub

Sub InvalidateRibbon()
Beep ''Test - Add ribbon invalidate code here
End Sub

--
Jim
"Alice Graham" wrote in message
...
|I am working with Excel 2007 and VSTO.
|
| I have added several buttons to the ribbon which format cells. I want to
| disable these buttons on protected worksheets, in the same way that the MS
| "Wrap Text" button behaves.
|
| I have a "getEnabled" callback which works fine but I don't know how to
| invalidate my buttons when the protection is changed.
|
| Any help would be appreciated.

Also,

the sheetProtect brings up a dialogue for the user to choose what exact
protection they want. Wouldn't this method invalidate the ribbon before the
user had made their decision?

"Jim Rech" wrote:

There is no protection change event but it is easy to trap the user changing
sheet protection through the ribbon UI.

First enhance your RibbonX to repurpose the SheetProtect control:

<?xml version="1.0" encoding="utf-8" ?
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
<commands
<command idMso="SheetProtect" onAction="mySheetProt"/
</commands
...other stuff


Then add the mySheetProt callback to your VB code:

Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
cancelDefault = False ''Allow normal Sheet Protection dialog
End Sub

Sub InvalidateRibbon()
Beep ''Test - Add ribbon invalidate code here
End Sub

--
Jim
"Alice Graham" wrote in message
...
|I am working with Excel 2007 and VSTO.
|
| I have added several buttons to the ribbon which format cells. I want to
| disable these buttons on protected worksheets, in the same way that the MS
| "Wrap Text" button behaves.
|
| I have a "getEnabled" callback which works fine but I don't know how to
| invalidate my buttons when the protection is changed.
|
| Any help would be appreciated.

Jim,

Thanks, got there in the end.

I repurpose the command then simply invalidate the buttons.
There is no need to use onTime method as it seems the ribbon waits until the
default action is carried out before any getEnabled callbacks are made.

Thanks,
Alice

"Jim Rech" wrote:

There is no protection change event but it is easy to trap the user changing
sheet protection through the ribbon UI.

First enhance your RibbonX to repurpose the SheetProtect control:

<?xml version="1.0" encoding="utf-8" ?
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
<commands
<command idMso="SheetProtect" onAction="mySheetProt"/
</commands
...other stuff


Then add the mySheetProt callback to your VB code:

Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
cancelDefault = False ''Allow normal Sheet Protection dialog
End Sub

Sub InvalidateRibbon()
Beep ''Test - Add ribbon invalidate code here
End Sub

--
Jim

Sorry, forgot to check back.<g But I'm glad you got it to work.

--
Jim
"Alice Graham" wrote in message
...
| Jim,
|
| Thanks, got there in the end.
|
| I repurpose the command then simply invalidate the buttons.
| There is no need to use onTime method as it seems the ribbon waits until
the
| default action is carried out before any getEnabled callbacks are made.
|
| Thanks,
| Alice
|
| "Jim Rech" wrote:
|
| There is no protection change event but it is easy to trap the user
changing
| sheet protection through the ribbon UI.
|
| First enhance your RibbonX to repurpose the SheetProtect control:
|
| <?xml version="1.0" encoding="utf-8" ?
| <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
| <commands
| <command idMso="SheetProtect" onAction="mySheetProt"/
| </commands
| ...other stuff
|
|
| Then add the mySheetProt callback to your VB code:
|
| Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
| Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
| cancelDefault = False ''Allow normal Sheet Protection dialog
| End Sub
|
| Sub InvalidateRibbon()
| Beep ''Test - Add ribbon invalidate code here
| End Sub
|
| --
| Jim

Unfortunately, I was too quick to decide I had it working.

Yes, what I've got works fine if the user clicks on the protection button,
or uses the new keytips. However, it does not work when the user changes
protection by running a macro, or by using the old 2003 key combinations.

Any suggestions for how to handle that or am I being too optimistic about
what I can achieve?

"Jim Rech" wrote:

Sorry, forgot to check back.<g But I'm glad you got it to work.

--
Jim
"Alice Graham" wrote in message
...
| Jim,
|
| Thanks, got there in the end.
|
| I repurpose the command then simply invalidate the buttons.
| There is no need to use onTime method as it seems the ribbon waits until
the
| default action is carried out before any getEnabled callbacks are made.
|
| Thanks,
| Alice
|
| "Jim Rech" wrote:
|
| There is no protection change event but it is easy to trap the user
changing
| sheet protection through the ribbon UI.
|
| First enhance your RibbonX to repurpose the SheetProtect control:
|
| <?xml version="1.0" encoding="utf-8" ?
| <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
| <commands
| <command idMso="SheetProtect" onAction="mySheetProt"/
| </commands
| ...other stuff
|
|
| Then add the mySheetProt callback to your VB code:
|
| Sub mySheetProt(control As IRibbonControl, ByRef cancelDefault)
| Application.OnTime DateAdd("s", 1, Now), "InvalidateRibbon"
| cancelDefault = False ''Allow normal Sheet Protection dialog
| End Sub
|
| Sub InvalidateRibbon()
| Beep ''Test - Add ribbon invalidate code here
| End Sub
|
| --
| Jim