none
Change the Codename of a worksheet RRS feed

  • Question

  • Hi

    Is it possible to change the codename of a worksheet programmatically? I have the following test code which delete TEST1 sheet, recreates it and then attempts to rename the codename to T1 - but sadly it does not work. Any ideas please?

    many thanks

    Peter

    Private Sub Worksheet_Change(ByVal Target As Range)

     
     Application.DisplayAlerts = False
     
     Sheets("Test1").Delete
     Application.DisplayAlerts = True

    ThisWorkbook.Sheets.Add after:=ActiveSheet
    ActiveSheet.Name = "Test1"
    ActiveSheet.CodeName = "T1"
    End Sub

    Monday, November 7, 2016 10:12 AM

Answers

  • Hi,

    If your query was answered kindly mark them as answered. This will help in keeping this forum neat. Thanks in advance.


    Vish Mishra

    • Marked as answer by py1 Monday, November 7, 2016 1:04 PM
    Monday, November 7, 2016 11:55 AM

All replies

  • Hi,

    Worksheet.CodeName is a readonly property in VBA.

    You can set it by using the VBAComponent :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.DisplayAlerts = False
      
     Sheets("Test1").Delete
     Application.DisplayAlerts = True
    
    ThisWorkbook.Sheets.Add after:=ActiveSheet
    
    With ActiveSheet
    .Name = "Test1"
    ThisWorkbook.VBProject.VBComponents(.CodeName).Properties("CodeName") = "T1"
    End With
    End Sub


    Vish Mishra

    Monday, November 7, 2016 10:36 AM
  • Hi Vish

    Thanks for the prompt reply.

    I tried this and got error 1004 - Programmatic access to Visual Basic Project is not trusted

    is there something else I need to do to make this work?

    thanks

    Peter

    Monday, November 7, 2016 10:42 AM
  • Hi,

    Yes this is because of security feature in Excel. You can enable and mark the macro as trusted in Trust Center of excel 

    To allow programmatic access to the Visual Basic project, follow these steps, as appropriate for the version of Excel that you are running.

    Excel 2007

    1. Click the Microsoft Office Button, and then click Excel Options.
    2. Click Trust Center.
    3. Click Trust Center Settings.
    4. Click Macro Settings.
    5. Click to select the Trust access to the VBA project object model check box.
    6. Click OK to close the Excel Options dialog box.

    Excel 2003

    1. On the Tools menu, point to Macro, and then click Security.
    2. In the Security dialog box, click the Trusted Sources tab.
    3. Select the Trust access to Visual Basic Project check box.
    4. Click OK.

    Source: https://support.microsoft.com/en-us/kb/813969


    Vish Mishra

    Monday, November 7, 2016 10:45 AM
  • A small change in the code as well :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.DisplayAlerts = False
      
     Sheets("Test1").Delete
     Application.DisplayAlerts = True
    
    ThisWorkbook.Sheets.Add after:=ActiveSheet
    
    With ActiveSheet
    .Name = "Test1"
    ThisWorkbook.VBProject.VBComponents(.CodeName).Properties("_CodeName") = "T1"
    End With
    End Sub


    Vish Mishra

    Monday, November 7, 2016 10:54 AM
  • Ok thanks - a couple more questions please?

    If I do this does it apply to me only? so if I give the workbook to someone else to execute will it fail unless they do the above as well? if this is the case then I cannot make may macro distributable?

    Also , t the setting above pose any risk?

    thank you.

     

    Monday, November 7, 2016 10:56 AM
  • Hi,

    To answer your question:

    1. Yes this security is a local setting. It mean if you share it to someone else then that person has to allow to run this macro.

    2. Risk is that: for example if you have allowed to run all the macros in your System then any excel workbook which has got macro may run without you to know about it. 


    Vish Mishra

    Monday, November 7, 2016 11:02 AM
  • Hi,

    If your query was answered kindly mark them as answered. This will help in keeping this forum neat. Thanks in advance.


    Vish Mishra

    • Marked as answer by py1 Monday, November 7, 2016 1:04 PM
    Monday, November 7, 2016 11:55 AM