SamT's suggestion works here.
In order to debug, paste the following (very similar) code into the ThisWorkbook code-module of PERSONAL.XLSB to replace any existing code:
Public WithEvents ThisApp As Application
 
Private Sub Workbook_Open()
    Set ThisApp = Me.Application
End Sub
 
 'This one is tested as written
Private Sub ThisApp_WorkbookOpen(ByVal Wb As Workbook)
     'Do stuff
     MsgBox "ThisAppWorkbookOpen"
End Sub
 

Sub ThisApp_SheetBeforeDoubleClick(ByVal Sht As Object, ByVal Target As Range, Cancel As Boolean)
'Stop
    If Sht.Parent Is ThisWorkbook Then Exit Sub
     
     'If all Workbook will have the same Root
     'If not Sht.Parent.Path = ??? Then exit Sub
     'If all have a similar name part
     'If not InStr(Sht.Parent.Name, NamePart), then Exit Sub
     'Do Stuff
    Dim MyRange As Range
    Dim IntersectRange As Range
     ''''Dim EndRow As Long
     '
     '
     '''''Application.ScreenUpdating = False
     '
     '''''EndRow = Range("D" & Rows.Count).End(xlUp).Row
     '''''Set MyRange = Range("G2:J" & EndRow)   'last row
    Set MyRange = Range("G2:G1000") 'last row
    'Stop
    Set IntersectRange = Intersect(Target, MyRange)
    On Error GoTo SkipIt
    If IntersectRange Is Nothing Then
        Exit Sub
    Else
         '
         '
         '
        Target = Format(Now, "ttttt")
        MsgBox " still need to format cells with ...custom macro hh:mm"
         '
         '''''Application.ScreenUpdating = True
    End If
    ActiveCell.Offset(, 1).Select
     '
     '
SkipIt:
    Exit Sub
     '
     
End Sub
Close Excel and click Yes to saving changes to Personal.xlsb.
The important part from a debugging point of view is the
MsgBox "ThisAppWorkbookOpen"
line; this should cause a message to appear when every/any workbook is opened, even when starting Excel.
If this doesn't happen report back here.

If it does happen - great.
So next, see two commented-out lines in the code:
'Stop
?
Enable them by taking out the apostrophe in both cases.
Now when you double-click a sheet, the vbe should appear with the yellow highlight on the Stop instruction. If it doesn't, you may, while adjusting the code, have come across a pop-up which asks 'This action will reset your project, proceed anyway?', where you have to click OK if you want your changes to take effect. This will do what it says on the tin. If that's happened, you just need manually to run the existing 
Private Sub Workbook_Open()
    Set ThisApp = Me.Application
End Sub
to get it going again.

That's enough to be getting on with - come back and tell us how you get on.