UDFs appear in multiple categories when clicking on f(x)

[alansidman]

I have approximately a dozen UDFs that have been developed over several years. When looking to employ a particular one from the function address window, I click on the f(x) and they appear as multiple categories. See Capture1.jpg. I then have to click on each User Defined category to find the one I want. Each category may hold one or many UDFs. See Capture2.jpg and Capture3.jpg.

They are all stored in mylibrary.xlam as seen in Capture4.jpg.

Am I able to consolidate these into one User Defined category?

[Olly]

You'll be needing Application.MacroOptions

Example:

Please Login or Register  to view this content.

Silly example, but shows you the method.

[alansidman]

Thanks Olly; will give it a try.

One question. Run the sub in Personal.xlsb or MyLibrary.xlam

Alan

[Olly]

The effect persists only per session, so stick the sub containing the MacroOptions in the Workbook_Open event of the addin

[alansidman]

Thanks again. That is probably why I am getting a 1004 error. I have researched that error, but continues to fail. Will follow up on your suggestion.

[Olly]

Just saw your edit - run that method in the workbook which contains the functions.

You could run it in another workbook, but then you'd be entering the world of fully qualifying procedure references, and the associated error handling (if for example the addin was disabled). Simpler to run it in each workbook which contains functions, for those functions - if that makes sense?!

Shout if you don't get it sorted, ping me your addin, I'll have a look

[alansidman]

Here is the code I have attempted to run. Attempted to place as Workbook Open event in the Addin also. All receive the dreaded 1004 error message. Failed.

Please Login or Register  to view this content.

Thanks for looking

[Olly]

Alan, I'm half way through understanding what's happening, but wine and puppy dog are taking over. I'll look at this over the weekend, if that's ok?!

[alansidman]

Olly,
There is no rush on this. It is something that has been festering for over a year and today I decided to take action to try and figure it out. Enjoy your weekend, your puppy and of course your wine.

Alan

[Olly]

Okay, figured it - the macro references need to be fully qualified.

So, in a general module, use:

Please Login or Register  to view this content.

And in the workbook module:

Please Login or Register  to view this content.

Works for my testing here - see if it works for you?

[romperstomper]

May I suggest that you don't give your routines the same name as the module that contains them?

[Olly]

Further update: I transferred to another machine to test, and it failed again. Try changing the workbook module to this:

Please Login or Register  to view this content.

[romperstomper]

You can't run MacroOptions unless there is a workbook open, IIRC, so you could simply add a Workbooks.Add statement before you run the MacroOptions part.

[alansidman]

Thanks Rory and Olly;

Will post later after testing.