CODEWITHSUNDEEP
excelvba
Selkie
Selkie

Reputation: 1255

How to create a new Excel worksheet and create a given name in the VBA project

When you create a new worksheet, you can set the name with .name = "Name Here!" However, the VBA object will still have "Sheet1" as the name.

How do I create a new sheet and set the VBA object name?

Please note, I am not asking how to set the name as displayed on the workbook, nor the .name property.

Upvotes: 1

Views: 292

Answers (1)

Soulfire
Soulfire

Reputation: 4296

This should do the trick:

Public Sub Change_Codename()

    Dim ws As Object

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    With ws

        .Parent.VBProject.VBComponents(.CodeName) _
        .Properties("_CodeName") = "Test"

    End With

End Sub

Just change "Test" to whatever you want the sheet's code name to be.

Here is how my objects look after executing the code:

enter image description here

Also, make sure you have trusted access to the VBA project model by going to File-->Options-->Trust Center-->Trust Center Settings.... Under Macro Settings be sure to tick "Trust access to the VBA project object model", like so:

enter image description here

Upvotes: 5

Related Questions