2007 Ribbon control disabled by Excel when sheet is protected -Override with getEnabled?

[Walt]

Hi,

Some controls are disabled when a worksheet is protected (ex.
Spelling). If I want to allow the user to do a spelling check via a
repurposed spelling control (Unprotect/Spell ck./Protect), I find I
can't enable the control in my attempts so far. There seems to be no
way to override the Excel disabling of the Ribbon control.

I've tested with the code below in a workbook with one of the three
worksheets protected. I've also tested with
Workbook_SheetSelectionChange vs. Workbook_SheetActivate thinking it
might be a matter of sequencing, but to no avail.

**********RIBBONX CODE*******************************************
<customUI onLoad="RibbonObj" xmlns="http://schemas.microsoft.com/
office/2006/01/customui">
<commands>
<command idMso="Spelling" onAction="TestRepurpose"
getEnabled="GetEnabled" />
</commands>
</customUI>

**********THISWORKBOOK CODE*******************************************
Private Sub Workbook_Open()
TF = True
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TF Then 'TOGGLE TF
TF = False
Else
TF = True
End If
Call RibbonReload
End Sub

**********STANDARD MODULE
CODE*******************************************
Public TF As Boolean
Dim Rib As IRibbonUI

Sub RibbonObj(ribbon As IRibbonUI)
Set Rib = ribbon
End Sub

Sub RibbonReload()
If Not Rib Is Nothing Then
Rib.Invalidate
Else
MsgBox "Rib is lost"
End If
End Sub

Sub TestRepurpose(ByRef Control As IRibbonControl, ByRef Pressed As
Variant)
MsgBox "Repurposed"
End Sub

Sub GetEnabled(ByRef Control As IRibbonControl, ByRef ReturnValue As
Variant)
ReturnValue = TF
End Sub

This is the same issue as in :
http://groups.google.com/group/micr...cc11e?hl=en&lnk=gst&q=enable#8487ca2b6b6cc11e
but with the Ribbon vs. Command Bar control.

Has anyone found a way to do this?

Best Regards,
Walt
WIN XP sp2, Excel 2007

 

[Ron de Bruin]

Hi Walt

Not tested you situation yet but why do you not add a new button to do your stuff.
I see if i have time this evening to test it.

 

[Walt]

Hi Ron,

I was thinking that, ideally, my changes should be minimal and that
this was the simplest and least invasive way. If this can't be done,
then I was thinking that I'd create the new button next to the
existing and set the visible property of the built-in button to
false. What if the user had the Spelling control loaded to the Quick
Access Toolbar? My understanding is that there is no way to manage
that.

Best Regards,
Walt

 

[Ron de Bruin]

Not looked very good but I think I go for this (Easy I think)
And in Macro1 add your unprotect/spelling/protect

'Callback for customButton1 onAction
Sub Macro1(control As IRibbonControl)
ActiveSheet.Unprotect
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect
End Sub


And this is the xml to disable the normal spelling and add a new button


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

<commands>

<!-- Example to disable Spelling-->
<command idMso="Spelling" enabled = "false" />

</commands>

<ribbon>
<tabs>

<tab idMso="TabReview" >
<group id="customGroup1" label="My Spelling" insertBeforeMso="GroupProofing" >
<button id="customButton1" label="Spelling" size="large" onAction="Macro1" imageMso="Spelling" />
</group>
</tab>

</tabs>
</ribbon>
</customUI>




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi Ron,

I was thinking that, ideally, my changes should be minimal and that
this was the simplest and least invasive way. If this can't be done,
then I was thinking that I'd create the new button next to the
existing and set the visible property of the built-in button to
false. What if the user had the Spelling control loaded to the Quick
Access Toolbar? My understanding is that there is no way to manage
that.

Best Regards,
Walt

 

[Walt]

Hi Ron,

So, I think you must have found the same result. Excel's disabling
of the Spelling control on the Ribbon and Quick Access Toolbar when a
worksheet is protected cannot be managed.

I was able to load your posted code and it works OK. But when I
tried to make it look cleaner and substitute visible for enabled in
<command idMso="Spelling" enabled = "false" />, I saw the error
message "The 'visible' attribute is not declared". Also, as I
suspected, the Quick Access Toolbar shows the Spelling button as
visible but not enabled. Managing the Built-In control would be a
nicer solution if possible.

I appreciate your thoughts on this Ron.

Best Regards,
Walt

 

[Ron de Bruin]

Hi Walt

I see if I have time tomorrow to test more.
Maybe Jim Rech have a idea, I am sure he will read this.

Bedtime for me

 

[Jim Rech]

I think you guys have it right. MS does not want you changing the number of
controls that appear in their built-in groups. And no way to enable a
button Excel has disabled. So you've got to have duplicate spellcheck
buttons, Walt.

