View Full Version : Creating a new worksheet with appropriate "(name)" attribute
robert32
01-17-2011, 08:21 AM
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
01-17-2011, 09:39 AM
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
robert32
01-18-2011, 03:56 AM
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.
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
01-18-2011, 04:53 AM
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.