File to open hidden by macro only, but visible when opened through conventional means?

[kymmeeh]

Inside File 1, if the user types "NEW" into Column B...I want File 2 to open in the background. It pops open a user form to get information from the user. Works fine.

Whenever I want another user to edit File 2, upon opening, it is always hidden. How can I address that?

'Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B10:B500")) Is Nothing Then
    If Target.Rows.count > 1 Or Target.Columns.count > 1 Then
        'nothing
    Else
        If InStr(1, Target.Value, "New", 1) > 0 Then
            If CheckFileIsOpen("File 2.xlsm") = False Then
                'On Error Resume Next
                    Application.ScreenUpdating = False 
                    Application.DisplayAlerts = False
                    Workbooks.Open ("S:\File 2.xlsm")
                    Windows("File 2.xlsm").Visible = False
                'On Error GoTo 0
            End If
            NewRMsBox.Show
        End If
    End If   
End If

Here is the coding that is in File 1 that closes the hidden File 2.

'***Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
If CheckFileIsOpen("File 2.xlsm") = True Then
    On Error Resume Next
    Workbooks("S:\File 2.xlsm").Save
    Workbooks("S:\File 2.xlsm").Close
    On Error GoTo 0
End If

Application.DisplayAlerts = False
If ThisWorkbook.ReadOnly Then
    ThisWorkbook.Close savechanges:=False
End If
Application.DisplayAlerts = True

'End Sub

 

[Artik]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If CheckFileIsOpen("File 2.xlsm") Then
        On Error Resume Next
        Windows("File 2.xlsm").Visible = True
        Workbooks("S:\File 2.xlsm").Close True
        On Error GoTo 0
    End If

(...)

Artik

 

[kymmeeh]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    If CheckFileIsOpen("File 2.xlsm") Then
        On Error Resume Next
        Windows("File 2.xlsm").Visible = True
        Workbooks("S:\File 2.xlsm").Close True
        On Error GoTo 0
    End If

(...)

Artik

Thank you, Artik. I this in my File 1 Sub Workbook_BeforeClose, but after closing File 1, and reopening only File 2, it still opens as hidden.

I tried it adding the code to File 2 open Opening instead. This worked, but when opening File 2, now has two windows of Excel open, one blank (no file at all) and one with File 2. Are you able to help me modify this to make it work?

Private Sub Workbook_Open()
On Error Resume Next
Windows("File 2.xlsm").Visible = True

On Error GoTo 0

End Sub

 

[Artik]

I confess that I did not test the previous code with the Workbook_BeforeClose event procedure, but in a "regular" procedure written in the standard module. And there it worked correctly. However, I forgot that Workbook_BeforeClose has its quirks. This is just one of them.
Remove the Workbook_BeforeClose procedure from the ThisWorkbook module, and insert the Auto_Close procedure in the standard module (e.g. Module1). This is an old-style "event" procedure that is called when the workbook is closed.

Sub Auto_Close()
    If CheckFileIsOpen("File 2.xlsm") Then
        'On Error Resume Next
        Windows("File 2.xlsm").Visible = True
        Workbooks("File 2.xlsm").Close True
        'On Error GoTo 0
    End If

    Application.DisplayAlerts = False
    If ThisWorkbook.ReadOnly Then
        ThisWorkbook.Close savechanges:=False
    End If
    Application.DisplayAlerts = True

End Sub

Note the order in which the procedures are done. If for what reason you need to have both procedures: Workbook_BeforeClose and Auto_Close, then _BeforeClose will execute first, followed by Auto_Close.

Artik

 

[Artik]

Basically, part of the code does not make sense, so the code can be shortened to the form:

Sub Auto_Close()
    
    If CheckFileIsOpen("File2.xlsm") Then
        'On Error Resume Next
        Windows("File2.xlsm").Visible = True
        Workbooks("File2.xlsm").Close True
        'On Error GoTo 0
    End If

    If ThisWorkbook.ReadOnly Then
        ThisWorkbook.Saved = True
    End If

End Sub

Artik

 

[kymmeeh]

Thank you, Artik! New to the Workbook_beforeclose, so good to know. Appreciate it.