Public Sub FindWorksheetCodeModule()
Dim component As VBIDE.VBComponent
Dim aWorkbook As Workbook
Set aWorkbook = Workbooks("Book1.xls")
Set component = aWorkbook.VBProject.VBComponents(aWorkbook.Worksheets("Sheet1").CodeName)
MsgBox component.Name
End Sub