Excel VBAのユーザーフォームから入力した値をテーブルに追加する方法

ユーザーフォームから入力した値をシートにあるテーブルに追加する方法です。
Accessではよくやる作業だと思いますが、簡易版をエクセルで作ってみました。

仕様は次の通りです。

テーブルのデータ追加はユーザーフォームのみできる（シートには保護をかけて編集できないようにする）
空のデータは追加できないようにする
既に入力されているデータは追加できないようにする。

このような仕様にすることで、意図しないデータの編集を防ぐことができる。
データの保全性を高めることができ、小規模なデータ管理などの活用が考えられる。

ユーザーフォーム（商品登録フォーム）で商品名と価格を入力したら、商品のマスターのテーブルにフォームに入力した情報を追加する方法のメモです。

仕様の詳細
テーブルの作成
フォームを呼び出すボタンの作成
ユーザーフォームの作成
Subプロシージャの作成

仕様の詳細

テーブル

テーブルは「製品ID」と「製品名」と「価格（円）」の３項目で構成されている。

ユーザーフォームを表示させるボタンは、項目の横に設定している。

テーブルの概要を知りたい方は下記を参照

Excelのテーブルを使うメリット｜圧倒的な効率化

ユーザーフォーム

ユーザーフォーム（商品登録フォーム）では商品名と価格を入力できる。
価格は数字しか入力できない。

商品名と価格はどちらも必須入力とし、両方もしくはどちらか一方が入力されていなければ、「商品名 and/or 商品価格を入力してください。」というエラーメッセージが表示され登録ができない。

また、商品名がテーブルに存在している場合は、「〇〇〇は登録済なので登録できません。」というエラーメッセージが表示され登録ができない。

テーブルへのデータ追加

ユーザーフォームに商品名と価格を入力して登録ボタンをクリックすると入力したデータがテーブルの最終行に追加される。

キャンセルボタンをクリックするとユーザーフォームを閉じる。

テーブルの作成

シートの先頭行に「製品ID」「製品名」「価格(円)」と入力して、入力したセルを選択した状態で「挿入」→「テーブル」→「テーブル」をクリック。

「先頭行をテーブルの見出しとして使用する」にチェックを入れて「OK」ボタンをクリック

データが空のテーブルができる。
テーブル名は必要があればテーブルデザインから変更する。今回はデフォルトの「テーブル1」のままにします。

1行だけデータを登録する。

A2セルに「1」と入力する。

フォームを呼び出すボタンの作成

注１）「開発」タブが表示されていないときは下記の手順で開発タブを表示させる。

　①「ファイル」→「オプション」。
　②「リボンのユーザー設定」→ 「メインタブの下の [開発]」チェックボックスをオン。

注２）VBAを使用するためにファイルは「xlsm」形式で保存する。

メニューから「開発」→「コントロール」→「挿入」→「フォームコントロール」→「ボタン」を選択

シートの上でボタンを作りたいところを選択したら下記が表示されるので「新規作成」ボタンをクリックする。

下図のように「ボタン１」の作成される。

VBEの標準モジュールのModule1の中に空の「ボタン１_Click()」の関数が作られる。
ここには、この後に作成するユーザーフォームの呼出しのコードを記載する。

分かりにくいので「ボタン１」→「入力フォームの表示」に変更する。

ボタンを右クリック →「テキストの編集」で表示名の変更ができる。
ボタンの移動やサイズの変更は「デザインモード」でできる。

下図のようにボタンの表示が変更される。

ユーザーフォームの作成

VBE（Visual Basic Editor）は「開発」→「Visual Basic」から起動できる。

「VBAProject」 →「挿入」→「ユーザーフォーム」を選択する。

ユーザーフォームができる。

必要な部品を下の「ツールボックス」の「コントロール」から選んで配置していく。

今回使うのは「ラベル」「テキストボックス」「コマンドボタン」の３つである。

要素は下記のようになる。

Label1：「商品名」と表示する
Label2：「価格（円）」と表示する
Label3：初期状態では空白とし、エラーがあればエラーメッセージが表示される
TextBox1：商品名を入力する
TextBox2：価格(円)を入力する
CommandButton1：クリックすると登録の処理を実行する
CommandButton2：クリックするとユーザーフォームを閉じる。

ラベルやコマンドボタンは選択すると、表示を訂正することができる。

コマンドボタンはクリックしたときにプログラムを実行させるため、ダブルクリックし空のSubプロシージャーを作っておく。

ユーザーフォームのプロパティ設定後に、ここにプログラムを入力することになる。

コントロールでプロパティ設定は、価格(円)のテキストボックス（TextBox2）とユーザーフォーム（UserFrom1）の２か所で行う。

TextBox2 Text Box

プロジェクトの下のプロパティのドロップボックスの「TextBox2 Text Box」を選択する。

「IME Mode」を「3-fmIME ModeDisable」に変更する。
IMEをオフにすることができる。さらに半角の数字のみを入力するためのプログラムを後で設定する。

