Disable Rename Sheet in Excel 2007

[AzizKamal]

I am using Excel 2007 and I need to disable Rename Sheet functionality in an Excel Workbook. I cannot protect the workbook because workbook is macro-enabled (xlsm) and when I protect the workbook, I get a Security Warning; Macros have been disabled and under Options I cannot enable the content. So I need to disable rename sheet without using workbook protection method.

I executed the following code mentioned in faq707-4727:

Sub ListXLPopups()
   Dim cbCtl As CommandBarControl
   Dim cbBar As CommandBar
   Dim i As Integer

   On Error Resume Next
   Application.ScreenUpdating = False
   Cells(1, 1).Value = "CommandBar"
   Cells(1, 2).Value = "Control"
   Cells(1, 3).Value = "FaceID"
   Cells(1, 4).Value = "ID"
   i = 2
   For Each cbBar In CommandBars
      Application.StatusBar = "Processing Bar " & cbBar.Name
      If cbBar.Type = msoBarTypePopup Then
         Cells(i, 1).Value = cbBar.Name
         i = i + 1
         For Each cbCtl In cbBar.Controls
            Cells(i, 2).Value = cbCtl.Caption
            cbCtl.CopyFace
            If Err.Number = 0 Then
               ActiveSheet.Paste Cells(i, 3)
               Cells(i, 3).Value = cbCtl.FaceId
            End If
            Cells(i, 4).Value = cbCtl.ID
            Err.Clear
            i = i + 1
         Next cbCtl
      End If
   Next cbBar
   Range("A:B").EntireColumn.AutoFit
   Application.StatusBar = False
End Sub

I copied this code in Module1 and pressed F5. Under CommandBar=Ply, Control=&Rename; id number is 889. I wrote the following in Workbook Open and BeforeClose:

Private Sub Workbook_Open()
Application.CommandBars("Ply").Controls("Rename").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Ply").Controls("Rename").Enabled = True
End Sub

Now when I right-click Sheet Name, Rename option is disabled. But Format -> Organize Sheets -> Rename Sheet is still enabled. Also, sheet can be renamed by double-clicking on Sheet Name. I also added the following on Sheet Deactivate:

Private Sub Worksheet_Deactivate()
Me.Name = "Sheet1"
End Sub

It partially solves the problem that if sheet is renamed inadvertently, then it will get its name again. But the very fact that user is able to rename the sheet is making end-users unhappy.

I watched the video available at this link:

http://msdn.microsoft.com/en-us/library/dd627337(office.12).aspx

It shows a way for Microsoft Word, where clicking Macros in Developer tab and selecting Word Commands, the default behavior can be overridden but such option is not available in Excel 2007.

 

[GlennUK]

How are you protecting your workbook exactly? I have no problems ... Review/Protect Workbook/Protect Structure And Windows, and macros work fine.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AzizKamal]

Thanks Glenn.

I opened the file. (filename is LWP.xlsm)

Then in cell B5, I selected a value from a dropdown. This is one of the three sheets I have on this workbook. Values in A5, C5 and D5 populated automatically through macro code.

Then I cleared values in cell B5. Values in A5, C5 and D5 also disappeared due to macro code.

Then I selected Review -> Protect Workbook -> Protect Structure and Windows, typed 123 in Password text box, reentered the password and clicked OK.

Then I saved the workbook and closed it. When I reopened LWP.xlsm, I got a Security Warning: Macros have been disabled. I clicked Options, but there is no radio button; Enable this content.

Now I selected value in B5 from dropdown, but there was no value picked in cells A5, C5 and D5.

 

[GlennUK]

This sounds like your security settings are affecting the options available, as I am getting an Enable Content prompt. Can you check to see if anything in your settings would cause this?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AzizKamal]

Thanks Glenn.

I did not get Enable Content prompt.

While analyzing further, I viewed a Microsoft Article, according to which the resolution is:

http://support.microsoft.com/kb/927150/en-us

To resolve this behavior, install 'The 2007 Microsoft Office Suite Service Pack 2'

I did not install SP2. Instead I followed steps, mentioned in WORKAROUND section. By adding ExcelBypassEncryptedMacroScan value, macros enabled and worked fine.

 

[GlennUK]

That's great. And thanks for sharing your solution ... that will definitely be useful for the future.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AzizKamal]

I found one more solution which does not even require installing service pack 2 or adding any value in registry. I would like to share this too.

The solution is:

Do not protect workbook using Review -> Protect Workbook -> Protect Structure and Windows. Instead add the following VBA Code:

Private Sub Workbook_Open()
ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Unprotect Password:="123"
End Sub

Now when you open the workbook, you get Enable Content prompt because at that time the workbook is not protected. As soon as you enable content, the following line executes:

ActiveWorkbook.Protect Password:="123", Structure:=True, Windows:=False

and the workbook becomes protected. But since the macros have already been enabled, executing the above line does not make them disabled and they are available.

 

[GlennUK]

That looks like a good workaround too. Thanks.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[firefytr]

May I ask why you did not install the SP?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[AzizKamal]

When I discussed this service pack solution in my team, they analyzed the system requirements. According to this link:

http://www.microsoft.com/downloads/...FamilyID=b444bf18-79ea-46c6-8a81-9db49b4ab6e5

Supported Operating Systems for Office 2007 Service Pack 2 are:
Windows Server 2003 Service Pack 2;Windows Server 2008;Windows Vista Service Pack 1;Windows XP Service Pack 3

We are currently using Windows XP Service Pack 2. So the team was of the opinion that upgrading to Windows XP Service Pack 3 may require testing other systems too and an alternate approach will be beneficial.

 

[firefytr]

I highly doubt that. But good luck.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[cmeagan656]

I can tell you from personal experience, Office 2007 Service Pack 2 works just fine on Windows XP Service Pack 2. The only reason it is not a "Support Operating System" is because Microsoft has dropped support for Windows XP Service Pack 2.

Hope this helps.

Please help us help you. Read Tek-Tips posting polices before posting.
Canadian members check out Tek-Tips in Canada for socializing, networking, and anything non-technical.