--
Jim
| Hi Ron,
|
| So, I think you must have found the same result. Excel's disabling
| of the Spelling control on the Ribbon and Quick Access Toolbar when a
| worksheet is protected cannot be managed.
|
| I was able to load your posted code and it works OK. But when I
| tried to make it look cleaner and substitute visible for enabled in
| <command idMso="Spelling" enabled = "false" />, I saw the error
| message "The 'visible' attribute is not declared". Also, as I
| suspected, the Quick Access Toolbar shows the Spelling button as
| visible but not enabled. Managing the Built-In control would be a
| nicer solution if possible.
|
| I appreciate your thoughts on this Ron.
|
| Best Regards,
| Walt
|
|
| > Not looked very good but I think I go for this (Easy I think)
| > And in Macro1 add your unprotect/spelling/protect
| >
| > 'Callback for customButton1 onAction
| > Sub Macro1(control As IRibbonControl)
| > ActiveSheet.Unprotect
| > Cells.CheckSpelling SpellLang:=1033
| > ActiveSheet.Protect
| > End Sub
| >
| > And this is the xml to disable the normal spelling and add a new button
| >
| > <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
| >
| > <commands>
| >
| > <!-- Example to disable Spelling-->
| > <command idMso="Spelling" enabled = "false" />
| >
| > </commands>
| >
| > <ribbon>
| > <tabs>
| >
| > <tab idMso="TabReview" >
| > <group id="customGroup1" label="My Spelling"
insertBeforeMso="GroupProofing" >
| > <button id="customButton1" label="Spelling" size="large"
onAction="Macro1" imageMso="Spelling" />
| > </group>
| > </tab>
| >
| > </tabs>
| > </ribbon>
| > </customUI>
| >
| > --
| >
| > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
| >

 

[Walt]

Hi Ron & Jim,

I may not like the answer, but I understand and I thank you both for
the consideration.

An instance where I've enabled and intercepted the spell checker in
the past has been:

Sub UnlockedSpellCK() 'PROTECTED SHEET UNLOCKED CELLS SPELL CHECKER
Dim TstRng As Range, CL As Range
On Error GoTo UnlockedSpellCKERROR
With ActiveSheet
Set TstRng = .Range("AB1") 'ANY KNOWN EMPTY CELL JUST TO
GET STARTED
For Each CL In .UsedRange.Cells
If CL.Locked = False Then Set TstRng =
Application.Union(TstRng, CL)
Next
.Unprotect
TstRng.CheckSpelling
.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True
End With
Set TstRng = Nothing: Set CL = Nothing
Exit Sub
UnlockedSpellCKERROR:
Set TstRng = Nothing: Set CL = Nothing
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True
MsgBox "Error in 'UnlockedSpellCK' routine."
End Sub

In it, I only spell check the UnLocked cells that the user has access
to change. An even nicer solution would be if Excel, by default,
didn't completely disable the spell checker when a protected sheet is
active, but would just check any UnLocked cells.

Best Regards,
Walt

 

[Ron de Bruin]

Hi Walt

You can hide the group

<tab idMso="TabReview">
<group idMso="GroupProofing" visible="false"/>
</tab>

And make a new one with the same buttons and your own spelling button
Nicer then two spelling buttons

But if the use have it in the QAT you still have the problem that the user can't use that option


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi Ron & Jim,

I may not like the answer, but I understand and I thank you both for
the consideration.

An instance where I've enabled and intercepted the spell checker in
the past has been:

Sub UnlockedSpellCK() 'PROTECTED SHEET UNLOCKED CELLS SPELL CHECKER
Dim TstRng As Range, CL As Range
On Error GoTo UnlockedSpellCKERROR
With ActiveSheet
Set TstRng = .Range("AB1") 'ANY KNOWN EMPTY CELL JUST TO
GET STARTED
For Each CL In .UsedRange.Cells
If CL.Locked = False Then Set TstRng =
Application.Union(TstRng, CL)
Next
.Unprotect
TstRng.CheckSpelling
.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True
End With
Set TstRng = Nothing: Set CL = Nothing
Exit Sub
UnlockedSpellCKERROR:
Set TstRng = Nothing: Set CL = Nothing
ActiveSheet.Protect DrawingObjects:=False, Contents:=True,
Scenarios:=True
MsgBox "Error in 'UnlockedSpellCK' routine."
End Sub

In it, I only spell check the UnLocked cells that the user has access
to change. An even nicer solution would be if Excel, by default,
didn't completely disable the spell checker when a protected sheet is
active, but would just check any UnLocked cells.

Best Regards,
Walt

 

[Walt]

Hi Ron,

Yes, hiding the GroupProofing and reconstructing a custom version does
work nicely. And yes, the QAT issue remains. The user can add the
custom spell checker to the QAT and it will stay functional while the
built-in dims. Again we have the double buttons, but this at least,
isn't overly distracting.

Thanks again for the help Ron.

Best Regards,
Walt

********************************************************************************