ユーザーフォームのラベル名をセルの値に一括書き換えする方法

Loop繰り返し処理VBA ラベル

スポンサーリンク


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

ユーザーフォームのラベル名(Label)をセルの値に連動して

Captionを自由自在に変えたいと思ったことありませんか?

このユーザーフォームでラベル(Label)を使うのはごく自然で、テキストボックスの説明やタイトルに使ってることが多いと思います。

このラベル(Label)の名前はユーザーフォームのプロパティにあるCaptionで好きな名前を付けることができます。
スポンサーリンク


スポンサーリンク

Label1 CaptionにセルA1の値

例えば、ユーザーフォーム1のLabel1の名前(Caption)をセルA1の文字や数字に連動して表示を変えるには、下記のようにコードを記述すれば可能です。

UserForm1.Label1.Caption = Range(“A1”).Text

ふたつある場合は、Label1のCaptionはセルA1でLabel2のcaptionはセルA2の場合は、

UserForm1.Label1.Caption = Range(“A1”).Text
UserForm1.Label2.Caption = Range(“A2”).Text

単純にそれぞれコードを書けばいいだけです。

このように数が少なければ問題ないのですが、10個・20個・・・それ以上なんてなったら大変な作業になります。

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

まとめて一括処理できないかなぁ~?

For~Nextを使う

エクセルVBAで多く使われているFor~Nextですが、「指定した回数だけ処理を繰り返す」というものです。

「顧客情報入力フォーム」で、名前や住所など必要入力項目をテキストボックスに入れ、コマンドボタンでリストに転記した時に入力データが間違っていないか確認の為、ユーザーフォームに表示できるように住所録でも使っているます。

そうすると、必要項目の数だけ転記したいセルの値があり、それぞれラベルに表示させなくてはいけなくなります。

たくさんあったら大変なので、まとめて一括処理しましょう。

実際には下記のようなコードを書いています。

Private Sub CommandButton1_Click()
For i = 1 To 16
With UserForm1.Controls(“Label” & i )
.Caption = Cells(i, 1)
End With
Next i
End Sub

これが基本的に繰り返しセルの値を取得してラベルの名前を書き換えるというVBAコードになります。

  1. ユーザーフォームのコマンドボタン(CommandButton1)を押したら
  2. 数字が変わる部分を変数「i」として、変わる数字が1~16
  3. ユーザーフォーム(UserForm1)に設置されているコントロールのラベル(Label「i」も1~16)も一緒に変える
  4. ラベル名(Label Caption)はセル「i」行目の1列目
  5. この作業を「i」に書いてある1~16まで繰り返して行う
  6. マクロ終了

これでLabel1~Label16までセルA1~A16までの値を順番にラベル名(Caption)を一括書き換えてくれます。

VBAを学校などで教わっていないので、たったこれだけのコードにたどり着くのに1時間もかかっちゃった。

そしてこのままだと実際には使えない!

やっとできたので実際の住所録へ組み込んでみましたが、実際にはこのままでは使えません。

なぜなら、VBAコードの場合必ずLabel番号とセルの番号が一致していないとダメなのです。

表示を変えたいラベルが必ずしもLabel1とは限らず、実際の住所録で使うときもLabel17~Label64で、取得したいセルはR5~R52で「For~Next」の指定した回数処理を繰り返すは使えるのですが、変数「i」をどうしたらいいのか悪戦苦闘。

2時間格闘してやっとたどり着いた!

Private Sub CommandButton1_Click()
For i = 5 To 52
With UserForm1.Controls(“Label” & i + 12)
.Caption = Cells(i, 18)
End With
Next i
End Sub

変更した部分は3か所。

  • 変数「i」を取得したいセルに合わせて5行目から52行目に変更
  • 表示を代えたいラベルは17~64で変数「i」の最初が5なので12足したら17になるから+12を付け加えてみたら大成功
  • 取得したいセルはR列なのでR列はA列から数えると18に変更
波乗りアヒル
波乗りアヒル

気をつけて!確認して!エラー表示!

ラベルの数がそもそも繰り返し処理の数だけユーザーフォームに設置されていないとエラーになります。

その場合ラベルの数の範囲内で変数を指定すれば使いまわしも成功しています。

ちなみにコマンドボタンのキャプションを変更したい場合はLabelをCommandButtonに代えるだけで対応できますよ!

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

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

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

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

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

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

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