UserFrom1

プロジェクトの下のプロパティのドロップボックスの「UserFrom1 UserFrom」を選択する。

Captionの「UserFrom1」を「商品登録フォーム」に変更する。

フォームのタイトルが「UserFrom1」を「商品登録フォーム」に変更される。

Subプロシージャの作成

VBAの中身を作成する。作成するプログラムは４つ。

入力フォームの表示ボタンをクリックたときに実行されるプログラム
価格(円)の入力時に実行されるプログラム
入力フォーム内のキャンセルボタンをクリックしたときに実行されるプログラム
入力フォーム内の登録ボタンをクリックしたときに実行されるプログラム

入力フォームの表示ボタンをクリックしたときのプログラム

「VBAProject」→「標準モジュール」→「Module1」を選択すると「ボタン1_Click()」のSubプロシージャがあるので、その中に、「UserForm1」を起動させる下記のプログラムを記載する。表示させるには「.Show」を使う。

Sub ボタン1_Click()
    '入力フォームの表示
    UserForm1.Show

End Sub

これで、シートの「入力フォームの表示」をクリックすると、作成したユーザーフォームが表示されるようになる。

価格(円)の入力時に実行されるプログラム

「VBAProject」→「フォーム」→「UserFrom1」を右クリックし「コードの表示」を選択する

ここの最下段に下のコードをコピー＆ペーストする。

'数字以外入力不可の設定
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If Not Chr(KeyAscii) Like "[0-9]" Then
        KeyAscii = 0
    End If
End Sub

このコードはTextBox2（価格(円)のテキストボックス）に半角の数字以外のキーが押されたら何も入力されないというプログラムです。

入力フォーム内のキャンセルボタンをクリックしたときに実行されるプログラム

前項と同じUserForm１の「コードの表示」させたところにプログラムを書く。

キャンセルのボタンをクリックしているので下記が自動で作成されてる。

Private Sub CommandButton2_Click()

End Sub

下記を上記の真ん中のスペースにコピー＆ペーストする。

Unload UserForm1

もし、上記のボックスの表示がなければ、下記をコピー＆ペーストする。

'ユーザーフォームを閉じる
Private Sub CommandButton2_Click()

    Unload UserForm1

End Sub

入力フォーム内の登録ボタンをクリックしたときに実行されるプログラム

前項と同じUserForm１の「コードの表示」させたところにプログラムを書く。

登録のボタンをクリックしているので下記が自動で作成されてる。

Private Sub CommandButton1_Click()

End Sub

上記の部分を下記で上書きする。

'登録ボタンがクリックされた時の処理
Private Sub CommandButton1_Click()

    '変数の宣言
    Dim RowNunber As Long   '登録しようとした製品がある行数を登録する
    Dim TableName As String     'テーブル名
    
    '変数の初期化
    RowNunber = 0
    TableName = "テーブル1"
    
     '入力した商品名がテーブルにあるかチェックする
    For i = 1 To ActiveSheet.ListObjects(TableName).Range.Rows.Count
        'データがある場合
        If ActiveSheet.ListObjects(TableName).Range(i, 2) = TextBox1.Value Then
            Rownumber = i   '商品名が見つかった時はテーブルの行数を保存
            Exit For 'ループを終了
        End If
    Next



    If TextBox1.Value = "" Or TextBox2.Value = "" Then        '必須項目が入力されていない時の処置
    
        Label3.Caption = "商品名 and/or 商品価格 を入力してください。"
        
        
    ElseIf Rownumber <> 0 Then  '既に商品が登録されている場合の処置
    
        Label3.Caption = TextBox1.Value & "は登録済なので登録できません。"
    
    
    Else    '必須項目が入力されているときの処置
        'シートの保護を解除
        ActiveSheet.Unprotect

        
        With ActiveSheet.ListObjects(TableName)
            '行を追加
            .ListRows.Add
    
            '最終行に値を入力
　　　　　　 .Range(.Range.Rows.Count, 1) = .Range(.Range.Rows.Count - 1, 1) + 1
            .Range(.Range.Rows.Count, 2) = TextBox1.Value
            .Range(.Range.Rows.Count, 3) = TextBox2.Value
        End With
        
        'シートの保護
        ActiveSheet.Protect
    
    End If

End Sub

このプログラムの概要は下記です。

まずは、入力された商品名がすでに登録されていないかを確認している。
その後、入力された内容によって、処理を３パターンに分けている。
１パターン目は、テキストボックスに空欄があったらLabel3にエラーメッセージを表示させる。
２パターン目は、商品が既に登録されていたらLabel3にエラーメッセージを表示させる。
３パターン目は、テーブルの最終行にユーザーフォームで入力した値を追加いている。
シートの保護された状態ではテーブルへの書き込みができないので保護を解除している。
その後、テーブルに行を追加し、追加した行に商品名と価格を追加している。
最後は再びシートに保護の処置をしている。

これで、最初に規定した仕様通りの商品マスターの台帳を作ることができた。