Workbook.Open Event not working correctly

[Poundland]

Hi Guys,

I'm baffled as to why the below code is not working correctly, the offending line of code as far as I can see is the Lineflow.Activate code line and for some reason, the Activate always defaults to a lower case 'a' not sure if this is why it is not working as it should.

Basically I would be expecting that once all the referred to documents have opened, the last of which is a shared document that the Lineflow would become the active document and would be displayed at the end of the routine, for some reason the first opened document is always the one that becomes active at the end of the routine.

Also for some reason the Screen Updating whilst set to false is still showing each document as it opens.

Can you help please and show me where i'm going wrong.


Private Sub Workbook_Open()
Dim Lineflow As Workbook
Set Lineflow = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Active Skus - Events.xlsm"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Common Data Feeds - Master.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Data Feeds - Events.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H911 Events and Supply Chain\Lineflow\Events Database.xlsm")
Application.DisplayAlerts = True
Lineflow.activate
Application.ScreenUpdating = True
End Sub

[SamT]

I don't see anything wrong with your code, except a possible timing issue. It may be that all the workbooks are not fully open when the LineFlow activate line is reached.

IMO, Your Workbook Event Procedure is overloaded. It is my preference to use Event subs like

Private Sub Workbook_Open()
OpenLineFlowBooks
Me.Activate
End Sub

This way, there is no troubleshooting of the Workbook_Open Sub. OpenLineFlowBooks does all the work that your Event Procedure does, and it means that if you ever want Workbook_Open to do something else, you only have to write one line to it, and then write another sub or Function that does what you need. That way, you only have to troubleshoot one small sub at a time.

Here is OpenLineFlowBooks. Note that all it does is loop thru the books and feed them to a another procedure, (OpenThisBook.) that actually opens the books. This allows you to use OpenThisBook in other places. Again, troubleshooting OpenLineFlowBooks is straight forward. I have verified that it works as is.

Private Sub OpenLineFlowBooks()
Const Mechandisers As String = "I:\H925 Merchandisers\Lineflow\Data Feeds\"
Const EventsAndSupply As String = "I:\H911 Events and Supply Chain\Lineflow\"
Dim BookList As Variant
Dim Success As Boolean
Dim i As Long

BookList = Array("Active Skus - Events.xlsm", _
"Common Data Feeds - Master.xlsx", _
"Data Feeds - Events.xlsx")

For i = 0 To UBound(BookList)
Success = OpenThisBook(Mechandisers, BookList(i), True)
If Not Success Then
MsgBox "Failed to Open " & BookList(i) & ". This Procedure will now Terminate."
Exit Sub
End If
Next

Success = OpenThisBook(EventsAndSupply, "Events Database.xlsm", True)
If Not Success Then
MsgBox "Failed to Open Events Database.xlsm. This Procedure will now Terminate."
Exit Sub
End If
End Sub

Finally, here is the Function that actually opens the books fed to it one by one by OpenLineFlowBooks. It compiles fine, but I have not tested it against your books and folders. I am not sure that the DoEvents line will fix any Timing issues. If not, it will take a bit more code.

Private Function OpenThisBook(BkPath As String, BkName As Variant, Optional Reading) As Boolean

Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error GoTo Failure

Workbooks.Open BkPath & BkName, ReadOnly:=Reading
DoEvents
OpenThisBook = True

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Function

Failure:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Function

This is just the way I like to do things. YMMV, there is no wrong way, as long as it works.

[snb]

You don't need a variable


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open "I:\H925 Merchandisers\Lineflow\Data Feeds\Active Skus - Events.xlsm", ReadOnly:=True
Workbooks.Open "I:\H925 Merchandisers\Lineflow\Data Feeds\Common Data Feeds - Master.xlsx", ReadOnly:=True
Workbooks.Open "I:\H925 Merchandisers\Lineflow\Data Feeds\Data Feeds - Events.xlsx", ReadOnly:=True
Workbooks.Open "I:\H911 Events and Supply Chain\Lineflow\Events Database.xlsm"

application.goto thisworkbook.sheets(1).cells(1)
End Sub

[Poundland]

Sam T,

Thank you for your code, unfortunately, exactly the same as my original code, active document visible at the end is the first document opened.

snb,

Same for your extra code line too, still the first document opened becomes the active document at the end of the code run.

Interestingly, and I am not sure why, but I used to have Workbook Activate and Deactivate Event codes that turned on and off the Excel Ribbon, when this was in place then the Lineflow did become the final active document at the end of the Workbook Open event. The user asked me to remove the hide ribbon routines and now the active sheet is as the issue I have given to you.

