1.プロジェクトエクスプローラーに新規ユーザーフォームを追加する
VBE上にて、挿入→ユーザーフォーム、を選択
（ここでは、作成したユーザーフォームのオブジェクト名を「frm_テキスト入力」とする）

2.ユーザーフォーム上の任意の位置に、テキストボックス、コマンドボタンを配置する
VBE上にて表示→ツールボックスを選択することにより、各種コントロールが選択できる
（ここでは、テキストボックスのオブジェクト名を「TextBox」、コマンドボタンのオブジェクト名を「CommandButton」とする）

3.ユーザーフォームにイベントマクロのコードを記述する
ユーザーフォームの任意の位置で右クリック→コードの表示、を選択することにより、コード入力画面に切り替えることが出来る

Option Explicit

'CommandButtonがクリックされた時に実行するイベントマクロ
Private Sub CommandButton_Click()
    
    '入力されたテキストの出力先セルを指定する
    Dim outputCell As Range: Set outputCell = ThisWorkbook.Worksheets(1).Range("A1")
    
    'Me(frm_テキスト入力).TextBox.Textプロパティにて、テキストボックスに入力されたテキストを取得し、それを出力先セルに代入する
    outputCell.Value = Me.TextBox.Text
    
End Sub

4.ブックが開かれた際に自動でユーザーフォームが表示されるよう、Workbookモジュール上にイベントマクロを記述する

Option Explicit

'ブックが開かれた際に実行されるイベントマクロ
Private Sub Workbook_Open()

    'ユーザーフォームを表示する
    frm_テキスト入力.Show

End Sub