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

excel-vba Excel-VBA
スポンサーリンク

こんにちは、とりっぷぼうるです。

元々エクセルとは全く縁のない生活をしていましたが、人員不足などで少しでもデータ入力作業や集計作業を効率化するしか方法が無く、仕方がなくエクセルに取り組み始まました。

なぜならば、大きな会社ならば専用ソフトやクラウド上でデータ集計や管理を行うのでしょうけど、小さなところではそのようなお金もないのでエクセルで解決するしか方法が無いのですよね。

このような理由からエクセルに取り組み始めましたので、当然ながらエクセルの専門知識もなく、学校に通うこともできずにただ単にエクセルの書籍とにらめっこしながら取り組んでいます。

波乗りアヒル
波乗りアヒル

でも書籍のままだと実用的でないことが多く、VBAコードを組み合わせてみたら動いた!という実用例・体験談を書いています。

Excel-VBAの専門学校通学や・資格があるわけではありませんので、あくまでエラーにならずに動いてくれているVBAコードになりますので、掲載のVBAコードが同じように動く保証はできません。
実際に動いているエクセルは2009と2016です。

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

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

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

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

Private Sub CommandButton1_Click()
Range(“A1”).Value = UserForm1.TextBox1.Value
End Sub
  1. 「コマンドボタン1をクリックしたら実行する」というマクロの記述を開始
  2. セルA1にユーザーフォーム1のテキストボックス1の入力された値を転記する
  3. マクロの記述終了

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

Private Sub CommandButton1_Click()
Range(“A1”).Value = UserForm1.TextBox1.Value
Range(“A2”).Value = UserForm1.TextBox2.Value
End Sub
  1. 「コマンドボタン1をクリックしたら実行する」というマクロの記述を開始
  2. セルA1にユーザーフォーム1のテキストボックス1の入力された値を転記する
  3. セルA2にユーザーフォーム1のテキストボックス2の入力された値を転記する
  4. マクロの記述終了

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

波乗りアヒル
波乗りアヒル

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

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

波乗りアヒル
波乗りアヒル

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

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

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

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

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

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

例えば、テキストボックス1~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
  1. 「指定した回数繰り返し同じ処理をする」というマクロを記述する
  2. 整数型の変数iを宣言する
  3. ユーザーフォーム2も一緒に
  4. 変数が1~10まで行う
  5. セル(i,1)の値はユーザーフォーム1に配置したテキストボックスiの値とする
  6. 処理を繰り返す
  7. Withステートメント終了
  8. マクロ記述終了

ここで大事なのが、数字が変わる変数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. 「指定した回数繰り返し同じ処理をする」というマクロを記述する
  2. ユーザーフォーム1も一緒に
  3. 変数が1~10まで行う
  4. セル(i+10,1)の値はユーザーフォーム1に配置したテキストボックスiの値とする
  5. 処理を繰り返す
  6. Withステートメント終了
  7. マクロ記述終了
波乗りアヒル
波乗りアヒル

テキストボックス番号に合わせるので、セル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. 「指定した回数繰り返し同じ処理をする」というマクロを記述する
  2. ユーザーフォーム1も一緒に
  3. 変数が1~10まで行う
  4. セル(i,1)の値はユーザーフォーム1に配置したテキストボックスi+10の値とする
  5. 処理を繰り返す
  6. Withステートメント終了
  7. マクロ記述終了
波乗りアヒル
波乗りアヒル

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

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

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

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

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

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

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

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

波乗りアヒル
波乗りアヒル

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

ちょっと長いですが、For~Next4つ合わせ!

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
  1. コマンドボタン2をクリックしたら
  2. シートの保護を解除
  3. 繰り返し処理変数iは1~40
  4. セルi+2,49(AW列)の値はユーザーフォーム3のテキストボックス番号i
  5. 繰り返す(AW3~AW43とテキストボックス1~40)
  6. 繰り返し処理変数jは41~80
  7. セルj-38,50(AX列)の値はユーザーフォーム3のテキストボックス番号j
  8. 繰り返す(AX3~AX43とテキストボックス41~80)
  9. 繰り返し処理変数kは3~42
  10. セルk,42(AP列)の値はセルk,49の値とセルk,50の値を結合した値にする
  11. 繰り返す(AP3~AP43とAW3~AW43とAX3~AX43)
  12. もしセルAZ1の値が1だったら
  13.  「レーン抽選が重複しています!確認して修正後、再度【抽選終了】ボタンを押してください」とメッセージを表示
  14. そうではなくもし、セルBA1の値が1だったら
  15. 「レーン番号が設定範囲外です。」とメッセージを表示
  16. ユーザーフォーム2で
  17. 繰り返し処理変数aは1~10
  18. ユーザーフォーム2に配置したテキストボックスaの値はセルa+1,40(AO列)
  19. 繰り返す(AO3~AO43)
  20. Withステートメント終了
  21. ユーザーフォーム2のテキストボックス11の値はセルAO2の値を取得する
  22. ユーザーフォーム3を閉じる
  23. 問題なければ
  24. 「レコードシートにデータ送信しました」とメッセージ表示
  25. ユーザーフォーム3に配置したラベル59の文字はセルAU1の値を取得する
  26. ユーザーフォーム3に配置したラベル61の文字はセルAW1の値を取得する
  27. IFステートメント終了
  28. シートの保護をする
  29. マクロ記述終了
波乗りアヒル
波乗りアヒル

長かったでしょう。ここで見落としがちなのが、シートの保護をかけている場合は最初に保護解除して最後に保護を書ける事を忘れると、セルへの転記する時にマクロエラー表示が出ますよ!

このサンプルにも使用しているVBAです

また、いいサンプルができたら追記します!

各コントロールまとめて転記シリーズ

ユーザーフォームを使うと配置したコントロール(テキストボックスやラベルなど)とセルとの間で値のやり取りが多くなります。

よく使うのでまとめてみました。

値の場所転記したい場所説明ページ
セルの値ラベル名VBAコード
セルの値テキストボックスの値VBAコード
セルの値コマンドボタンの表示名VBAコード
セルの値セルの値VBAコード
テキストボックス入力値セルの値VBAコード
波乗りアヒル
波乗りアヒル

成績表作成には知っておきたいExcel-VBAもくじ

ボウリングハンデキャップのつけ方
HDCPの種類はリーグのルールにより複雑に ボウリングの成績表(リーグ戦)で必ず必要になるのが、ハンデキャップ(HDCP)です。 ボウリングご予約の時に一番多く使われているのが、女性にだけハンデキャップをつけてあげることが多いですね。 だいだい何点ぐらいのハンデキャップが多いの?
ボウリングリーグ対戦表の数字を名前に自動変換する方法
リーグ戦の対戦表をエクセルで作る時、数字で表されている番号を名前に自動的に変換できると作業時間が短くなり効率的ですね。たった2つのエクセル関数を使うだけで、簡単にできるんです。今回の回戦数を入力するだけで一発変換完了。もちろんお客様にスケジュール対戦表として渡すことも簡単ですよ。
人数に応じてリーグ対戦表を自動選択する方法
複数の表があり、条件に合った表を選んで、さらにセルの値を選んで数字を名前に自動変換できると、とても便利なエクセルができますよね。ボウリングのリーグ対戦表も人数により表が異なるので、参加人数が決まったら、自動的に表を選んで対戦番号がこれも自動的に参加者名に変換されると便利で作業効率がアップして時間短縮になりますよ。
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

コメント