Subscript our of range - two excel document open

Question

Here's my issue:

Private Sub Workbook_Open()
'the name of the current user
Dim UserName As String
'on opening, find out who this is (and convert to lower case)
    Dim wb As Workbook
    Set wb = Workbooks("Staffing Log - HQ - 2018 - 2019.xlsm")
    wb.Activate


UserName = LCase(Environ("UserName"))
On Error Resume Next

Application.Visible = False
'ActiveWindow.Visible = False
 Sheets("GoodDBData").Visible = True ERROR OCCURS HERE
end sub

When I open this workbook while having another workbook already open, I keep getting the subscript out of range. It's as if even if I tell the program to ACTIVATE the proper workbook with the wb.Activate it does not recognize it.

Any idea why? Thank you

Try specifying the work book the sheet is in: `wb.Sheets("GoodDBData").Visible` — cybernetic.nomad, Sep 12 '18 at 20:22

score 2 · Answer 1 · answered Sep 12 '18 at 20:32

Your code is in the ThisWorkbook private code sheet. In a private code sheet, any references that are not fully qualified revert parentage to that workbook/worksheet.

Sheets("GoodDBData").Visible = True

This defaults to the workbook containing the code. If you are looking for the GoodDBData worksheet in Workbooks("Staffing Log - HQ - 2018 - 2019.xlsm") then qualify the parent workbook.

wb.workSheets("GoodDBData").Visible = True

Subscript our of range - two excel document open

2 Answers2