Opening workbook not making it the active workbook

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
735
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?

Code:
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

Code:
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
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
Seems weird. Have you tried fully qualifying the sub? (Just need to add your sheet name)

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

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
735
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

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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:

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,530
Messages
5,659,355
Members
418,498
Latest member
nattynat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top