Cannot see excel sheet in VBE

Question

I am working with an Excel file that was created by somebody else. One sheet containing Macros appears to be password protected, but what I don't understand is that I cannot see it in VBE under the sheet list. The sheet tab is visible in Excel, but I cannot see the content.

Is there any way to unhide it in VBE?

Answer 1

As far as I know, there is no way you can hide a sheet from VBE! However, you can rename it there (in effect changing the .CodeName of the worksheet). Thus, if you know the Excel worksheet name (the one you see in the Excel worksheet tab), but cannot find it in VBE, go to the Immediate window in VBE (Ctrl-G) and run

? Worksheets("YourName").CodeName

- this should give you the name under which it can be found in the VBE Project tree.

Answer 2

One sheet containing Macros

Does that refer to Excel 4.0 macros?

Worksheets containing Excel 4.0 macros don't appear to be visible within the list in VBE.

They do appear to be accessible from VBA to some extent: using Excel 2007 I inserted an Excel 4.0 macro sheet to a workbook then tried the following:

Public Sub TestAccessToXL4MacroSheet()
Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet ' succeeds
    Debug.Print ws.Name               ' outputs "Macro1"
    Set ws = Worksheets("Macro1")     ' fails: "Subscript out of range"
End Sub