+ Reply to Thread
Results 1 to 26 of 26

How can I change menu item selection (display custom ribbon) when changing sheets?

  1. #1
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    How can I change menu item selection (display custom ribbon) when changing sheets?

    I use a custom ribbon in my workbook and I am familiar with creating custom menu items and ribbons. What I want is for a specific menu item to be selected on sheet selection. Is there a way to select a specific menu item (show specific ribbon) when a sheet is selected. In essence, I would like to display a different custom ribbon for each sheet in my workbook.
    Last edited by rkjudy; 05-26-2011 at 12:13 AM.

  2. #2
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    I suggest using the Workbook_SheetActivate event, at which you can change the menu items according to the selected sheet:
    Please Login or Register  to view this content.
    I hope that helps.

    Regards,
    Mohammad

  3. #3
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Great! Since I'm new, can you include the code to switch to the menu item "QuickQuote"?

  4. #4
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    if you post your workbook i will show you what to do exactly

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    If you are using 2010, then you can activate a specific tab in code; if not, you will need to use Sendkeys.
    Remember what the dormouse said
    Feed your head

  6. #6
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Here is an example workbook that you can include the code on. Thanks.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Dear rkjudy,
    sorry for late reply..
    check out the attachment
    regards,
    mohammad
    Attached Files Attached Files

  8. #8
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    If I want to change the menu selection when I change a sheet, where would I put this code. I'm trying to make it so that when I execute Sheeets("sheetname").Select command via a macro, the menu selection changes to my custom menu and ribbon.

  9. #9
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    If you look at the code in the workbook module you will find a select statement that will check for the sheet name and then activates the corresponding ribbon.. this is inserted in the sheet activate command.. so when you activate a certain sheet even in code this will be executed.
    If you have disabled the events, you can copy the code after the Sheets("sheetname").Select line so it would be something like this:
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Well, I guess I'm really dense, but the code included in the attachment returns an error with every combination I've tried. The initial downloaded Example file worked perfect until I tried to create a macro that had a Sheets.().Select command. Take a look at what I've done and see if you can tell, better yet, show me where I've gone wrong.
    Attached Files Attached Files

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    As I mentioned earlier, pre 2010 activatetab does not exist, and you'd have to use Sendkeys.

  12. #12
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    I'm using MS Office Excel 2010.

  13. #13
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    The tab activation works fine for me in your file. None of the buttons work because you haven't included the callbacks in the file.

  14. #14
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    rkjudy,
    the code only works on the original file that i posted,, the reason is that in order to get the ribbon object, you have to modify on a line the customui xml file, like this:
    Please Login or Register  to view this content.
    after that you can use MyAddInInitialize to set the ribbon object.
    if that doesn't make sense for you please tell me and ill explain more

  15. #15
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    by the way the file you have attached works just fine and doesn't return any errors,, however, if you modify on the code you have to save and then reopen the file in order for the ribbon to re initialize

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Or store a reference to its pointer.

  17. #17
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    How can this be done romperstomper??

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    See Ron's article here on using ObjPtr to get the pointer to the RibbonUI object and then use CopyMemory to reinstate it if required.

  19. #19
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    thank u that was very useful

  20. #20
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Glad to help. Hopefully by the next version, that won't be necessary!

  21. #21
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Ok, I've used your code and it works perfectly in Excel 2010. Thank you so much. However, I have users that run Excel 2007. When I run it in this version, I get all kinds of errors. Is there something unique about 2007 that I need to be aware of?

  22. #22
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    See my earlier comment and the link to Tony's article in your other post with Andy.

  23. #23
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    I've read Ron's article, but I don't think that is my problem. I don't lose the ribbon while using the sheet. I immediately get errors when the workbook opens in 2007, but works perfect in 2010. I guess I'm just not experiences enough to figure this out. Any other easily understood suggestions?

  24. #24
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    No, I was referring to the link in your other question about activating tabs.

  25. #25
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Mohd, Is Ron's article an explanation of how to make my custom ribbon work in 2007 specifically, because it works perfect in 2010? My custsom ribbon worked fine in 2007when I wasn't trying to change to a different tab and ribbon on a Sheet().Select command, but I'm getting errors now that I've included this code. Also, if you were wondering, I have the proper XML code and schema for 2007 loaded in the workbook as well as the XML code and schema for 2010. I used the CustomUI Editor. I just don't know what I need to do to make this work in 2007.

  26. #26
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How can I change menu item selection (display custom ribbon) when changing sheets

    Specifically, i was referring to the article I mentioned in this post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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