複数のテキストボックスの値を一括で複数のセルの値に書き換える方法

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

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

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

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

波乗りアヒル

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

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

実際に動いているエクセルは2009と2016です。

テキストボックスの値をセルに書き写す方法
1. テキストボックスが複数ある場合のセルへの一括転記方法
2. 変数iに当てはめる行番号とテキストボックス番号が異なる時の対処法
繰り返し処理を複数組み合わせると作れますよ
各コントロールまとめて転記シリーズ

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

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

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

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コードはこのようになっています。

ちょっと長いですが、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

コマンドボタン2をクリックしたら
シートの保護を解除
繰り返し処理変数iは1～40
セルi+2,49（AW列）の値はユーザーフォーム3のテキストボックス番号i
繰り返す（AW3～AW43とテキストボックス1～40）
繰り返し処理変数jは41～80
セルj-38,50（AX列）の値はユーザーフォーム3のテキストボックス番号j
繰り返す（AX3～AX43とテキストボックス41～80）
繰り返し処理変数kは3～42
セルk,42（AP列）の値はセルk,49の値とセルk,50の値を結合した値にする
繰り返す（AP3～AP43とAW3～AW43とAX3～AX43）
もしセルAZ1の値が1だったら
「レーン抽選が重複しています！確認して修正後、再度【抽選終了】ボタンを押してください」とメッセージを表示
そうではなくもし、セルBA1の値が1だったら
「レーン番号が設定範囲外です。」とメッセージを表示
ユーザーフォーム2で
繰り返し処理変数aは1～10
ユーザーフォーム2に配置したテキストボックスaの値はセルa+1,40（AO列）
繰り返す（AO3～AO43）
Withステートメント終了
ユーザーフォーム2のテキストボックス11の値はセルAO2の値を取得する
ユーザーフォーム3を閉じる
問題なければ
「レコードシートにデータ送信しました」とメッセージ表示
ユーザーフォーム3に配置したラベル59の文字はセルAU1の値を取得する
ユーザーフォーム3に配置したラベル61の文字はセルAW1の値を取得する
IFステートメント終了
シートの保護をする
マクロ記述終了

波乗りアヒル

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

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

VBAシートの保護したらマクロエラーになった時の対処法

さっきまで使えていたマクロがシートの保護を設定したらエラーになった経験ありませんか？でも慌てずに、コマンドボタンにプロテクトを書き加えれば解決します。

VBAメッセージボックスをセルの値で表示する方法

メッセージが文字だけじゃなくて、指定したセルの値と連動して変化してくれるとものすごく便利です。何週目の成績？など何がセルの値だと1週目、2週目と変動してメッセージを出してれると便利ですね。

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

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

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

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

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