Consulting

Results 1 to 10 of 10

Thread: VBA code to hide ribbon / menu bar ?

  1. #1
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location

    VBA code to hide ribbon / menu bar ?

    All - I have a couple of goober users that want to try to meddle with a spreadsheet and code that I have created. I would like to create code to run upon open of the spreadsheet that will hide or disable the ribbon and menu bars and only allow them to enter data into the spreadsheet. I do not want them to copy or paste or anything else. Then I would like separate code to unhide or enable the ribbon and menus if I need to use it.

    What I had planned was to have two pieces of code, one to hide and the other two enable. I would have the hide code run upon startup and then the enable code I would assign a CTRL letter to activate when I want to use the menus. I could then assign the code to hide the menus again to another CTRL letter so I could hide the ribbon/menus again.

    Inside the code I may also want to disable the CTRL-C to prevent copying in the entire spreadsheet to also help prevent them from meddling with formulas.

    Any suggestions or code that may help ? I am not sure of the code that would accomplish this.

    Thanks !

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    To hide:
    [vba]Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"[/vba]

    to show, simply change False to True.
    Be as you wish to seem

  3. #3
    Thanks. Is there an equivalent to this in Excel 2003?

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    There is no Ribbon in 2003 so you either have to put the application in Full Screen mode or loop through all Commandbars making them invisible.
    Be as you wish to seem

  5. #5
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    Thanks Aflatoon.....is ExecuteExcel4Macro the name of the macro or should this remain the same in the code and then just create a module with a different name and paste this inside that module ? I appreciate the help :-)

    [VBA]

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

    [/VBA]

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    That's just a line of code that goes in whatever routine you need it in.
    Be as you wish to seem

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    What happens if your other code crashes?
    What happens if Excel crashes? or Windows?

    How does the user get their Excel controls back?

  8. #8
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    thanks again Aflatoon.....works just as I had hoped. Now I need to figure out if it is possible to hide only one or two tabs on the ribbon via VBA. I can then have two options possibly for the goober users :-)

    mikerickson - are you pointing out a problem with this approach ?

  9. #9
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Any dictator application has the problem that if it falls over, it can't get back up on its own.

    Yours is not the only workbook these users use. The power VBA gives a developer comes with the attendant responsibility to insure that the users aren't inadvertently hurt.

  10. #10
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    Quote Originally Posted by bdsii
    Now I need to figure out if it is possible to hide only one or two tabs on the ribbon via VBA.
    No - you need to alter the CustomUI in the workbook's XML. See Ron de Bruin's site for plenty of examples. Andy Pope also has a VBA driven editor.
    Be as you wish to seem

Posting Permissions

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