Changing workbooks with custom Ribbon causes error 'Cannot run the macro...'

Hi All

I have a workbook with quite an extensive custom ribbon, with many getvisible & getPressed callbacks.

What I am finding is when changing workbooks, sometimes it all works seamlessly, with the custom tabs disappearing as they should. But sometimes I get an error "cannot run the macro 'blahblahblah'. The macro may not be available in this workbook or all macros may be disabled.". This doesn't occur all the time. Anyone have any info on how to avoid this or head me in the right direction?

If I comment out all of the code to refresh certain ribbon controls it seems to fix the issue, so this is the root cause of the problem, however then all of the ribbon controls do not update dynamically when the ribbon is refreshed when other code calls for the ribbon to be refreshed. I believe the refresh is triggered by changing the workbook, but then it finds no macros in the new workbook or something.

i.e. comment out multiple

Please Login or Register  to view this content.

How would I make excel refresh the individual controls when working in the workbook with the ribbon, but not have the refresh trigger when another workbook becomes the active workbook?

Due to the nature of some of the controls being used, I have Worksheet_SelectionChange and Workbook_SheetActivate events that call for the ribbon to be refreshed/invalidated when changing cells and/or sheets in the workbook so toggle buttons and visibility of ribbon controls update correctly.

Thanks!

I call the refresh ribbon macro from some individual macros when the ribbon needs to be updated. Mainly as far as I remember from getVisible, onAction and getPressed callbacks. I suspect this is what is causing the issue, but as far as I am aware its required, otherwise the likes of togglebuttons and visibility do not update until I trigger one of the Worksheet_SelectionChange and Workbook_SheetActivate events? (i.e. simply pressing a button doesn't deactivate the previous toggle button)

My code probably (attempts to) explains this better :-
example code:-

Please Login or Register  to view this content.

this is an example of two types of toggle buttons I am using

The one with font10 also has other similar buttons with 6 & 8 font, so it is dynamically highlighted dependent on the size of font in a cell.

The cell_notes_style one also has other similar toggle buttons with other styles, so it is dynamically highlighted dependent on the style applied to a cell.

If I do not call the refreshribbon macro from these subs, the previously applied style togglebutton for example does not get unhighlighted. I suspect it is this additional call ribbonrefresh causing the issues, but it seems to be required to achieve what I want to occur when in the workbook with the custom ribbon.

When I comment all of them out changing workbooks causes no errors.

Any idea if there is a way to achieve what I am after.

Are your callbacks prefixed with the project name in the customui?

Do you mean the namespace, I have the following (had to remove the leading '<' as have not figured out how to post XML):-

Please Login or Register  to view this content.

I do not have the namespace (HFX in this instance) in front of individual onAction/getPressed callbacks, should it be?

One other thing I have noticed, the errors only happen when I have one of the custom tabs open and I select another workbook that does not have the same ribbon being present. No errors occur when I have one of the built in tabs open and switch workbooks.

It is always the getVisible or getPressed callbacks that cause the errors.

I never would have thought to do that. So I changed the standard 'VBAProject' reference to something else, and added the same name into the customui XML code, but it still gives the same error when switching workbooks. The error is now noting cannot find "myProject.Font8_onPressed", etc. Apart from that it seems to be the same behavior. When in the workbook with the ribbon, all of the actions still work fine with the updated prefix.

Any other ideas?

I changed a couple of them just now (all of the applicable macros on one custom Tab), but I still get the same error unfortunately if I have that Tab selected and change workbooks.

I'll try simplify it down to a couple of macros causing the issue and upload.

Not all of the callback macros are in the same module.

Interestingly, when I isolated just the Font6/8/10 callbacks (i.e. no getVisible ones) to sanitize the file to upload, things started working as they should for these toggle buttons and swapping workbooks with no errors.

Leads me to believe I was doing something wrong with the getVisible callbacks. Looking into this I found I was calling the refresh of the ribbon from these getVisible callbacks (copied from some example on the web), removing these seems to solve the issue (need to review further tomorrow as need to sleep!)

Will upload the resulting file if I still get the same errors, but I think I have solved the problem (fingers crossed). Thanks for all your help!