Clever Geek Handbook

Can't see Excel sheet in VBE

I am working with an Excel file that was created by someone else. One sheet containing macros is password protected, but I don't understand that I can't see it in VBE in the sheet list. The sheet tab appears in Excel, but I can't see it.

Is there a way to show it in VBE?

+3


source to share


2 answers


One sheet containing macros

Does this apply to Excel 4.0 macros?

Worksheets containing Excel 4.0 macros do not appear in the list in VBE.



They seem to be available from VBA to some extent: with Excel 2007, I inserted an Excel 4.0 macro sheet into a workbook and 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
+3


source


As far as I know, you cannot hide a sheet from VBE! However, you can rename it there (by actually changing the .CodeName

worksheet). Thus, if you know the name of the Excel table (the one you see in the tab of the Excel sheet) but cannot find it in the VBE, go to the Immediate window in VBE ( Ctrl- G) and run



? Worksheets ("YourName"). CodeName
- this should give you a name by which it can be found in the VBE project tree.
0


source






More articles:


All Articles