Workbook_Open Event

[sspatriots]

Hi,

This is similar to a previous post, except it is a question on opening several files as ReadOnly when I open the workbook. I open all four of the files below when I launch this one workbook using the "Workbook_Open" event. The problem is one of these files stops the code with a message that says:

'The author would like you to open 'Production Schedule.xlsm' as read-only unless you need to make changes. Open as read-only?

I realize this is a setting on that file, but if my code below is telling it to open as "ReadOnly" why would it ask me that question anyway. I'd like to prevent that from popping up as I'm opening this file. I tried turning off ".Displayalerts" before this line in the code, but the message still pops up. Any thoughts on how to prevent this without telling the owner of the other file to turn that recommendation off would be greatly appreciated.


Thanks, SS

    file_path1 = "H:\Jobs\PO Block History.xlsm"
    file_path2 = "G:\Manufacturing\Manufacturing Detail Schedule1.xlsx"
    file_path3 = "H:\Shop Files\MRL Production Schedule\Production Schedule.xlsm"
    file_path4 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm"

    Set my_wb1 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True)
    Set my_wb2 = Workbooks.Open(Filename:=file_path2, ReadOnly:=True)
    Set my_wb3 = Workbooks.Open(Filename:=file_path3, ReadOnly:=True)
    Set my_wb4 = Workbooks.Open(Filename:=file_path4, ReadOnly:=True)

 

[rollis13]

All you need to do is add parameter IgnoreReadOnlyRecommended:=True to the Workbooks.Open function.

 

[Jeffrey Mahoney]

That is strange. I tried to duplicate your issue. Mine opened with no warning at all

 

[rollis13]

@Jeffrey Mahoney, I had this problem too, but I noticed that while testing with the new parameter my Excel somehow messed up. I had to restart my PC to get the message to pop up again.

 

[sspatriots]

All you need to do is add parameter IgnoreReadOnlyRecommended:=True to the Workbooks.Open function.

Thank you. That did the trick

 

[sspatriots]

I have one more question that is relative to this same event. My entire event is shown below. When the workbook opens, it does everything as expected now, except at the end the original workbook that is running the event is not the one that is open on my screen. One of the other 4 are showing on my screen every time. I've tried stuff like delcaring the workbook as a public constant and using the following line at the end of my code, but it still doesn't return to this file on my screen.

    Windows(CommG2JL_WindowID).Activate

My full workbook open event:

Private Sub Workbook_Open()
    
    Dim my_wb1 As Workbook
    Dim my_wb2 As Workbook
    Dim my_wb3 As Workbook
    Dim my_wb4 As Workbook
    
    Dim file_path1 As String
    Dim file_path2 As String
    Dim file_path3 As String
    Dim file_path4 As String
    
    Dim lo As ListObject
    
    
    With Application
'        .EnableEvents = False
        .ScreenUpdating = False
'        .CutCopyMode = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
        
    Pleasewait.Show vbModeless
    
    
    Sheets("LogDetails").Visible = xlSheetVeryHidden   'ADDED, SPS, 01/17/23
    
    'Set reference to the first Table on the sheet
    Set lo = Worksheets("Jobs").ListObjects(1)
  
    'Clear All Filters for entire Table
    lo.AutoFilter.ShowAllData
    
    RefreshAllData
    
   
    ResetAllFormulas
    
    CFResetAll
    
    
    file_path1 = "H:\Jobs\PO Block History.xlsm"
    file_path2 = "G:\Manufacturing Detail Schedule1.xlsx"
    file_path3 = "H:\Shop Files\MRL Production Schedule\Production Schedule.xlsm"
    file_path4 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm"

    Set my_wb1 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    Set my_wb2 = Workbooks.Open(Filename:=file_path2, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    Set my_wb3 = Workbooks.Open(Filename:=file_path3, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    Set my_wb4 = Workbooks.Open(Filename:=file_path4, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    

    With Application
'        .EnableEvents = True
        .ScreenUpdating = True
'        .CutCopyMode = False
        .DisplayAlerts = True
        .Calculation = xlAutomatic
    End With


    Unload Pleasewait

End Sub

 

[Jeffrey Mahoney]

Try adding this in the workbook open event at the bottom
ThisWorkbook.activate

 

[sspatriots]

Try adding this in the workbook open event at the bottom
ThisWorkbook.activate

I tried that right at the end of that code, but it still ends up with one of the other 4 files being open on my screen.

 

[sspatriots]

When I add it to the end of my code after the "Unload Pleasewait" line and before the "End Sub" it seems to work. However, all the workbooks that are opening are flashing on the screen as they open. I would think that with the "ScreenUpdating" set to false in the beginning that it would not do this.

 

[sspatriots]

I inserted another "ScreenUpdating = False" just before I open those read only workbooks and then added the ActiveWindow.Visible False statements after each workbook and that seems to work.

Can someone tell me what the difference is between Visible being False and actually having the files just minimized? Just wondering what I could put there to just minimize them instead because I don't see a way to access the read only files if I wanted to go down to my task bar and just look at one of them.

Well I ended up with this for the bottom portion of my code:

    file_path1 = "H:\Jobs\PO Block History.xlsm"
    file_path2 = "G:\Manufacturing\Manufacturing Detail Schedule1.xlsx"
    file_path3 = "H:\Shop Files\MRL Production Schedule\Production Schedule.xlsm"
    file_path4 = "H:\Jobs\00 ENGINEERING DATA\Job List.xlsm"

    Application.ScreenUpdating = False

    Set my_wb1 = Workbooks.Open(Filename:=file_path1, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    ActiveWindow.Visible = False
    Set my_wb2 = Workbooks.Open(Filename:=file_path2, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    ActiveWindow.Visible = False
    Set my_wb3 = Workbooks.Open(Filename:=file_path3, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    ActiveWindow.Visible = False
    Set my_wb4 = Workbooks.Open(Filename:=file_path4, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    ActiveWindow.Visible = False
    

    With Application
'        .EnableEvents = True
        .ScreenUpdating = True
'        .CutCopyMode = False
        .DisplayAlerts = True
        .Calculation = xlAutomatic
    End With


    Unload Pleasewait

    ThisWorkbook.Activate

End Sub