複数のラベル名を一括で複数のセルの値に書き換える方法

セル値をラベルに Excel-VBA
スポンサーリンク

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

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

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

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

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

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

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

ラベルキャプションをセルの値から一括取得

ボウリングの大会や予約でASで名前登録作業を行っていて、一人目の名前を入れると、空欄の部分に登録した名前が表示され、二人目を登録すると2番目に名前が表示されるのが当然ですが、エクセルでも似たようなことができます。

リーグ成績表でも使用している「選手登録フォーム」で【新規登録】ボタンを押すと入力した参加者名が表示されるという事です。

この【新規登録】ボタンに「コマンドボタン」と言いますが、次のような指示を書いています。

ユーザーフォームに配置した「会員番号」「選手名前」「AVE」を入力する枠(テキストボックス(TextBox))と「性別」を選択する(オプションボタン(OptionButton))に入力・選択された内容を指定したセルに転記して、さらに、名前「ラベル(Label)」といいますが、【新規登録】ボタンに自動的にあらかじめ指定したセルの値を取得して表示する

と、いう指示を書いています。

選手が誰も登録されていない状態はラベル(Label)はラベル番号で表示されています。

LTB選手登録

「会員番号」「選手名前」「AVE」と入力して「性別」を選択して、【新規登録】ボタンを押すと

ラベル一括変換後

登録済みの選手名に表示(ラベルキャプション)が一括で切り替わってくれます。

入力した名前が間違っていないかどうかが、入力後すぐに表示されているのでわざわざ、ワークシートのセルを探す必要が無くなるので時間の節約になりますね。

では、実際にはこのようなコードを記述しています
Private Sub CommandButton1_Click()
If UserForm1.TextBox1.Value = “” Or UserForm1.TextBox2.Value = “” Or UserForm1.TextBox3.Value = “” Then
MsgBox “会員番号・選手名前・AVEを入力してください”
Else
Range(“AB1”).Value = UserForm1.TextBox1.Value
Range(“AC1”).Value = UserForm1.TextBox2.Value
Range(“AE1”).Value = UserForm1.TextBox3.Value
If OptionButton1.Value = True Then
Range(“AD1”).Value = “1”
ElseIf OptionButton2.Value = True Then
Range(“AD1”).Value = “2”
End If
Range(“AB1”).End(xlDown).Offset(1).Value = Range(“AB1”).Value
Range(“AC1”).End(xlDown).Offset(1).Value = Range(“AC1”).Value
Range(“AD1”).End(xlDown).Offset(1).Value = Range(“AD1”).Value
Range(“AE1”).End(xlDown).Offset(1).Value = Range(“AE1”).Value
End If
For i = 44 To 83
With UserForm1.Controls(“Label” & i)
.Caption = Cells(i – 41, 29)
End With
Next i
For j = 84 To 123
With UserForm1.Controls(“Label” & j)
.Caption = Cells(j – 81, 31)
End With
Next j
UserForm1.TextBox1.Value = “”
UserForm1.TextBox2.Value = “”
UserForm1.TextBox3.Value = “”
End Sub
  1. 「コマンドボタン1をクリックしたら実行する」というマクロの記述を開始
  2. もしユーザーフォーム1のテキストボックス1が空欄または、ユーザーフォーム1のテキストボックス2が空欄または、ユーザーフォーム1のテキストボックス3が空欄だったら
  3. 「会員番号・選手名前・AVEを入力してください」とメッセージを表示する
  4. そうでなかったら(全て入力されていたら)以下のコードを実行する
  5. セル「AB1」にユーザーフォーム1のテキストボックス1の入力値を転記する
  6. セル「AC1」にユーザーフォーム1のテキストボックス2の入力値を転記する
  7. セル「AE1」にユーザーフォーム1のテキストボックス3の入力値を転記する
  8. もしオプションボタン1(男)が選択されていたら
  9. セル「AD1」に半角数字の「1」を転記する
  10. そうではなくもしオプションボタン2(女)が選択されていたら
  11. セル「AD1」に半角数字の「2」を転記する
  12. オプションボタンに関するIfステートメント終了
  13. セル「AB1」の入力済み最終行を見つけてその下の行にセル「AB1」の値を記述する
  14. セル「AC1」の入力済み最終行を見つけてその下の行にセル「AC1」の値を記述する
  15. セル「AD1」の入力済み最終行を見つけてその下の行にセル「AD1」の値を記述する
  16. セル「AE1」の入力済み最終行を見つけてその下の行にセル「AE1」の値を記述する
  17. テキストボックスが空欄でなかった場合のIfステートメント終了
  18. 繰り返し処理の変数「i」を宣言し、変数「i」は44~83とする(書き換えたいラベル番号)
  19. Withステートメント開始し、ユーザーフォーム1に配置された「ラベル番号「i」」の名前をセル(i-41行目の29列目)より順に取得して書き換える
  20. 名前の書き換えるWithステートメント終了
  21. 変数「i」を指定した範囲で繰り返す
  22. 繰り返し処理の変数「j」を宣言し、変数「j」は84~123とする(書き換えたいラベル番号)
  23. Withステートメント開始し、ユーザーフォーム1に配置された「ラベル番号「j」」の名前をセル(j-81行目の31列目)より順に取得して書き換える
  24. AVEの書き換えるWithステートメント終了
  25. 変数「j」を指定した範囲で繰り返す
  26. ユーザーフォーム1のテキストボックス1を空欄にする
  27. ユーザーフォーム1のテキストボックス2を空欄にする
  28. ユーザーフォーム1のテキストボックス3を空欄にする
  29. マクロの記述終了

変数ってのが、ややこしいいかな?

変数「i」は44~83になっているのは、参加選手名の1番~40番に相当するラベル番号が実は最初の図のように44~83なのです。

その為にラベルの名前を書き換えるのは当然44~83なので、ラベルの番号が変わるから「変数」なんです。

そして取得したいセルは見えないのですが、セルAB1には転記した名前が、セルAB2には「選手名」と記載しています。その為最初に入力された選手名は必ず、セル「AB3」に入力されるようになっています。

なぜならば、入力済み最終行を見つけてその下のセルに入力されるようになっているからです。

ということで、1番目の選手名はセル「AB3」なので3行目。

ということで、

選手番号1はラベル44でセルは3行目になり、44引く3は、-41となります。

また、AB列はAから数えると29番目の列なので列番号は29です。

これがCells(i-41,29)という意味です。

難しくないでしょ!ここでの説明で使用した参考ページで各ボタンの説明マニュアルを記載しています。

ボウリング成績表選手登録方法~LTB個人リーグ編
エクセルで作成しているボウリングリーグ成績表を自動化で、リーグ戦参加者を新規登録・修正・削除する専用フォームの使い方

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

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

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

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

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

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

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

コメント