Macro Callbacks in Excel Custom UI Code Not Working

I've used Ron de Bruin's Custom UI Editor for a while now, and looking at my xml code you can see I'm not expert. I simply started years ago with his sample file and modified it (very carefully) to my needs.

I've used it in several projects and never had a problem. But this latest one has me stumped. I created an excel workbook with the attached VBA code as I always do, then modified an existing Custom UI project as always. But although as far as I can tell I've done everything exactly as I have in the past, I'm getting an error stating "Cannot run the macro 'ixxxxxxxxx'. The macro may not be available in this workbook or all macros may be disabled"

The trust settings are correct; allow all macros and VBA access. I thought perhaps I had fat-fingered some code somewhere so I used Custom UI Editor to generate the callbacks and they were identical to what I had, but I used them anyway.

I can run all of the subroutines from the macro window with no problem, but I'm at my wits' end trying to figure out what's wrong with my code this time around.

Any help would be greatly appreciated. I'll include my UI xml code and my vba code as well. I'm sure it's probably something simple, but for the life of me I can't find it.Mortgage-Analysis-VBA.vb.txtMortgage-Analysis-UI-Code.txt

As you can see, I haven't even changed the icons in the UI code. I haven't made it that far.
David BarnesIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To try that, I pasted your codes on a Standard Module and the Open and BeforeClose event codes on ThisWorkbook Module. And then I pasted the xml code into the customUI14.xml.

When I tested the buttons on Custom Ribbon Tab, I got no such error except the Variables not defined errors, that's because I always use Option Explicit on top of each module and it prompts you if you use any variable in the code which you have not explicitly declared and I suggest you to do the same.

The some variables like Adodb Connenction variable and Connection String need to be declared as Public Variables because you are accessing them in Open event as well as in standard modules.

Also, you need to add control As IRibbonControl parameter in all your macros as this is essential step to call the macros by clicking the custom buttons added on custom tabs.
Fabrice LambertConsultingCommented:
Are your event handlers are in a standard module ?
David BarnesIT ManagerAuthor Commented:
Thank you Subodh. I do have the variables you mentioned declared as public. I have a separate module for public variables. I should have mentioned that.

I will add Option Explicit for sure. As you can tell, I'm not a VBA expert. My programming experience is with older languages and I'm just getting started with VBA. But I've never had this problem with my Custom UI code before.

I'm going to take a fresh look this morning and try all of your suggestions.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

David BarnesIT ManagerAuthor Commented:
Hi Fabrice,

In this particular case I put everything except a couple of sheet event handlers in ThisWorkbook. The only other modules I have is one to calculate the internal rate of return, which is working, and one for public variables.
David BarnesIT ManagerAuthor Commented:
Subodh, I'm not sure what you mean by including "control As IRibbonControl" in all my macros. I'm probably taking an unnecessary step, but following the samples in UI Editor I've always added a callback with that parameter and either placed the code in that callback, as in my print macros, or had it call the actual macro. For instance:

'Callback for customButton9 onAction
Sub iPrintPreview(control As IRibbonControl)
End Sub

The above code has worked perfectly in the other workbooks I've created and integrated with the UI Editor.

Is there any chance I'm missing a reference? I checked other workbooks that are working correctly and they have the same references.

I've attached a file showing my references. I really appreciate your help.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Passing control As IRibbonControl as parameter with the codes is very essential otherwise you should get an error something like wrong number of arguments or so.
The references are fine.

Another thing is, make sure the macro names in the UI Editor are exactly same as on Modules. You have too many macros so cross check them carefully.
David BarnesIT ManagerAuthor Commented:
I know there are a lot of macros. Once my employer uses this for a while and asks for any changes I will fine-tune the code a bit. Obviously all of those filters could be combined with a parameter. But this way it's easier for me to modify.

I did notice one difference in the other workbooks where I've used UI Editor to create a custom ribbon. In those I placed all of the code in a module instead of ThisWorkbook. I can't for the life of me understand how that would make a difference, but I suppose it's worth a try.

And I will triple check the names in my UI file.

Thanks again!
David BarnesIT ManagerAuthor Commented:
I don't understand this at all. If someone can enlighten me I would greatly appreciate it.

I inserted a module and place all of the code except the two Sheet1(All)-specific and two ThisWorkbook-specific macros into it. I changed nothing else, and suddenly it works.

Why would a module work and not ThisWorkbook?

Regardless, I really appreciate the help. And while I know what to do now, I still don't understand why. But hey, it's working!
David BarnesIT ManagerAuthor Commented:
The UI callbacks were not being found in the ThisWorkbook container. I inserted a module containing the same code and everything works.

Thanks to everyone who helped. I really appreciate it.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertConsultingCommented:
I inserted a module containing the same code and everything works.
that's what I suggested.
David BarnesIT ManagerAuthor Commented:
Sorry Fabrice, I may not have understood your original post. I just happened to notice that I'd placed the code in it's own module on previous worksheets and thought I'd give it a try. I'd still like to know why it works in a module and not in ThisWorkbook.

Thanks for your help!
Fabrice LambertConsultingCommented:
Guess it is because ThisWorkbook module is a class, thus not accessible without an instance.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Open Event Code on ThisWorkbook Module works fine with Custom Ribbon Tabs. Look at the custom tab called PersonalMacros in the attached.
Fabrice LambertConsultingCommented:
Open Event Code on ThisWorkbook Module works fine with Custom Ribbon Tabs.

Open in new window

The workbook event handlers work as expected, but event handler procedures for the ribbon must be in a standard module.
David BarnesIT ManagerAuthor Commented:
My testing confirms what you're saying Fabrice. I simply didn't understand your first post on this question. You had it right, at least for my code.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.