+ Reply to Thread
Results 1 to 6 of 6

VBA to hide "Insert Worksheet" tab

  1. #1
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    VBA to hide "Insert Worksheet" tab

    Question:
    Is there a way to hide the "Insert Worksheet" tab in '07? I would like to hide the tab so that users cannot add another worksheet to the workbook. I have tried the standard right click, but Excel will not allow you do that. I also dont want to lose visibility to the other worksheet tabs already created. Thank you in advance.
    Last edited by dthhal; 08-13-2009 at 11:29 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: VBA to hide "Insert Worksheet" tab

    There are other ways to add sheets, better to prevent by using this code
    Please Login or Register  to view this content.
    Copy the code
    Select the workbook in which you want to store the code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
    Right-click on the ThisWorkbook object, and choose View Code
    Where the cursor is flashing, choose Edit | Paste
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

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

    Re: VBA to hide "Insert Worksheet" tab

    If you protect the workbook structure the Insert Worksheet button will not work.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    07-31-2009
    Location
    Gilbert, Az
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: VBA to hide "Insert Worksheet" tab

    Roy,
    This works perfect. This is exactly what I was looking for.

    Andy, Thank you for your help as well.

  5. #5
    Registered User
    Join Date
    04-16-2012
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    Microsoft Office 2007
    Posts
    1

    Re: VBA to hide "Insert Worksheet" tab

    If anyone has any idea, I would still like to know how to hide the tab without hiding all of the others.

  6. #6
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: VBA to hide "Insert Worksheet" tab

    Quote Originally Posted by Randall00 View Post
    If anyone has any idea, I would still like to know how to hide the tab without hiding all of the others.
    Going through the entire process is a bit beyond the scope of this thread. In short, you set the visible property to false (for a group within a tab in the example below) in the XML like this:

    Please Login or Register  to view this content.
    If you are willing to go 'all in' on learning about the ribbon and subsequent manipulation, I would suggest RDB's library of how-to's as a starting point.

    http://www.rondebruin.nl/xmlribbongroups.htm
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

+ 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