You are agreeing with the opposite of what I proposed as being the best way!
IOW better to loop the sheets rather than bothering with the VBProject
object model

If you really want to return the sheet name that way you could do

sName = Activeworkbook.VBProject.VBComponents(sCode).Prope rties("Name")

Regards,
Peter T


"michael.beckinsale" wrote in message
...
Hi Peter / Don

Many thanks for your replies, hope you can help out with a bit of
tweaking!

Peter - The code below is what l have ended up using along the lines
of your suggestion. I would not have thought about approaching it this
way at all, l simply thought that you could reference a cell that
contained a sheet codename and return the sheet name!

Sub GetSheetCodeName()

Dim sCodename As String

For i = 1 To 26
sCodename = Cells(i, 1).Value
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName = sCodename Then
Cells(i, 3).Value = ws.Name
Exit For
End If
Next
Next i


End Sub

I completely agree with your comment that this would be better done
using code within VBE. The following is the the code that Chip
supplied and VBComp.Name returns the sheetcode name. What code would
you use to return the sheet name as it appears on the sheet tab? I
have tried many combinations to no avail.

Sub ListModules()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim ws As Worksheet
Dim Rng As Range

Set VBProj = ActiveWorkbook.VBProject
Set ws = ActiveWorkbook.Worksheets(ModuleListSheet)
Set Rng = ws.Range("A1")

For Each VBComp In VBProj.VBComponents
Rng(1, 1).Value = VBComp.Name
Rng(1, 2).Value = ComponentTypeToString(VBComp.Type)
On Error Resume Next
Rng(1, 3).Value = ActiveWorkbook.Sheets(VBComp.Name).Name
On Error GoTo 0
Set Rng = Rng(2, 1)
Next VBComp

End Sub

Don - I am l missing something here (or most probably l didn't explain
myself clearly) but doesn't your code simply list the sheet names
starting in cell(1,1) ?

Kind regards

Michael