スポンサーリンク

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

セル値をラベルに Excel-VBA

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

ボウリングの大会や予約で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)という意味です。

Excel-VBAもくじへ

コメント