View Full Version : Solved: Re-connecting the Ribbon after error

09-07-2008, 11:53 AM
I've been Googling for a user-friendly way to reconnect the Ribbon after an error, mostly (in my limited experience so far) after trying to InvalidateControl().

So far, the recommendations usually involve reloading the workbook or document.

The best workaround I've come up with is not very elegant, and I don't know how bullet-proof it will be in more circumstances.

I was wondering if anyone could offer suggestions to make it more robust?

Option Explicit
Public gobjRibbon As IRibbonUI, gobjRibbonBackup As IRibbonUI
Public gbDetailed As Boolean, gbBrief As Boolean, gbCSheet As Boolean

Public Sub OnRibbonLoad(ribbon As IRibbonUI)
Set gobjRibbon = ribbon
Set gobjRibbonBackup = gobjRibbon
End Sub

'callback for View buttons
Sub ViewPressed(control As IRibbonControl, pressed As Boolean)
Dim bBeenThere As Boolean

bBeenThere = False

On Error GoTo ReinitRibbon
'Set gobjRibbon = Nothing ' for testing purposes
With control
Select Case .id
Case "togDetailed"
Call PBOM_Detailed
gbDetailed = True
gbBrief = False
gbCSheet = False
Case "togBrief"
Call PBOM_Brief
gbDetailed = False
gbBrief = True
gbCSheet = False
Case "togCSheet"
Call PBOM_Csheet
gbDetailed = False
gbBrief = False
gbCSheet = True
End Select
End With

gobjRibbon.InvalidateControl ("togDetailed")
gobjRibbon.InvalidateControl ("togBrief")
gobjRibbon.InvalidateControl ("togCSheet")
bBeenThere = False
Exit Sub
If Err.Number = 91 And Not bBeenThere And Not gobjRibbonBackup Is Nothing Then
bBeenThere = True
Set gobjRibbon = gobjRibbonBackup
Call MsgBox("Sorry - There was as problem with the Ribbon object, " & vbCrLf & vbCrLf & _
"so I think you'll have to save and re-load this workbook." & vbCrLf & vbCrLf & _
"But the good news is you probably didn't lose any data", vbCritical + vbOKOnly, sTitle)
Exit Sub
End If
End Sub


Bob Phillips
09-09-2008, 06:53 AM
There was a thread in the public NGs on this exact topic recently Paul, and even Ron de Bruin conceded that was the only way that he knew how.

09-09-2008, 08:06 AM

Good stuff on http://www.rondebruin.nl/. I had been to Ron's site before, but there's SO much there, I missed the QAT info that he has.

I could not find anything at his site about "re-connecting" the ribbon.

You wouldn't happen to have the NG thread information would you? Binsearch.info didn't have any hits for the keywords I entered.


Ken Puls
09-09-2008, 08:40 PM
Hi Paul,

Based on all the experimentation that we did when writing the RibbonX book, Robert Martin & I also have come to the conclusion that the only way to reload the ribbon is to reload the workbook. It is hardly ideal, but an unfortunate reality. :(

09-10-2008, 06:34 AM
Hi Ken --

1. I was at my local Barnes & Nobel bookstore looking for "The Book", not in stock so I have to online order it:thumb

2. You were kind enough to make Chap 14 available online for review -- thanks for that:clap:

From the VBA fragment in my first post, Setting a "backup" RibbonUI variable to the Ribbon in the Ribbon OnLoad, and re-Setting the "real" ribbon On Error to the backup seems to work, at least some times, and avoids having to re-load the file. :yes

This is pretty deep stuff for me, and I'm sure that there are pitfalls.:doh:

It would be nice of Microsoft to address this (and a few other) issues, since they feel that RibbonX is the wave of the future.:banghead:

Thanks again


Ken Puls
09-10-2008, 10:05 PM
Hi Paul,

Sadly, it doesn't seem to be stocked in the "Brick and Mortar" stores. From what I hear, 99% of computer books are sold online, so they only tend to stock the very best sellers and authors. Being a first time author, I guess we're too much of a risk. ;) I hope that you find it useful when it arrives. :)

Chapter 14? I made Chapter 7 (http://www.excelguru.ca/node/93) available... I didn't know that 14 was out, actually.

With regards to the backup RibbonUI variable, it's a great approach. The more steps you can take to protect the RibbonUI object the better, but ultimately, I really don't think that you should need to. And ultimately I'd agree on your last point. It would be nice if MS addressed quite a few things. ;)

09-11-2008, 08:53 AM
Chapter 7 is correct (but I guess you knew that :yes )

There's SO many computer books out of varying quality that I really like to actually see it before I buy it. That's why I was glad that there was a chapter available, so that I could see that it more than just a re-hash of on line help.

It's on order now

Thanks again


01-07-2011, 05:43 AM
I know this is an old post, but in case it is useful, it is possible to regain the reference to the Ribbon - see this (http://www.thecodecage.com/forumz/members-excel-vba-programming/208295-how-preserve-regain-id-my-custom-ribbon-ui.html) post (and its linked post in MrE).

02-17-2011, 09:54 AM
I posted what I do (based on info I found in the above post) in another thread here called Ribbon Object. As I am new, it won't allow me to paste a link.

Forum Admins-- perhaps your parser should allow links within your own forum :)

Bob Phillips
02-21-2011, 05:34 PM
I posted what I do (based on info I found in the above post) in another thread here called Ribbon Object. As I am new, it won't allow me to paste a link.

Forum Admins-- perhaps your parser should allow links within your own forum :)

With 33 posts you should have no trouble posting a link.