David Barnes
asked on
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.
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.
Are your event handlers are in a standard module ?
ASKER
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.
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.
ASKER
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.
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.
ASKER
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)
ActiveSheet.PrintPreview
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.
'Callback for customButton9 onAction
Sub iPrintPreview(control As IRibbonControl)
ActiveSheet.PrintPreview
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.
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.
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.
ASKER
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!
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!
ASKER
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I inserted a module containing the same code and everything works.that's what I suggested.
ASKER
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!
Thanks for your help!
Guess it is because ThisWorkbook module is a class, thus not accessible without an instance.
Open Event Code on ThisWorkbook Module works fine with Custom Ribbon Tabs. Look at the custom tab called PersonalMacros in the attached.
TestCustomRibbonTab.xlsm
TestCustomRibbonTab.xlsm
Open Event Code on ThisWorkbook Module works fine with Custom Ribbon Tabs.ASKER
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.
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.