The Amsterdam Excel Summit

Results 1 to 10 of 10

Thread: VBA code to hide ribbon / menu bar ?

  1. #1

    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
    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
    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
    Thanks 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 :-)


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


  6. #6
    That's just a line of code that goes in whatever routine you need it in.
    Be as you wish to seem

  7. #7
    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
    thanks again 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
    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
    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