Below are the original codes for those Event Procedures; These were on Workbook Activate and Deactivate Events but are now not used.


Sub Activatecode()

Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
Application.ScreenUpdating = True
End Sub
Sub Deactivatecode()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
End Sub

[Jan Karel Pieterse]

What if you move all code in the _Open event to a normal sub and call that sub from the _Open event using Application.OnTime?

[SamT]

unfortunately, exactly the same as my original code, active document visible at the end is the first document opened.
I was afraid that might haqppen. The workbook opening is just too slow. I think you will need the Application WorkbookActivate Event.

This is my first time so be careful. This worked in my Excel XP

Insert a Class Module into your Project. Name it objApplication. Add this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
If Wb.Name = "Events Database.xlsm" Then ThisWorkbook.Activate
End Sub

In ThisWorkbook:
Add this Module level Declaration
Dim ThisApp As New objApplication

Add This sub

Sub InitializeThisApp()
Set ThisApp.App = Application
End Sub

Use this Workbook Open Event procedure

Private Sub Workbook_Open()
InitializeThisApp
OpenLineFlowBooks
End Sub

Make no other changes to the code I originally provided

[Aflatoon]

I'd suggest starting with JKP's solution.

[Poundland]

Sam T,

Followed all the instructions and inserted all code in correct places, all opens ok but active document at the end of the code run is the Data Feeds - Events workbook, still not the Lineflow.

[Poundland]

What if you move all code in the _Open event to a normal sub and call that sub from the _Open event using Application.OnTime?

I have googled the use of this method, and it's use has me baffled, how would I apply this to my issue?

[Aflatoon]

In a normal module:

Sub OpenOtherWorkbooks() Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Active Skus - Events.xlsm"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Common Data Feeds - Master.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Data Feeds - Events.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H911 Events and Supply Chain\Lineflow\Events Database.xlsm")
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ThisWorkbook.Activate
End Sub


and your workbook_Open code is then just:

Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 0, 1), "OpenOtherWorkbooks"
End Sub

[SamT]

Followed all the instructions and inserted all code in correct places, all opens ok but active document at the end of the code run is the Data Feeds - Events workbook, still not the Lineflow.

All four books are open? IS Data Feeds - Events the largest?

Change this sub thusly. You should see a message for each book opened.

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
MsgBox (Wb.Name & " Is Open")
If Wb.Name = "Events Database.xlsm" Then ThisWorkbook.Activate
End Sub
Also try

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
Static OpenBooks As Byte
OpenBooks = OpenBooks + 1
If OpenBooks = 4 Then ThisWorkbook.Activate
End Sub

[Poundland]

Aflatoon,

Used your procedure correctly and activated workbook at the end of the code run was the first workbook opened and not the Lineflow document.

[Poundland]

Sam T, no message appeared when any workbook was opened, and activated workbook at the end of the run was the Active Sku List workbook.

[Paul_Hossler]

If the 'sub-workbooks' have their own Workbook_Open() you can try disabling events





Private Sub Workbook_Open()
Dim Lineflow As Workbook
Set Lineflow = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Active Skus - Events.xlsm"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Common Data Feeds - Master.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Data Feeds - Events.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H911 Events and Supply Chain\Lineflow\Events Database.xlsm")
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Lineflow.activate '<<<< moved


End Sub

[SamT]

Sam T, no message appeared when any workbook was opened, and activated workbook at the end of the run was the Active Sku List workbook.
I gave two versions of the code. The second had a typo, fixed now. Both failed?

[Poundland]

OK, I have the code below which works, I have no idea why this code works over the above code that has been given to me, but it works, and the activated document at the end of the opening routine is the Lineflow.

If somebody could explain to me why this works and yet everything else doesn't, then your a better man than I ;)

Workbook.Open Event

Private Sub Workbook_Open()
Dim Lineflow As Workbook
Set Lineflow = ThisWorkbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Active Skus - Events.xlsm"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Common Data Feeds - Master.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H925 Merchandisers\Lineflow\Data Feeds\Data Feeds - Events.xlsx"), ReadOnly:=True
Workbooks.Open ("I:\H911 Events and Supply Chain\Lineflow\Events Database.xlsm")
Application.DisplayAlerts = True
Lineflow.activate

Application.ScreenUpdating = True

End Sub


Workbook.Activate Event

Private Sub Workbook_Activate()
Application.ScreenUpdating = False
'Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = Not Application.DisplayStatusBar
Application.ScreenUpdating = True
End Sub

Workbook.Deactivate Event

Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
End Sub