Worksheet Number Max

[Liz]

I have a macro that deletes about 5 worksheets (some are reports and some are
charts) then recreates the worksheets with the latest data. I assign these
worksheets specific names through the macro as well. I have noticed in the
VBA editor that the Sheet/chart number continually increases (even though the
sheet has been given a name). Will I run into problems eventually running
this macro...for instance...I appear to be creating sheet/chart number 200+
now in the VBA editor with my macro, and every time I run the macro the
sheet/chart number increases. Can you help me understand what this sheet
number (or is it index?) is for in the VBA editor and if there is a maximum I
should be concerned about?

Thanks so much,
Liz

 

[Bernie Deitrick]

Liz,

You can change the codename of the sheet or chart after you have added them. Add lines like this

ChangeShtCodeName Worksheets("Sheet1"), "shtSheet5"
ChangeChartCodeName Charts("Chart1"), "chtChart1"


after you have added the sheet or chart (change "Sheet1" and "ShtSheet5", etc, as appropriate)

add this code to your project:

Sub ChangeShtCodeName(oSht As Worksheet, sNewName As String)
oSht.Parent.VBProject.VBComponents(oSht.CodeName) _
.Properties("_CodeName") = sNewName
End Sub

Sub ChangeChartCodeName(oCht As Chart, sNewName As String)
oCht.Parent.VBProject.VBComponents(oCht.CodeName) _
.Properties("_CodeName") = sNewName
End Sub

If you need more help with that, post your code....

HTH,
Bernie
MS Excel MVP

 

[Liz]

Hi Bernie,
I am sorry, but I am a bit confused. I have added the 2 macros below to my
module/project. I have then added the sample code to my macro below, but I
don't think I am applying it correctly. A sample of my code is below, which
identifies the sheet names and deletes/recreates them. Then at the end I
tried to use your code and modify it for my purposes...I am sure I modified
for my purposes incorrectly since I get an error at the new macros you gave
me...please advise.

btw - the "sProjID" variable is pulled from a cell value on the worksheet.

Thanks so much!

'Name Sheets to be used in macro
sWBS = sProjID & " - WBS"
sSummary = sProjID & " - Summary Report"
sProjChart = sProjID & " - Project Chart"
sWeightChart = sProjID & " - Weight Chart"
sBackup = ProjID & " - WBS Backup"

'Delete summary report, charts and backup worksheet, if they exist, and
create/recreate them.

Application.DisplayAlerts = False
On Error Resume Next 'continue with macro if sheets do not yet exist for
deletion

Sheets(sSummary).Delete
Worksheets.Add.Name = sSummary

Sheets(sProjChart).Delete
Worksheets.Add.Name = sProjChart

Sheets(sWeightChart).Delete
Worksheets.Add.Name = sWeightChart

Sheets(sBackup).Delete
Worksheets.Add.Name = sBackup

On Error GoTo 0
ChangeShtCodeName Worksheets(sWBS), "shtSheet1" & sProjID 'rename of WBS
worksheet
ChangeShtCodeName Worksheets(sSummary), "shtSheet2" & sProjID 'rename of
summary worksheet
ChangeShtCodeName Worksheets(sWBSBackup), "shtSheet3" & sProjID 'rename of
WBS worksheet
ChangeChartCodeName Charts(sWeightChart), "chtChart1" & sProjID 'rename of a
chart
ChangeChartCodeName Charts(sProjChart), "chtChart2" & sProjID 'rename of a
chart

 

[Bernie Deitrick]

Liz,

The codename cannot have spaces, so if sProjId can have a space, you need to remove it (using
Replace)

And there appears to be a problem with the first new sheet that is attempted to be renamed, so I add
one sheet to get past that, then delete it.

Also, you are not actually adding charts, you are adding only worksheets....


HTH,
Bernie
MS Excel MVP

Sub TryNow()
'Name Sheets to be used in macro
Dim sProjID As String
Dim sWBS As String
Dim sSummary As String
Dim sProjChart As String
Dim sWeightChart As String
Dim sBackup As String

sProjID = "Test123"

sWBS = sProjID & " - WBS"
sSummary = sProjID & " - Summary Report"
sProjChart = sProjID & " - Project Chart"
sWeightChart = sProjID & " - Weight Chart"
sBackup = sProjID & " - WBS Backup"

'Delete summary report, charts and backup worksheet, if they exist, and create/recreate them.

Application.DisplayAlerts = False
On Error Resume Next 'continue with macro if sheets do not yet exist for deletion

Sheets(sSummary).Delete
Sheets.Add.Name = sSummary

Sheets(sWBS).Delete
Sheets.Add.Name = sWBS

Sheets(sProjChart).Delete
Sheets.Add.Name = sProjChart

Sheets(sWeightChart).Delete
Sheets.Add.Name = sWeightChart

Sheets(sBackup).Delete
Sheets.Add.Name = sBackup

'For some reason, the first new sheet
'attempted to be renamed does not get
'a new codename, so we'll add in one
'to fail, and delete it late

Sheets.Add.Name = "Dummy"

ChangeShtCodeName Sheets("Dummy"), "ShtDummy" 'This will fail
ChangeShtCodeName Sheets(sWBS), _
Replace("shtSheet1" & sProjID, " ", "") 'rename of WBS Worksheet
ChangeShtCodeName Sheets(sSummary), _
Replace("shtSheet2" & sProjID, " ", "") 'rename of Summary Worksheet
ChangeShtCodeName Sheets(sProjChart), _
Replace("chtChart2" & sProjID, " ", "") 'rename of a Chart
ChangeShtCodeName Sheets(sWeightChart), _
Replace("chtChart1" & sProjID, " ", "") 'rename of a Chart
ChangeShtCodeName Sheets(sBackup), _
Replace("shtSheet3" & sProjID, " ", "") 'rename of WBS Worksheet

Sheets("Dummy").Delete
End Sub

Sub ChangeShtCodeName(oSht As Worksheet, sNewName As String)
oSht.Parent.VBProject.VBComponents(oSht.CodeName) _
.Properties("_CodeName") = sNewName
Exit Sub
End Sub

Sub ChangeChartCodeName(oCht As Chart, sNewName As String)
oCht.Parent.VBProject.VBComponents(oCht.CodeName) _
.Properties("_CodeName") = sNewName
End Sub