+ Reply to Thread
Results 1 to 18 of 18

Vba code to prevent deleting & renaming worksheets

  1. #1
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Vba code to prevent deleting & renaming worksheets

    Dear All,

    I am trying to prevent the deleting and renaming of worksheets in a workbook. Upon searching, I found the following code, for that purpose, kindly provided by someone by the name ‘gnod’:

    Please Login or Register  to view this content.
    I copied and pasted the above code in the module of each worksheet that I don’t want deleted or renamed. Unfortunately, I can’t get the code to work at all. I believe I must be doing something wrong.

    I should be very grateful, please, if the code could be checked for me, to determine a possible reason it is not working for me.

    Meanwhile, here is the link where I found the code:
    http://www.vbaexpress.com/forum/arch...p/t-10874.html

    I’ll appreciate any help with this, please.

    Buddy8

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41

    Re: Vba code to prevent deleting & renaming worksheets

    Try this short code snippet:

    It will get rid of the contextual menu when you right click on a worksheet tab:

    Please Login or Register  to view this content.
    You may want to re-enable the menu on the before close event as well....to prevent it from being removed from all of the end user's workbooks....

    Please Login or Register  to view this content.
    Last edited by mrmmickle1; 06-12-2016 at 09:09 PM.
    Using Excel 2010, 2013 & 2016 | Windows 7 | 64 Bit

  3. #3
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Hi Mrmmickle1,

    Thanks for your prompt and kind response. Could you, please, advise me regarding where, in the main code, I should copy your suggested additional lines into. Or do I put them in the sheet modules separately as you have put them.

    Sorry about this, my knowledge of vba codes is very basic, and I would appreciate your further help on this, please.

    Thanks for everything.

    Buddy

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41

    Re: Vba code to prevent deleting & renaming worksheets

    You will need to put the below code in the ThisWorkBook Code Module

    1. Alt + F11
    2. In the Project Explorer double click the ThisWorkBook Icon
    3. Paste code in the code module (white space on the right)

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Thanks very much mrmmickle1. The codes work OK. However, when a user double-clicks on the tab name, that name can be changed - which, in a way, defeats the whole purpose.

    Please, is there anything additional that can be included along with your suggested code that will prevent a user changing the name on a tab name upon double-clicking on the tab? I'll appreciate further help on this.

    Thanks.

    Buddy8
    Last edited by Buddy8; 06-16-2016 at 05:24 PM.

  6. #6
    Forum Contributor
    Join Date
    05-06-2016
    Location
    Cluj, Romania
    MS-Off Ver
    2007-2010
    Posts
    220

    Re: Vba code to prevent deleting & renaming worksheets

    HI,
    put the cod in sheet module (right click in TAB sheet & View Code)

    Please Login or Register  to view this content.
    Change in cod sheet nr & sheet name

  7. #7
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Hi TudyBTH,

    Thanks. I tried your codes but they don't solve the problem. A user will still be able to change the tab names of each of the sheets by double-clicking on the tab name.

    Buddy8

  8. #8
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41

    Re: Vba code to prevent deleting & renaming worksheets

    TudyBTH's code worked for me. It will revert the Worksheet Name to the one you have specified when a user selects or deselects the sheet.... However, if you want to cover another scenario you could use one additional Sub Procedure, but I think this would be a little overkill...... Maybe try putting these three in the Worksheet Module of Sheet1 and test:

    Must go in Worksheet Module:
    Please Login or Register  to view this content.
    Last edited by mrmmickle1; 06-17-2016 at 12:56 AM.

  9. #9
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Thanks mrmmickle1 and my apologies to TudyBTH. It is possible that I might have messed up my Microsoft Office Excel 2007 completely, hence I could not get TudyBTH's code to work.

    I tested it on a fresh workbook with no other macros, and did not get it to work. On further investigation, I realised that with any new end-user workbook that I open, the worksheets have the 'delete' and 'rename' options all greyed out upon a right-click.

    In my main workbook which contains other macros, I had previously tried to use some vba codes to prevent 'delete' and 'rename' of sheets. All those sheets were password-protected. The VBA codes worked OK to disable the 'delete' and 'rename' options respectively.

    However, because they did not prevent renaming of worksheets by double-clicking on the sheet name-tabs, I deleted them and sought further solution through the thread that I posted.

    It appears that the initial testing and subsequent deletion of those codes have messed up my Excel workbooks. When I open a NEW Excel workbook on the computer, all the worksheets therein will have their 'delete' and 'rename' options greyed out. I have tried all that I can conjure up given my baby knowledge of vba codes, to get the worksheets in subsequent new Excel workbooks to show the 'delete' and 'rename' options on right-click. It has proved difficult. All worksheets in new workbooks continue to show the 'delete' and 'rename' options greyed out!

    Please, is there any idea of the steps that I should take to fix this so that when new workbooks are opened, the 'delete' and 'rename' options of the worksheets are not greyed out. I also believe that with this fixed, the codes that have kindly been provided by both you and TudyBTH will work for me OK.

    I hope I can get help to fix this mess. Thanks for your help.

    Buddy8
    Last edited by Buddy8; 06-17-2016 at 11:03 PM.

  10. #10
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41

    Re: Vba code to prevent deleting & renaming worksheets

    The original code you posted reversed should reapply your initial settings:

    This code should initially have "greyed" (disabled) out the options you mention.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Hi mrmmickle1,

    Thanks for your prompt responses to my request for help. Very kind of you. I am truly grateful.

    I have tried your suggestion - copying and pasting into the worksheet(s) modules the reversed code which you provided. It still does not re-apply the original Excel settings for any new workbooks.

    I must confess that before I stumbled upon and used the original code posted on my thread, I tried a few other codes and deleted them on seeing that they did not work for me. I suppose it might be through those 'trying-of-codes' that I created the problem. I was thinking that if there would be any problem, it should only affect the main workbook with my macros. Unfortunately, it is affecting any new workbook that is opened either by me or by anybody using the computer.

    I currently have Office 2007 and Office 2003, respectively, installed on the computer. I observe that the problem only affects Excel 2007 Workbooks. If I open Excel 2003 Workbook, the 'Delete' and 'Rename' options on the worksheets are fine. They are not greyed out.

    It is very frustrating to me and others who use Excel 2007 on the computer. I wonder if everything will reapply if I should re-install Office 2007. I was trying to avoid that.

    I wish there was something else I could do to resolve this.

    Thanks for everything.

    Buddy8

  12. #12
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Vba code to prevent deleting & renaming worksheets

    Not sure if I understand your question correctly but try:
    ===> Review ===> Protect Workbook (enter your own password)

    Regards,
    tt3

  13. #13
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    Initially, I tried to put together a small invoice program using Excel. There are 4 worksheets in the workbook, In an effort to ensure that users in my office were not able to delete or rename any of the worksheets, I looked around online, found and tried a few vba codes to enable me to achieve that.

    As I tried the codes, I discovered that although each of them was able to grey out 'Delete' and 'Rename' upon the right-click of each worksheet, they could not prevent the renaming of each worksheet if a user chose to double-click on the name tab of any of the sheets. I deleted the codes and sought for other code(s) that would prevent the deleting of the sheets and also prevent the renaming of the sheets even when the name-tab is double-clicked.

    It is possible that as a result of adding and removing the vba codes that did not serve my purpose, the 'delete' and 'rename' functions of ALL Excel worksheets that anyone opens on the computer are permanently greyed out. Ususlly when you open a new Excel workbook and right-click on any of the worksheets, the 'delete' and 'rename' options will be clearly shown and not greyed out. This is no longer the case with any Excel 2007 workbook that is opened on the computer. Those options appear permanently greyed out. I believe that this might have been caused by the adding and deleting of vba codes which I did on the Invoice program earlier on. It is causing other users of the Excel 2007 on the computer some frustration.

    I therefore need help to reapply the initial settings so that when a new workbook is opened, the 'delete' and 'rename' options will show normally upon a right-click on any of the worksheet tabs.

    Thanks for any help with this.

    Buddy8
    Last edited by Buddy8; 06-19-2016 at 04:17 AM.

  14. #14
    Forum Contributor
    Join Date
    02-24-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Vba code to prevent deleting & renaming worksheets

    Buddy8,
    I can't offer you a suggestion to correct your current problem, but I can offer this to you.

    For me, a much more simpler way to prevent anyone from deleting, adding, or renaming sheets is to simply use the 'Protect Workbook' feature (icon) that is to the right of the 'Protect Sheet' icon on the Review tab. You can also password protect the workbook.

    But as we all know, Excel does not have the highest levels of security, and anyone that is decent at coding can by-pass any Excel/VBA passwords/protection. But it will keep the majority of Excel users from getting into areas you don't want them to.
    If I helped in any way, please click the star

  15. #15
    Forum Contributor
    Join Date
    05-14-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    120

    Re: Vba code to prevent deleting & renaming worksheets

    I tried a few tricks here and there and eventually got the Workbooks reset to the way they would normally be. Not too sure which one of them resolved the problem.

    Thanks to all of you who have helped me. I am grateful.

    Buddy8

  16. #16
    Forum Contributor
    Join Date
    09-10-2008
    Location
    Phoenix, AZ
    MS-Off Ver
    Office 365
    Posts
    985

    Re: Vba code to prevent deleting & renaming worksheets

    Hi Buddy,

    Why not just hide the ribbon... and when finished you can select Alt+F8 and select Show Ribbon and your done

    Please Login or Register  to view this content.
    Best Regards,

    John

  17. #17
    Registered User
    Join Date
    12-03-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    41
    Buddy,

    The settings you were toggling on and off are not workbook specific. They apply to the entire application. This is why changing them affected other workbooks. It's always good to be careful running code especially if you don't understand it's effects. Glad you were able to resolve your issue.
    Last edited by mrmmickle1; 06-19-2016 at 07:02 PM.

  18. #18
    Forum Contributor
    Join Date
    02-24-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Vba code to prevent deleting & renaming worksheets

    Buddy8,
    Thanks for the rep!
    Glad to help, and glad you found your own solution to get your Excel program working properly.

+ 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] renaming copied worksheets deleting .xls]
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-06-2005, 03:05 PM
  2. renaming copied worksheets deleting .xls]
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 07:05 AM
  3. renaming copied worksheets deleting .xls]
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  4. [SOLVED] renaming copied worksheets deleting .xls]
    By dhl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. renaming copied worksheets deleting .xls]
    By dhl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. renaming copied worksheets deleting .xls]
    By dhl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] renaming copied worksheets deleting .xls]
    By dhl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. renaming copied worksheets deleting .xls]
    By dhl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2005, 06:05 PM

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