Excel Worksheet Codenames 2

[Guest]

Thanks for the Help.

After some research I solved the problem as follows:

Sub alpha()
' Declare the variables
Dim VBComp As VBComponent, iExistCntr As Integer
Dim iNewCntr As Integer, sOldCodeName As String
Dim sNewCodeName As String
Dim sht As Worksheet, shtNewSheet As Worksheet
Dim iShtCntr As Integer
' Count the existing sht with name starting "Sheet"
iExistCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iExistCntr = iExistCntr + 1
End If
End If
Next VBComp
' Add the worksheet
ThisWorkbook.Sheets.Add
' Re-count the existing sht with name starting "Sheet"
iNewCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iNewCntr = iNewCntr + 1
If iNewCntr = iExistCntr + 1 Then
sOldCodeName = VBComp.Name
End If
End If
End If
Next VBComp
' Count sht that have been renamed
iShtCntr = 0
For Each sht In ThisWorkbook.Sheets
If Left(sht.CodeName, 6) = "MyCdNm" Then
iShtCntr = iShtCntr + 1
End If
Next sht
' Develop the new codename
If iShtCntr = 0 Then
sNewCodeName = "MyCdNm" & "Sheet1"
Else
sNewCodeName = "MyCdNm" & "Sheet" & iShtCntr + 1
End If
' Name the new worksheet
ThisWorkbook.VBProject.VBComponents(sOldCodeName). _
Name = sNewCodeName
End Sub

This code allows me to add new worksheets and control their order via the
codename that I assign.

I hope that it someone might find it usefull.

Regards,

Alasdair Stirling

 

[Tom Ogilvy]

1) why not stay in the original thread so any readers would see the solution
you developed associated with the problem it is designed to fix

2) why not gather your data in one or two passes. 4 loops seems excessive.

3) think the code I provided could be modified to do it in one loop (and
probably be more reliable).

 

[Guest]

Sorry about creating a new thread, it's the first time that I have posted to
the discussion.

I don't think that there is much difference between the ways that Tom Ogilvy
and I have identified the new sheet's codename, but as correctly pointed out
I have been able to reduce the number of loops as follows:

Sub alpha()
' Declare the variables
Dim VBComp As VBComponent, iExistCntr As Integer
Dim iNewCntr As Integer, sOldCodeName As String
Dim sNewCodeName As String
Dim sht As Worksheet, shtNewSheet As Worksheet
Dim iShtCntr As Integer
' Count the existing sht with name starting "Sheet"
iExistCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iExistCntr = iExistCntr + 1
End If
End If
Next VBComp
' Add the worksheet
ThisWorkbook.Sheets.Add
' Re-count the existing sht with name starting "Sheet"
iNewCntr = 0
iShtCntr = 0
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If Left(VBComp.Name, 5) = "Sheet" Then
iNewCntr = iNewCntr + 1
If iNewCntr = iExistCntr + 1 Then
sOldCodeName = VBComp.Name
End If
End If
End If
If Left(VBComp.Name, 6) = "MyCdNm" Then
iShtCntr = iShtCntr + 1
End If
Next VBComp
' Develop the new codename
If iShtCntr = 0 Then
sNewCodeName = "MyCdNm" & "Sheet1"
Else
sNewCodeName = "MyCdNm" & "Sheet" & iShtCntr + 1
End If
' Name the new worksheet
ThisWorkbook.VBProject.VBComponents(sOldCodeName). _
Name = sNewCodeName
End Sub

 

[Tom Ogilvy]

I don't think that there is much difference between the ways that Tom
Ogilvy

and I have identified the new sheet's codename,

Using the name of the new sheet specifically with the vbcomponent properties
vice counting sheets I would think is quite a bit different. I assume you
will rename all sheets to your code name - otherwise your method could fail
(and this assumes the added sheet will aways be the last - I can't say that
is a good assumption or not).

Plus, in the original question, you said that asking for the codename when
the vbe had not been opened caused problems - however, aren't you doing that
here:

sOldCodeName = VBComp.Name
Granted, your original was
Sheets(sNewShtName).CodeName

so perhaps using the VBComp.Name isn't a problem, but wondered if you had
tested that situation. (as i recall, that still caused problems, thus
reverting to the properties approach - but it has been a while since I have
played with it).

Not trying to give you a hard time, but I would see this solution as very
specific to your situation.

 

[Guest]

On consideration your method has significient advantages. This seems to work:

Sub alpha()
' Declare the proceedure variables
Dim VBComp As VBComponent
Dim sOldCodeName As String, iShtCntr As Integer
Dim sNewCodeName As String, sNewShtName As String
Dim sht As Worksheet, shtNewSheet As Worksheet
' Add the worksheet
ThisWorkbook.Sheets.Add
sNewShtName = ActiveSheet.Name
' Identify the new sheet code name
For Each VBComp In ThisWorkbook.VBProject.VBComponents
If LCase(VBComp.Properties("Name").Value) = _
LCase(sNewShtName) Then
sOldCodeName = VBComp.Properties("_CodeName").Value
Exit For
End If
Next VBComp
' Count sht that have been renamed
iShtCntr = 0
For Each sht In ThisWorkbook.Sheets
If Left(sht.CodeName, 6) = "MyCdNm" Then
iShtCntr = iShtCntr + 1
End If
Next sht
' Develop the new codename
If iShtCntr = 0 Then
sNewCodeName = "MyCdNm" & "Sheet1"
Else
sNewCodeName = "MyCdNm" & "Sheet" & iShtCntr + 1
End If
' Name the new worksheet
ThisWorkbook.VBProject.VBComponents(sOldCodeName). _
Name = sNewCodeName
End Sub