Opening workbook not making it the active workbook

nigelandrewfoster

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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,009
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
747
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,009
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,155
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
 

Forum statistics

Threads
1,172,129
Messages
5,879,218
Members
433,411
Latest member
stevemoney

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