CODEWITHSUNDEEP
excelvbaexcel-4.0
Natalie
Natalie

Reputation: 55

VBA Well-hidden macro

I'm working on an inherited spreadsheet (creation date 1998, or maybe earlier) and need to change the references on a dialog sheet. I can't see the macro in the run macro list, or anything similar, the sheet itself doesn't show up in the VBA project explorer window, which sounds like it could be an Excel 4.0 macro from other questions I've read here. A lot of people suggest rewriting an Excel 4.0 macro from the beginning, but the problem is that I don't actually know what the macro does.

Specifically, the macro is linked to a command button, and when I click on 'Assign macro', the name of the macro is written in the 'Macro name' text box, but it's not actually listed in the drop-down menu. If I try to assign this macro to another command button by typing the same name into the 'Macro name' text box, I get an error that the macro is unavailable.
(Screenshot link: https://i.sstatic.net/ph60w.jpg)

I tried using the solution here (Cannot see excel sheet in VBE), and because the sheet I'm interested in is not actually a worksheet, I declared ws as a dialog sheet.

Public Sub TestAccessToXL4MacroSheet()

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

End Sub

The result is that the name of the sheet appears in the immediate window. (Screenshot link: https://i.sstatic.net/DyF80.jpg)

Is this the intended result? I can already see the sheet name as a tab, I want to see the sheet itself in the VBA editor.

Upvotes: 2

Views: 4642

Answers (1)

ASKhan
ASKhan

Reputation: 36

XL4 worksheets (your macro1 sheet for example) are not visible in VB IDE.

To view the sheet in Excel right click on one of the visible sheet tabs and from the menu select the command "Unhide...". You can select the macro1 sheet and click "OK".

Alternatively, try

Sub ShowAllSheets()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Sheets
        sh.Visible = True
    Next
End Sub

Then you will need to convert the XL4 code to VBA manually.

Upvotes: 2

Related Questions