Opening workbook not making it the active workbook

[nigelandrewfoster]

Strange I know, but a few versions of Excel I am developing for do not seem to be working as expected. A user already has workbook A open. She opens workbook B, it runs the Workbook_Open code but bombs at the first range reference it encounters (in bold on second macro, below) because it thinks workbook A is still the active workbook and the range or worksheet does not appear in A. Well, I thought strange enough but ok - I'll put ThisWorkbook.Activate in the Workbook_Open macro. It STILL thinks workbook A is the active workbook at the point the excution stops with the error. Any suggestions? A workaround?

Private Sub Workbook_Open()    Dim MetaUpdate As Boolean
    Application.ScreenUpdating = False
    Application.CellDragAndDrop = False
    If MsgBox("Update Metadata?", vbYesNo) = vbYes Then MetaUpdate = Update_Metadata()
    ThisWorkbook.Activate
    Call Update_Type_Dropdowns   ** Routine bombs as soon as it runs this macro (below)
    Call All_But_Jobs
    Range("I_Dropdown_Refresh") = False
    Call Protect_All
    Application.ScreenUpdating = True
End Sub

Sub Update_Type_Dropdowns() ' This changes EVENT DATA sheet.  Which ACTIVITIES are allowed for each STORE TYPE?    Dim rEvent As Range, col As Integer
[B]    With Worksheets("Event Data")[/B]
        .Range("Dropdown_Lists").Offset(1).Resize(250, 11).ClearContents
        For Each rEvent In .Range(.Range("Event").Offset(1), .Range("Event").Offset(65535).End(xlUp))
            If rEvent <> "" And rEvent.Offset(, -1) <> "" Then
                If RangingValid(rEvent.Offset(, 2).Value) = True Then ' Check Event Ranging against Overview Ranging
                    For col = 0 To 10 ' Go through each Event and add to permitted Activity Dropdowns
                        If (rEvent.Offset(, 1).Value2 And 2 ^ col) = 2 ^ col Then
                            Range("Dropdown_Lists").Offset(65535, col).End(xlUp).Offset(1) = rEvent
                        End If
                    Next
                End If
            End If
        Next
    End With
End Sub

 

[gallen]

Seems weird. Have you tried fully qualifying the sub? (Just need to add your sheet name)

Call ThisWorkbook.Sheets("YOURSHEET").[COLOR=#333333]Update_Type_Dropdowns[/COLOR]

 

[nigelandrewfoster]

Hi gallen, thanks very much for your reply.

Not qualified procedure calls like this before. What does Sheets refer to here? The sub 'Update_Type_Dropdowns" is in a standard module. My concern is that even if this works, at some point Excel needs to know the active workbook is the one that is running otherwise I have to qualify EVERYTHING!!

 

[gallen]

Hi gallen, thanks very much for your reply.

Not qualified procedure calls like this before. What does Sheets refer to here? The sub 'Update_Type_Dropdowns" is in a standard module. My concern is that even if this works, at some point Excel needs to know the active workbook is the one that is running otherwise I have to qualify EVERYTHING!!

Sorry for late reply. Sheets is just the collection of sheets in the workbook, so in the brackets you put the sheet name to reference that particular sheet. I see your point though. You shouldn't have to even activate it. Once the workbook is opened it becomes the active workbook.
Is there any code in the Workbook_Activate event that is switching focus back?

Only other thing I can think of as a band-aid and not a solution is to check for the active workbook name:

Sub Update_Type_Dropdowns() ' This changes EVENT DATA sheet.  Which ACTIVITIES are allowed for each STORE TYPE?

Dim rEvent As Range, col As Integer


[COLOR=#ff0000][B]    If ActiveWorkbook.Name <> ThisWorkbook.Name Then ThisWorkbook.Activate[/B][/COLOR]
    
    With Worksheets("Event Data")
        .Range("Dropdown_Lists").Offset(1).Resize(250, 11).ClearContents
        For Each rEvent In .Range(.Range("Event").Offset(1), .Range("Event").Offset(65535).End(xlUp))
            If rEvent <> "" And rEvent.Offset(, -1) <> "" Then
                If RangingValid(rEvent.Offset(, 2).Value) = True Then ' Check Event Ranging against Overview Ranging
                    For col = 0 To 10 ' Go through each Event and add to permitted Activity Dropdowns
                        If (rEvent.Offset(, 1).Value2 And 2 ^ col) = 2 ^ col Then
                            Range("Dropdown_Lists").Offset(65535, col).End(xlUp).Offset(1) = rEvent
                        End If
                    Next
                End If
            End If
        Next
    End With


End Sub

 

[GTO]

If it bombs at With Worksheets("Event Data"), why not preface that with ThisWorkbook, i.e. With ThisWorkbook.Worksheets("Event Data"). That way it shouldn't matter what workbook is Active.

Mark