Customised Ribbon not Re-evaluating as excpected

[swaatacba]

This cusxtomised ribbon has been built to re-valuate itself on changes of workbook and worksheet. However, the expected revaluation - see IsBtnEnabled code - no longer operates as worksheet change events take place.

The XML Code is as follows

Please Login or Register  to view this content.

There is a standard module called "RibbonX" which includes the following code.

Please Login or Register  to view this content.

A separate class module handles the Excel events "MyEventsTrap"

Please Login or Register  to view this content.

The application events initiates the ReSet_MyRibbon call, but the review of the status of the ribbon buttons - IsButEnabled - is not activated when moving from one worksheet to the next within a workbook.

I'd grateful for advice or a solution.

[Greg M]

Hi there,

You've omitted far too much of your code for us to be able to make meaningful overall suggestions, but the attached workbook enables/disables the "Create New" button whenever the active sheet is/is not the "Sheet1" worksheet.

It is probably not necessary to trap the "WorkbookActivate" event because the "ACBA-Mapping" tab is displayed only when the workbook which contains the Ribbon XML code is the active workbook.


ThisWorkbook VBA CodeModule:

Please Login or Register  to view this content.

Standard VBA CodeModule:

Please Login or Register  to view this content.

MyEventsTrap VBA Class Module:

Please Login or Register  to view this content.

Hope this helps.

Regards,

Greg M

[swaatacba]

Greg

Thank you for your reply.

Clearly, I have inadvertently misled you. You assumed that the code is in a workbook, whereas it is extracted from a stand alone add-in. I apologise.

I built this 3 years ago, when it seemed to work fine. Some amendments introduced in the past couple of weeks may caused a problem.

Even more curiously, after saving and closing the add-in, it appeared to reset itself on reopening. It nows seems to work as designed.

Whether this has resolved the problem is debatable. For this timebeing I will leave this thread open.


Stephen


Stephen

[Greg M]

Hi again,

Thanks for that feedback.

Yes, it did occur to me that the code might have been in an AddIn rather than in a workbook, but I didn't feel like complicating the issue if it wasn't appropriate

I'll be interested in any further feedback you/others post here.

Regards,

Greg M

[swaatacba]

I have done some research on this issue. Ken Puls' et al RibbonX text book states:

"After you have globally set the object, it will be available throughout the project. Note, however, that the Ribbon object is very "sensitive" to change. This means that if you step into the code at any time to make changes, the object instance will be lost and any operation requiring the Ribbon object will fail. Therefore, it isn't just as simple as test, tweak, test — you need to save, close, and reopen the project any time you make a change."

This is from Chapter 5 "Callbacks: the Key to adding to your Custom UI.

This seems to explain the problem.

[romperstomper]

See Ron's page here: https://www.rondebruin.nl/win/s2/win015.htm

[swaatacba]

See Ron's page here:

Thank you Rory. That is very interesting and certainly works in my limited test.

I'll need to consider where to store the Ribbon recovery pointer within my Add-In. I suspect an appropriate Name is the answer.

Stephen

[swaatacba]

See Ron's page here:

Your reference to Ron de Bruin's page works perfectly. But I made it more complicated by insisting on using 'Names' rather than a reference to a cell. I should have remembered that when calling a named value in VBA the value referred to has a an equals sign "=" in front of it; because it is expecting a reference to a cell. So when referring to the value of a name the code must exclude the "=". This was solved by the, rather clumsy, code below.

Please Login or Register  to view this content.

[romperstomper]

Not sure why you'd use Mid with 1, 1 at the end rather than Left, but you could also use something like:

Please Login or Register  to view this content.

which will evaluate the name and returns its value directly.

[swaatacba]

Thank you Rory. I'm always up for something quicker and new, to me. Whether I'll retain it, is a different issue.

[swaatacba]

@rorya and @Greg M

I have left this thread open while seeking to resolved other issues, see Custom-ribbon-not-responding-to-getenabled-code-in-excel-2019l
.

It seems that Rory's response to those issues has had a beneficial knock on effect here. I'm setting this thread to solved.