+ Reply to Thread
Results 1 to 16 of 16

Creating Custom Menu Items

  1. #1
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Creating Custom Menu Items

    Currently I have a Addin file that uses RibbonX in creating a custom Menu Item in the ToolBar. This is a separate menu item added at the end of the main toolbar, and not within the 'Addins' Excel menu. And this seems to work well using various callbacks like such:

    Please Login or Register  to view this content.
    Is it possible to accomplish the same without the use of RibbonX? In my company we are looking into expanding this menu system into a complete 'suite' of items by user 'department'. We think, but haven't tried yet that we can read the user's Windows active directory login info, determine what department(s) he or she belong to and then have Excel load those specific department menu items.

    Can I have an Addin when it loads read the user department and then load X menu items? I would hope also to reuse some code as multiple departments would want some of the same macros to be available.

    Any thoughts if you can create menu items like 'Home', 'Insert', etc without the use of Ribbonx and accomplish this? Or can I have the RibbonX code identify what department(s) the user is a group of and load those menu items?

    I would prefer to not have a separate Addin File for each department as that would then result in duplicating code in multiple files and making any updates more involved as well.
    Last edited by ptmuldoon; 05-25-2017 at 11:17 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Creating Custom Menu Items

    Hi ptmuldoon,

    Have you considered the Quick Access Toolbar? QAT?

    https://www.excelcampus.com/keyboard...s-toolbar-qat/
    http://www.myonlinetraininghub.com/c...e-qat-in-excel
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Quote Originally Posted by MarvinP View Post
    Hi ptmuldoon,

    Have you considered the Quick Access Toolbar? QAT?
    Yeah, pretty sure QAT will not work for this. Attached is a screen shot of the Menu Items that I created with RibbonX about 2 years ago. So its been a while since playing with VBA, etc.

    Think you can create similar Menu items directly via a macro/addin and not use RibbonX? I'm trying to learn the best way to provide various menu items and functions to each department without cluttering up the menu too much.
    Last edited by ptmuldoon; 05-26-2017 at 03:30 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Creating Custom Menu Items

    Hi ptmuldoon,

    PUP 7.2 has a lot of functions included in it. You can buy his source code for a very small amount. Perhaps you can see how Walkenbach does this type of problems. Your "Text Functions" group reminded me of his built-in functions. http://spreadsheetpage.com/index.php/pupv7/functions/

    See it at:

    http://spreadsheetpage.com/index.php/pupv7/vba/
    Last edited by MarvinP; 05-26-2017 at 12:24 AM.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating Custom Menu Items

    Depending on how varied the custom tabs contents are by department you could set up a master tab with all departments content and then use callbacks to control visibility of groups.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Quote Originally Posted by Andy Pope View Post
    Depending on how varied the custom tabs contents are by department you could set up a master tab with all departments content and then use callbacks to control visibility of groups.
    Yes, I think that is where I am possibly looking to with it. Basically creating a Dynamic Menu system that would potentially read the User's department info and create the menu 'on the fly' when Excel launches.

    I'm going to start digging around on creating a Dynamic menu. But do you still need RibbonX to do that? And if so, do then have the RibbonX load the Menu items based on the User department or other?

    @MarvinP - You bet I read Walkenbach's Books. That VBA for Dummies book was the first book I read in getting started. And its a damned good book and his tutorial on the Text Tools was fantastic!!!

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating Custom Menu Items

    It would be a combination of both ribbonx and vba.

    Use the getVisible callback of the group and return true of false if the user should be seeing that particular group.

  8. #8
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Ok, doing some research and digging... I found some good information done by Rondebruin here. He's one smart guy!!

    https://www.rondebruin.nl/win/s2/win019.htm

    Now, in trying to figure it out, I see there is some sort of call back within the RibbonX like this:

    HTML Code: 
    And I see the RDBdynamicMenuContent macro in the module code that creates the menu. But I can't seem to figure out yet how to modify this to start with a Dynamic Menu Tab item.

    My thought was trying to have module code add to the customUI RibbonX starting with tab info, but haven't figured it out. I think if that will work I can then begin to put in conditions what users can see and different toolbars.

    I think/hope I'm on the right track at least!
    Attached Images Attached Images
    Last edited by ptmuldoon; 05-26-2017 at 03:02 PM.

  9. #9
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    @Andy Pope

    I think you are probably correct with the GetVisible approach. I found that info on Rondebruin's site as well and it will work well. But the issue there is that I could end up having some very large, long RibbonX code for listing the Tab/Menu's for 5 or 6 different departments. I would prefer to keep the Menu code separate for each group to make any changes and updates easier.

    I'm going to experiment with that over this weekend, and see what I can learn. If I can find a way to load add/append to the RibbonX for each group as needed, that would be great.
    Last edited by ptmuldoon; 05-26-2017 at 03:35 PM.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating Custom Menu Items

    within excel the only truly dynamic part, where you can load ribbonx via VBA, is the dynamic menu control. Elements like tab, group and controls within a group need to be specified in ribbonx and then some of their properties can be manipulated via callbacks.

    If you don't want all the departments in 1 file you could use a 2 file approach. A master addin is loaded with a shared tab. It can then determine which other addin to include, based on users department.
    For this you would need to investigate the namespace feature of ribbonx xml

    There is a couple of example files on this page
    http://andypope.info/vba/ribboneditor.htm

  11. #11
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Andy. That's a great ribbon editor Addin you created there and thank you for sharing.

    So.. I have another topic going here that is related, but I decided on likely giving the User a general UserForm where they chose which menu's to load.

    And in trying to keep on topic here with RibbonX and the GetVisibility, I'm trying to get 2 menus to load at the same time. So I have this in my RibbonX

    Note: .... Is just to abbreviate the code. It is valid in CustomUI Editor.

    Please Login or Register  to view this content.
    And then in the VBA trying to load the Ribbon, I have something like this. Note, I'm looping Column B for a Value of 1, and Column A includes the "Audit" and "Tax" names.

    I have this code, but it will only load 1 menu, and not multiple when I assign a 1 to each item.

    Please Login or Register  to view this content.
    Is this because the RefreshRibbon closes the first menu when opening the next? Or is it in the RibbonX and issues with InsertAfter?

    In Trying to learn some more, I think the Rib.Invalidate clears the menu before rebuilding it. So is there a way to reload the menu with 2 or more New Menu tabs?

    Please Login or Register  to view this content.
    Last edited by ptmuldoon; 05-27-2017 at 09:54 PM.

  12. #12
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating Custom Menu Items

    Here is a very simple example file. There are 3 tabs, My Tab | Audit | Tax.

    My Tab is always show, but Audit and Tax are dependent upon information in Departments sheet.

    The getVisible callback is used to return whether tab is visible or not.
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Andy

    That's pretty good. I'm still working to understand it, but is it possibly to refresh the Menu's without opening and closing Excel? I tried looking at the code but couldn't figure out if that is possible or something I should try to add in?

  14. #14
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    In working to bring this together now from this other thread in getting a userform set, I have combined the code from @sysss with yours. He was also very helpful in this thread here with the userform.

    https://www.excelforum.com/excel-pro...l-address.html

    So now the goal here is open the userform, select the menu you will want, and then the On-click code of the form will load the menus the user choses.

    I think this is close now as the Menu's will load if you open and close Excel. But I am thinking/hoping of a way to refresh the ribbon without having to relaunch Excel each time

    Updated Sample Here Attached
    Attached Files Attached Files
    Last edited by ptmuldoon; 05-28-2017 at 06:32 PM.

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Creating Custom Menu Items

    Try this.

    The button on My Tab will show a user form to choose which tabs are displayed.
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Creating Custom Menu Items

    Andy.

    That is 100% fantastic!. I hope to look for at the code later today and work to perhaps begin adding in the rest of the macros and RibbonX for each department. This will let me combined 2 Existing Addins together and get rid of alot of duplicate code as well also then have ability to set up additional department menus.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to 'Grey Out' menu items on Custom Menu if Workbook is ReadOnly
    By TC1980 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-30-2013, 08:22 AM
  2. creating new custom menu items
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-03-2010, 10:48 AM
  3. Creating Menu bar items that can not be selected
    By ben_patto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2009, 03:24 AM
  4. Replies: 3
    Last Post: 06-27-2006, 08:50 AM
  5. custom menu items not responding after initial save
    By MAB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2006, 09:50 AM
  6. xla creating new menu items
    By dspencer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2006, 11:01 AM
  7. [SOLVED] Create/Remove Custom Menu Items from Add-in
    By Michael Malinsky in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-27-2005, 02:04 AM
  8. [SOLVED] coping custom menu items
    By bobbyvt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1