スポンサーリンク

複数のコマンドボタンのキャプションを一括で複数のセルの値に書き換える方法

セル値をボタンに Excel-VBA
スポンサーリンク

セルA1~A10の値をまとめてコマンドボタン1~10に転記表示する

ボウリング場のリーグ戦の成績表でも使っているエクセル。

リーグ戦参加者の成績表を入力する為の専用フォームに配置した、ネームプレート(コマンドボタン)の表示名(キャプション)をまとめて変換するために使っています。

このネームプレートを使ったボウリングスコアーを入力する方法は、「ボウリング個人スコアー入力方法~LTB個人リーグ編」に記載していますので、ここでは省略します。

ボウリング個人スコアー入力方法~LTB個人リーグ編
エクセルでビジュアルベーシック(VBA)を使わずに作成していると、入力したい参加者の名前を探すためにシートを選択したりスクロールしたり、とても大変で時間ばかり無駄にかかってしまい非効率です。個人のレコードシート!ユーザーフォームを使えば、瞬間移動できるのです。
では早速実用例です。

ボウリングのリーグ成績表で使っている実用例は、最初に「MENU」フォームから【選手選択】ボタンを押す。

ボウリングリーグ戦MENU~LTB編

すると、MENUフォームが閉じられて、選手選択フォームが開きます。

この時に、すでに入力されている参加選手名がセルから読み取られて、選手選択フォームに配置したコマンドボタンに転記されます。

下記がワークシートのセルに記載されている参加選手名リストです。
※画面のスペースの関係で本来最高40名登録できますが、13名のみ表示

このリストから名前を40名分読み取って(セルの値)コマンドボタンにそれぞれ転記

この場合コードを書いているのは「MENU」フォーム(ユーザーフォーム5)に配置した【選手選択】ボタン(コマンドボタン6)です
Private Sub CommandButton6_Click()
If UserForm5.TextBox1.Value = “” Then
MsgBox “スコアー入力する週が入力されていません”
Else
Range(“BE1”).Value = UserForm5.TextBox1.Value
For i = 1 To 40
With UserForm6.Controls(“CommandButton” & i)
.Caption = Cells(i + 2, 29)
End With
Next i
msg = MsgBox(Range(“BE1”) & “週目ですね”, Buttons:=vbYesNo + vbQuestion)
If msg = vbYes Then
Unload UserForm5
UserForm6.Show
End If
End If
End Sub

このコードは実際使っているコードなので、コマンドボタンの表示名をセルから取得して表示するコード以外も書いています。

赤い下線の部分だけで目的の一括書き換えができますのでこの部分のみコードの意味を書きます。

  1. 「コマンドボタン6をクリックしたら実行する」というマクロの記述を開始
  2. 繰り返し処理の変数「i」を宣言し、変数「i」は1~40とする(転記先のコマンドボタンの番号)
  3. Withステートメント開始し、ユーザーフォーム6に配置したコマンドボタン番号「i」(1~40)
  4. 名前(キャプション)をセルi+2行目(登録番号1番の名前が3行目にある為)の29列目(AC列)から取得して書き換える
  5. Withステートメント終了
  6. 指定回数(1~40)を繰り返して行う
  7. ユーザーフォーム5を閉じる
  8. ユーザーフォーム6を表示
  9. マクロ終了

これで名前を取得してから選手選択フォームを開くことができ、表示もセルから取得してくれます。

※コマンドボタン31~40は未登録なので空欄(キャプションなし)になります。

ここに書いてあるVBAコード全体については、「VBAメッセージボックスをセルの値で表示する方法」に全て記載していますのでここでは省略しました。

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

Excel-VBAもくじへ

コメント