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

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

"Philip" wrote:

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

Hi Philip,

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

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


---
Regards,
Norman


"Philip" wrote in message
...
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

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)

"Philip" wrote in message
...
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

Thanks all,

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

Philip

"Norman Jones" wrote:

Hi Philip,

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

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


---
Regards,
Norman


"Philip" wrote in message
...
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

Hi Philip

Try below, please.


Option Explicit


Sub CodeName()
'// Codename
ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName) _
.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


--
Best regards
Joergen Bondesen


"Philip" wrote in message
...
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