Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Worksheet Codenames 2
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Worksheet Codenames 2
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" wrote: 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). -- Regards, Tom Ogilvy "Alasdair Stirling" <Alasdair wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Worksheet Codenames 2
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 he 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. -- Regards, Tom Ogilvy "Alasdair Stirling" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Worksheet Codenames 2
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 "Tom Ogilvy" wrote: 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 he 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. -- Regards, Tom Ogilvy "Alasdair Stirling" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops with sheet codenames | Excel Discussion (Misc queries) | |||
Excel Worksheet Codenames | Excel Programming | |||
Using worksheet codenames | Excel Programming | |||
From my Addin, referring to activeworkbook's sheets by their codenames | Excel Programming | |||
strange worksheet.codenames in XL97 | Excel Programming |