Setting the CodeName of a new worksheet in the VBE Editor

[Guest]

Hi,

When I programatically add a new worksheet in VBA, is there a way to
programatically set the CodeName property ...

So instead of showing as Sheet1 in the VB Editor 'Project Explorer', the new
sheet shows as what I want it to be.

This is for Excel 2003

thanks for any help

Philip

 

[Guest]

Paul,

When the sheet is created it is the active sheet so this should be the next
line after creation.

ActiveSheet.Name = "Your Name"

Mike

 

[Norman Jones]

Hi Philip,

To change the sheet's codename, try something like:

'=============>>
Public Sub Tester()
ThisWorkbook.VBProject.VBComponents _
(Worksheets("Sheet2").CodeName).Name = "aName"
End Sub
'<<=============

 

[Bob Phillips]

Worksheets.Add
With ActiveSheet
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "wsNewSheet"
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks all,

In the end I found it was fairly simple like this:
thisworkbook.VBProject.VBComponents("Sheet1").Name = "Phil"

Philip

 

[Joergen Bondesen]

Hi Philip

Try below, please.


Option Explicit


Sub CodeName()
'// Codename
ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName) _
.Name = "Philip"
End Sub


Sub SheetVBAname()
Dim ShVBAname As String
MsgBox "VBA codename is: " & vbCr & ActiveSheet.CodeName
End Sub

' <<<<

Option Explicit

Sub TESTSheetAddNameCodeName()
Dim NewShName As String '***
Dim NewShCodeName As String '***
Dim sh As Worksheet
Dim ws As Worksheet

NewShName = "Philip"

NewShCodeName = "PhilipVBA"

SheetAddNameCodeName NewShName, NewShCodeName
End Sub


'----------------------------------------------------------
' Procedure : SheetAddNameCodeName
' Date : 20060312
' Author : Joergen Bondesen
' Modifyed by :
' Purpose :
' Note :
'----------------------------------------------------------
'
Function SheetAddNameCodeName(NewShName As String, _
NewShCodeName As String)
Dim sh As Worksheet
Dim ws As Worksheet

'// Controle for codename
For Each sh In ThisWorkbook.Sheets
If sh.CodeName = NewShCodeName Then
MsgBox "Codename exist. Macro will terminate."
End
End If
Next sh

'// Check to see if Sheet exists and if not, create it.
On Error Resume Next
Set ws = Worksheets(NewShName)
On Error GoTo 0

If ws Is Nothing Then
Set ws = Worksheets.Add
ws.Name = NewShName

On Error Resume Next
ws.Parent.VBProject.VBComponents(ws.CodeName) _
.Properties("Name") = NewShName

Application.DisplayAlerts = False
If Err <> 0 Then ws.Delete
Application.DisplayAlerts = True
On Error GoTo 0
Else
MsgBox "Sheet name exist. Macro will terminate."
End
End If

'// Codename
ThisWorkbook.VBProject.VBComponents _
(ActiveSheet.CodeName).Name = NewShCodeName

End Function