複数テキストボックスの入力値を一括して各セルへ記載する方法

2019 3/13

2018.07.12 2019.03.13

テキストボックスの値をセルに書き写す方法

エクセルでの作業効率化の為にオリジナルの入力フォーム（ユーザーフォーム）を使ってデータの入力作業を行うようにテキストボックスを活用していると思います。

このテキストボックスに入力した数字や文字を、当然ながら指定したセルにコマンドボタンを押して転記していると思います。

VBAコードで書くとこんな感じですよね。

Private Sub CommandButton1_Click()
Range(“A1”).Value = UserForm1.TextBox1.Value
End Sub

「コマンドボタン１をクリックしたら実行する」というマクロの記述を開始
セルA1にユーザーフォーム1のテキストボックス１の入力された値を転記する
マクロの記述終了

これはテキストボックスが1つの時のVBAコードで、もし2つあったとしたら、2つ並べてVBAコードを書けば解決しますよね。

Private Sub CommandButton1_Click()
Range(“A1”).Value = UserForm1.TextBox1.Value
Range(“A2”).Value = UserForm1.TextBox2.Value
End Sub

「コマンドボタン１をクリックしたら実行する」というマクロの記述を開始
セルA1にユーザーフォーム1のテキストボックス１の入力された値を転記する
セルA2にユーザーフォーム1のテキストボックス2の入力された値を転記する
マクロの記述終了

これぐらいならば、何の問題もなくVBAコードを書いていると思います。

波乗りアヒル

このページにたどり着いたという事は、テキストボックスがたくさんあって、VBAコードをもっと楽に簡単にできなかと思ったからですよね。

テキストボックスが80個もあったら・・・

波乗りアヒル

こんな感じでいっぱいあったら、VBAコードとんでもなく書くのが面倒ですよね。

ちなみにこのユーザーフォームはボウリングのレーン抽選で使用している実際のものですが、説明するにはちょっと複雑なので、また後程応用編で！

この80個ものVBAコードを書き続けるのなんて面倒なので、そこで指定した回数繰り返し処理を行ってくれるFor～Nextステートメントを使います

テキストボックスが複数ある場合のセルへの一括転記方法

それでは、テキストボックスが複数ありまとめて一括処理してセルに転記させましょう。

Excel-VBAの書籍には、指定した回数だけ同じ処理を繰り返すにはFor～Nextステートメントを使うと記載されています。

例えば、テキストボックス１～10の値をまとめてセルA1～A10に転記するというVBAコードを書くと下記のようになります。

Sub 指定した回数繰り返し同じ処理をする()
Dim i As Integer
With UserForm1
For i = 1 To 10
Cells(i , 1).Value = UserForm1.Controls(“TextBox” & i).Value
Next i
End With
End Sub

「指定した回数繰り返し同じ処理をする」というマクロを記述する
整数型の変数iを宣言する
ユーザーフォーム2も一緒に
変数が1～10まで行う
セル（i,1）の値はユーザーフォーム1に配置したテキストボックスiの値とする
処理を繰り返す
Withステートメント終了
マクロ記述終了

ここで大事なのが、数字が変わる変数iです。

iには1～10までの値が入る事になるので、セル（i,1）はセルA1になり、テキストボックス1の値が転記されることになります。

次はセル（2,1）なのでA2でテキストボックス2・・・10となります。

実際に使っているエクセルでは、数字入力が基本なので、整数型を指定する1行
「Dim i As Integer」を省略しても問題なくマクロ動いています。

波乗りアヒル

セルの行番号とテキストボックス番号が異なる場合ありますよね。

その時の変数iの方法は？って思いませんでしたか？

変数iに当てはめる行番号とテキストボックス番号が異なる時の対処法

For～Nextステートメントを使ってまとめて処理する方法は分かったけど、必ずしも変数iに入れる行番号とテキストボックスの番号が必ず一致するとは限りません。

波乗りアヒル

と、いうより異なっていることの方が実際多いです。

