CODENAME and selecting sheets through VBA

[bill]

Hello all.

I generally do:
dim ThisBook as workbook
dim ThisSheet as worksheet

set thisbook = activeworkbook
set thissheet =thisbook.worksheets("MudFace") ' Get it?

Anyway, suppose I have a string variable holding the CODENAME of a sheet.

How would I go about selecting the sheet (for processing BTW) in code?
Would like to avoid enumeration.

Thanks!

 

[Juan Pablo González]

Unfortunately, I don't know of another way, that isn't a For loop:

For Each Ws in TheBook.Worksheets
   If Ws.CodeName = "TheCodeName" Then Exit For
Next Ws

 

[Juan Pablo González]

Wait, there looks that there is a way (Be careful in XP, because it needs access to the VBProject)

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> SheetName(Wb <SPAN style="color:#00007F">As</SPAN> Workbook, CodeName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    SheetName = Wb.VBProject.VBComponents(CodeName).Properties("Name").Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> TestIt()
    <SPAN style="color:#00007F">Dim</SPAN> St <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Wb <SPAN style="color:#00007F">As</SPAN> Workbook
    
    <SPAN style="color:#00007F">Set</SPAN> Wb = ActiveWorkbook
    St = "Hoja1"
    MsgBox SheetName(Wb, St)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

The function SheetName returns the name of the sheet as you see it in Excel.. then, you could use it like

Set Ws = Wb.Sheets(SheetName(Wb, TheCodeName))

 

[bill]

Juan,

Very Nice.

Worked like a champ.