Any events before Workbook_open event?

[bradyboyy88]

I am using the following code to basically catch any workbooks opened while my main workbook is opened. And it converts it to read only then closes it and starts it under its own instance of excel. It helps to keep my workbook seperate from all other ones. The problem is if the workbook being opened has the read only message option enabled then it asks "The author would like you to open "tracker.xls" as read only unless you need to make changes. Open as read-only?". I want to stop it from asking that until the workbook as been opened in its own instance. The issue is the App_WorkbookOpen(ByVal Wb As Workbook) event happens after the other workbooks message event happens. My code is set up as a class as shown below:

'Creating seperate instances using events
Public WithEvents App As Application




'Create a seperate instances for any excel files trying to open while this is open
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "test"
    Dim xlApp As Application


    If Wb Is ThisWorkbook Then
    
    Exit Sub
    
    End If
    
    If Wb.ReadOnly <> True Then
        Application.DisplayAlerts = False
        Wb.ChangeFileAccess xlReadOnly
        Application.DisplayAlerts = True
    End If
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open Wb.FullName
    xlApp.Visible = True
    Wb.Close False
    'Just for this program since it seems to leave window back open again
    ThisWorkbook.Application.Visible = False
End Sub

Thisworkbook

Public Sub Workbook_Open()
    
    On Error GoTo ErrorHandler
    'Speed up vba code
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    'Icon Update
    Call SetIcon(ThisWorkbook.Path & "\Images\LOGO.ico", 0)
    
    'Seperate instance classes and GUIClass    
    Public ExcelGUIUpdates As New ExcelGUIClass


    'Seperate Instance Declaration
    Set ExcelGUIUpdates.App = Application
    
    'Delete itself from history
    Call ExcelGUIUpdates.DeleteRecentlyOpened
    
Exit Sub


ErrorHandler:
Debug.Print "Error Number: " & Err.Number & ", Error Message: " & Err.Description & ", Last DLL Error: " & Err.LastDllError


End Sub

 

[Kamolga]

On data tab, edit links, startup prompt and select "don't display the alert and update links.

Otherwise through VBA, if you want to keep let the user the opportunity to refuse when he opens,

application.displayalerts=false

and put back to true at the end of the macro should do the trick.

 

[bradyboyy88]

Thanks for your solution but the Application.DisplayAlerts = False does not seem to work. I actually use that in the beginning of workbook_open in the thisworkbook module as you can see. So displayalerts has been set to false from the beginning from when i opened my main workbook.

 

[xenou]

just for clarity, there is a line of code that would set alerts back on:

        Application.DisplayAlerts = False
        Wb.ChangeFileAccess xlReadOnly
        Application.DisplayAlerts = True

 

[Jaafar Tribak]

Try this modification : (changes in blue)

'Creating seperate instances using eventsPublic WithEvents App As Application


'Create a seperate instances for any excel files trying to open while this is open
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "test"
    Dim xlApp As Application

    If Wb Is ThisWorkbook Then
    
    Exit Sub
    
    End If
    
    If Wb.ReadOnly <> True Then
        Application.DisplayAlerts = False
        Wb.ChangeFileAccess xlReadOnly
        Application.DisplayAlerts = True
    End If
    
    [COLOR=#0000ff][B]Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    xlApp.Workbooks.Open(Wb.FullName, IgnoreReadOnlyRecommended:=True).ChangeFileAccess xlReadOnly
    xlApp.Visible = True
    xlApp.DisplayAlerts = True[/B][/COLOR]
    Wb.Close False
    'Just for this program since it seems to leave window back open again
    ThisWorkbook.Application.Visible = False
End Sub