ユーザフォーム

Public Function doModal()
Me.Show
End Function

Private Sub Cancel_btn_Click()
Unload Me
End Sub

Private Sub OK_Btn_Click()

Dim PathName As String
Dim FileName As String
Dim pos As Long

pos = InStrRev(Me.TextBox1.Text, "\")
PathName = Left(Me.TextBox1.Text, pos)
FileName = Mid(Me.TextBox1.Text, pos + 1)

List(0) = Me.TextBox1.Text
List(1) = PathName
List(2) = FileName
List(3) = Me.ComboBox1.Text
Unload Me

End Sub

Private Sub 参照1_btn_Click()

Dim OpenFileName As Variant
Dim mySheet As Worksheet
Dim Local_List() As String: ReDim Local_List(0)
Dim cnt As Integer: cnt = 0

ChDir ActiveWorkbook.Path


OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")

If OpenFileName <> False Then
Me.TextBox1.Text = OpenFileName

Workbooks.Open FileName:=OpenFileName, ReadOnly:=True
For Each mySheet In Worksheets '---(1)
Local_List(cnt) = mySheet.Name
cnt = cnt + 1
ReDim Preserve Local_List(cnt)
Next
ReDim Preserve Local_List(cnt - 1)
Me.ComboBox1.List = Local_List

ActiveWorkbook.Close SaveChanges:=False

End If

End Sub

＝＝＝＝＝

Sub main()

UserForm1.doModal
UserForm1.Hide
End Sub