そんな時は、テキストボックスの番号に合わせて変数iを指定しますので下記の条件で説明します。

テキストボックスの番号が1～10
転記したいセルはA11～A20

Sub 指定した回数繰り返し同じ処理をする()
With UserForm1
For i = 1 To 10
Cells(i +10, 1).Value = UserForm1.Controls(“TextBox” & i).Value
Next i
End With
End Sub

「指定した回数繰り返し同じ処理をする」というマクロを記述する
ユーザーフォーム1も一緒に
変数が1～10まで行う
セル（i+10,1）の値はユーザーフォーム1に配置したテキストボックスiの値とする
処理を繰り返す
Withステートメント終了
マクロ記述終了

波乗りアヒル

テキストボックス番号に合わせるので、セルi+10とすることは最初が

1+10なので11行目A11となり、可能になりました。

次の例は・・・

テキストボックスの番号が11～20
転記したいセルはA1～A10

Sub 指定した回数繰り返し同じ処理をする()
With UserForm1
For i = 1 To 10
Cells(i , 1).Value = UserForm1.Controls(“TextBox” & i+10).Value
Next i
End With
End Sub

「指定した回数繰り返し同じ処理をする」というマクロを記述する
ユーザーフォーム1も一緒に
変数が1～10まで行う
セル（i,1）の値はユーザーフォーム1に配置したテキストボックスi+10の値とする
処理を繰り返す
Withステートメント終了
マクロ記述終了

波乗りアヒル

今度はセルの番号に合わせたので、テキストボックスi+10とすることは最初が1+10なのでテキストボックス11となり、可能になりました。

変数iに「+」を加えればある程度対応できますが、「-」を使う事も可能で、時々やむを得ず使うこともあります。もちろん問題なくマクロは動いています。

繰り返し処理を複数組み合わせると作れますよ

最初に紹介した80個もあるユーザーフォーム、これはボウリングのレーン抽選用のユーザーフォームです。

【選手名表示】ボタンを押すと、登録済み選手が表示されてレーン抽選をしたら「レーン番号」「投球順」をテキストボックスに入力します。

全ての選手のレーン抽選が終わったら【抽選終了】ボタンを押せば80個のテキストボックスの値が、指定されたセルへそれぞれ一括転記されて、スコアー記入用紙（通称：レコードシート）が出来上がり、そのまま印刷もできます。

テキストボックスが80個も

このユーザーフォームはボウリングのリーグ戦での成績表エクセルファイルで使っています。

あわせて読みたい

ボウリング成績表選手登録方法～LTB個人リーグ編リーグ戦参加者を新規登録・修正・削除することが可能です。 VBA（マクロ）が無効の場合はユーザーフォームが使えません。必ず有効にして下さい。【選手登録方法～LTB...

波乗りアヒル

参考までにVBAコードはこのようになっています。

ちょっと長いですが、For～Next４つ合わせ！

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect
For i = 1 To 40
Cells(i + 2, 49).Value = UserForm3.Controls(“TextBox” & i).Value
Next i
For j = 41 To 80
Cells(j – 38, 50).Value = UserForm3.Controls(“TextBox” & j).Value
Next j
For k = 3 To 42
Cells(k, 42).Value = Cells(k, 49).Value & Cells(k, 50).Value
Next k
If Range(“AZ1”).Value = “1” Then
MsgBox “レーン抽選が重複しています！確認して修正後、再度【抽選終了】ボタンを押してください”
ElseIf Range(“BA1”).Value = “1” Then
MsgBox “レーン番号が設定範囲外です。”
With UserForm2
For a = 1 To 10
UserForm2.Controls(“TextBox” & a).Value = Cells(a + 1, 40).Value
Next a
End With
UserForm2.TextBox11.Value = Range(“AO2”).Value
Unload UserForm3
UserForm2.Show
Else
MsgBox “レコードシートにデータ送信しました”
UserForm3.Label59.Caption = Range(“AU1”).Value
UserForm3.Label61.Caption = Range(“AW1”).Value
End If
ActiveSheet.Protect
End Sub