Creating a new worksheet with appropriate "(name)" attribute

[robert32]

Hi,

I am writing some VBA code that will add a new worksheet to a spreadsheet I have.

I would like the worksheet to be named using a particular convention, so was thinking of using some code a bit like:

Sheets.Add.Name = "Variations" + CStr (VariationsWorksheetCount)

The VariationsWorksheetCount will be incremented everytime a new worksheet is added.

I'm fairly comfortable the above will work as intended, but is there a way of assigning a value to the "(name)" attribute in the properites window. I don't want it to default to Sheet1, Sheet2, etc. everytime.

Any help would be grately appreciated.


Cheers,

Rob.

[Tinbendr]

I always prefer to name a worksheet object and work with it as opposed to adding a worksheet, then using the ActiveWorkbook object. That way, I know which WB/WS I'm dealing with.

Dim WS As Worksheet

Set WS = Worksheets.Add(after:=Worksheets.Count)
With WS
.Name = "Variations" & CStr(Worksheets.Count)
'other stuff with WS
End With

[GTO]

...

[robert32]

Thanks for this. I'm not sure if it addresses my problem. It doesn't seem to name the attribute of the workbook. Maybe I'm missing something...



Dim WS As Worksheet

Set WS = Worksheets.Add(after:=Worksheets.Count)
With WS
.Name = "Variations" & CStr(Worksheets.Count)
'other stuff with WS
End With




Rob.

[GTO]

I'm fairly comfortable the above will work as intended, but is there a way of assigning a value to the "(name)" attribute in the properites window. I don't want it to default to Sheet1, Sheet2, etc. everytime.

Hi Rob,

I understood the same as TinBender, but with your latest response, I am wondering about the above part.

Might you be asking if there is a way to programmatically change the CodeName of a worksheet?

Mark

[robert32]

Hi Rob,

I understood the same as TinBender, but with your latest response, I am wondering about the above part.

Might you be asking if there is a way to programmatically change the CodeName of a worksheet?

Mark

Hi Mark,

Yes. The value that appears in the "(name)" attribute of the properties window in the VBA editor. Not the "name" attribute (i.e. the name that appears on the worksheet tab in the front-end of Excel).


Cheers,

Rob.

[GTO]

Hi again,

That is referred to as the sheet's CodeName property. From vb help:

CodeName Property

Returns the code name for the object. Read-only String.
Note The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.

This is incorrect though, as you can actually get to the property. Not sure how you would use this to advantage, but try:


Option Explicit

Sub exa3()
Dim wks As Worksheet

With ThisWorkbook
Set wks = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count), _
Type:=xlWorksheet)

wks.Name = "Variations_" & Format(.Worksheets.Count, "00")

wks.Parent.VBProject.vbcomponents(wks.CodeName) _
.Properties("_CodeName") = "sht" & wks.Name
End With
End Sub
Hope that helps,

Mark