aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

VBA
1Module1
2Option Explicit
3
4Dim newBookPath As String
5Public newBookName As String
6
7Sub 新規ブックの作成()
8
9    Dim newBook As Workbook
10    
11    '新しいファイルの名前を指定
12    newBookName = Format(Date, "yyyy年mm月dd日") & ".xlsx"
13    Sheets("Sheet2").Cells(2, 1) = newBookName
14    
15    '新しいファイルのフルパスを設定
16    newBookPath = ThisWorkbook.Path & "\" & newBookName
17    
18    '指定したパスにファイルが作成済でないかを確認。
19    If Dir(newBookPath) = "" Then
20        '新しいファイルを作成
21        Set newBook = Workbooks.Add
22        
23        '新しいファイルをVBAを実行したファイルと同じフォルダ保存
24        newBook.SaveAs newBookPath
25        
26    Else
27        '既に同名のファイルが存在する場合はメッセージを表示
28        MsgBox "既に" & newBookName & "というファイルは存在します。"
29        
30        End If
31        
32        ThisWorkbook.Sheets("Sheet2").Range("A2").Value = newBookName
33
34End Sub
35
36Sub 新規ブックのオープン()
37
38    Workbooks.Open Filename:=newBookPath, ReadOnly:=False
39    
40    Sheets("Sheet1").Range("A1") = UserForm1.TextBox1.Text
41
42End Sub

Sheet1
Option Explicit

Private Sub CommandButton1_Click()

    UserForm1.Show
    
End Sub

ThisWorkbook
Option Explicit

Private WithEvents 検出 As ブックが閉じられたことを検出

Private Sub Workbook_Open()

    Set 検出 = New ブックが閉じられたことを検出

End Sub

Public Sub 検出_WorkbookClosed()

    Dim 見つけた As Range
    
    Set 見つけた = ThisWorkbook.Sheets("Sheet2").Range("A1:G1").Find(What:=Sheets("Sheet2").Cells(2, 1).Value)
    If Not 見つけた Is Nothing Then
        UserForm1.Show
    End If

End Sub
コード

UserForm1
Option Explicit

Private Sub CommandButton1_Click()

    Call 新規ブックの作成
    
    Call 新規ブックのオープン
    
    Unload Me
    
End Sub

ブックが閉じられたことを検出
' option
Option Explicit

'************************************************************************
'*  event
'************************************************************************
Event WorkbookClosed()

'************************************************************************
'*  variable
'************************************************************************
Private WithEvents App As Application
Private cnt As Long
Private activeCount As Long

'************************************************************************
'*  class event
'************************************************************************
'*-----------------------------------------------------------------------
'*  constructor
'*-----------------------------------------------------------------------
Private Sub Class_Initialize()
    
    'initialize
    Set App = Application
    
    'save
    cnt = Workbooks.Count
    
    activeCount = 1
    
End Sub

'************************************************************************
'*  Application event
'************************************************************************
'*-----------------------------------------------------------------------
'*  NewWorkbook
'*-----------------------------------------------------------------------
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    
    'save book count
    cnt = Workbooks.Count
    ThisWorkbook.Sheets("Sheet2").Cells(1, activeCount) = Wb.Name
    activeCount = activeCount + 1
    
End Sub

'*-----------------------------------------------------------------------
'*  WorkbookOpen
'*-----------------------------------------------------------------------
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

    'save book count
    cnt = Workbooks.Count
    ThisWorkbook.Sheets("Sheet2").Cells(1, activeCount) = Wb.Name
    activeCount = activeCount + 1

End Sub


'*-----------------------------------------------------------------------
'*  WorkbookActivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    
    'check count
    If Workbooks.Count < cnt Then
        cnt = Workbooks.Count
        ThisWorkbook.Sheets("Sheet2").Cells(1, activeCount) = Wb.Name
        activeCount = activeCount + 1
        RaiseEvent WorkbookClosed
    End If
    
End Sub

'*-----------------------------------------------------------------------
'*  WorkbookDeactivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
    
    'check count
    If Workbooks.Count = 1 Then
        cnt = 0
        ThisWorkbook.Sheets("Sheet2").Cells(1, activeCount) = Wb.Name
        activeCount = activeCount + 1
        RaiseEvent WorkbookClosed
    End If
    
End Sub