この記事で紹介するノウハウは３つ

ExcelVBAで、大量のユーザーフォームの部品をループ文で動的に生成する。また、これを指定のFrameの中に作る。
その指定のFrameの中だけ、マウスホイールでスクロールさせる。
生成した部品の「クリック時のイベント動作」もループ文で動的に定義する。

この記事のコードを搭載したサンプルファイルはこちらです。
https://github.com/shibahead/garage/blob/main/Qiita%E6%8A%95%E7%A8%BF%E7%94%A8_%E5%8B%95%E7%9A%84UI.xlsm

主に参考にさせていただいたURLはこちらです。ありがとうございます。
https://ateitexe.com/excel-vba-add-userform-control/
https://qiita.com/daik/items/9dd0f81613d62214072b
本記事の新規性は、上記URLの内容をベースとしつつ、
部品をFrame内に生成する・Frameスクロール高さ調整・Frame内をマウスでスクロール等の、
より実務的な部分となります。

この記事の背景

ExcelVBAのユーザーフォームで
コントロール（ボタン、チェックボックス、ラベル等の部品）を大量に作る場合、
・大量の部品を編集する
・大量の「クリック時のイベント」を書く
などがとても手間なので、
これらをループ文で（動的に）生成して、さらにクリック時のイベント動作もつける
ということをやりました。

ノウハウ１．ユーザーフォームの部品をループ文で大量に作る

手順1/2 入れ物となるユーザーフォームを作成する

普通のユーザーフォームの追加から、以下のようなユーザーフォームを作ります。

生成した部品を入れるFrameを作っておく。
FrameのスクロールバーはONにしておく（ユーザフォームのプロパティで設定）。
OKやCancelボタンはお好みで。

手順1/2 ループ文で部品を生成する

以下のコードを実行すると、手順1で作ったUserForm1のFrame1の中に、ボタンを100個生成できます。

Sub ユーザーフォームボタンの動的な生成1()
    'ラベルを作る個数
    max_num = 100

    'ボタンを動的に作る（Frame1の中に）
    For i = 1 To max_num
        Set newButton = UserForm1.Frame1.Add("Forms.CommandButton.1", "Button" & i)
        With newButton
        'ラベルの詳細を設定
            .Width = 100
            .Height = 20
            .Left = 10
            .Top = (i - 1) * 25 + 10
            .Caption = "ボタン" & i & "番目"
        End With
    Next

    'FRAMEのスクロール量を調節する
    UserForm1.Frame1.ScrollHeight = i * 25

    'ユーザーフォーム表示
    UserForm1.Show
    
End Sub

UserForm1.Frame1.Add("Forms.CommandButton.1", "Button" & i) の部分でCommandButtonを動的に追加しています。
この中のFrame1をControlsにすると、ユーザーフォーム直下にボタンが追加されます。
CommandButton.1を変えるとボタン以外の部品も色々と追加できます。詳しくはググってください。
単純にボタンを100個作ると全部同じ位置に重なってしまうので、.Topで位置をずらす必要があります。
また位置をずらしても、Frameのスクロール高さを調整しないとスクロールできないので、UserForm1.Frame1.ScrollHeightでループ回数に応じてスクロール高さを変更しています。

コードを実行して表示されるユーザーフォームはこちら。

これでボタン自体は追加できましたが、ここでさらにやりたい事が出てきます。

Frameの中のスクロール量が多いので、マウスホイールでスクロールできるようにしたい。（VBAの標準では、ユーザーフォームはマウスホイールに対応していません）
それぞれのボタンをクリックしたときに、何か起きるようにしたい＝イベントを定義したい

ということで、以下でこれらの機能を追加していきます。

ノウハウ２．Frameの中をマウスホイールでスクロールさせる

ここではFrameの中をマウスホイールでスクロールできるようにするノウハウを説明しますが、
「別にマウスホイールが効かなくても良い」という場合は無視してください。

ユーザーフォームをマウスホイールに対応させるコードはネット上に色々とありますが、
2022年11月現在探したところ、以下のURLのコードが一番使いやすいように感じました。
vbmodelessに対応していませんが、Frameの中も対応できています。
https://github.com/cristianbuse/VBA-UserForm-MouseScroll

インストール方法は、以下2つのファイルをimportせよとあるのですが、

自分の環境ではimport時にエラーが出たため、
下記のサンプルファイルをダウンロードして、そこからVBE上でのドラッグ＆ドロップでimportしました。
https://github.com/cristianbuse/VBA-UserForm-MouseScroll/blob/master/VBA%20UserForm%20MouseScroll_DEMO.xlsm
このようにMouseScrollとMouseOverControlが設置できれば成功です。

あとはモジュール内のUserForm1.Showの前後に以下のように追記すれば、
ユーザーフォームを表示した際にマウスホイールが効くようになります。

'マウススクロールを有効化
EnableMouseScroll UserForm1
'ユーザーフォーム表示
UserForm1.Show
'マウススクロール終了
DisableMouseScroll UserForm1

ノウハウ３．ボタンクリック時イベントも、ループ文で動的に定義する

最後に、上記で生成した大量のボタンそれぞれに、クリック時のイベントの定義します。
普通だとボタンが１００個あればCommandButton1_Click()~CommandButton100_Click()と
大量のコードを書くことになりますが、ループ文で書けば簡単です。

手順1/2 イベントを定義するクラスモジュールを追加する

まず、クリック時のイベントを定義するクラスモジュールをclsEventという名前で作成します。
CommandButtonのところを変更すれば、他の部品にも使えます。
またpButton_Click()のClickを他のイベント名にすれば、他のイベントにも対応できます。

'クリックのイベントを発生させるクラス
Public WithEvents Button As MSForms.CommandButton

'クリック時に動作するイベントプロシージャ
Private Sub Button_Click()
    MsgBox Button.Caption & "が押されました", vbInformation
End Sub

このようにクラスモジュールに追加できればOKです。

手順2/2 生成したボタンをイベントクラスを紐づける

ボタンにイベントを紐づけるために、標準モジュールのボタンを生成した後の部分に、下記のコードを追加します。
手順１で定義したイベントクラスをボタンの数だけ用意して、それぞれ紐づけを行うという流れです。
この部分はボタン生成のループの中で行っても良いですが、わかりやすさのために別ループとしました。

    'ボタンクリック時のイベントクラスを生成
    Dim ctrl() As New clsEvent
    ReDim ctrl(1 To max_num)
    'ボタンとイベントクラスを紐づける
    For i = 1 To max_num
        Set ctrl(i) = New clsEvent
        Set ctrl(i).Button = UserForm1.Controls("Button" & i)
    Next

まとめ

イベント追加も含めたメイン部分のコード全体は以下のようになります。

Sub ユーザーフォームボタンの動的な生成2()
    'ラベルを作る個数
    max_num = 100

    'ラベルを動的に作る
    For i = 1 To max_num
        Set newButton = UserForm1.Frame1.Add("Forms.CommandButton.1", "Button" & i)
        With newButton
        'ラベルの詳細を設定
            .Width = 100
            .Height = 20
            .Left = 10
            .Top = (i - 1) * 25 + 10
            .Caption = "ボタン" & i & "番目"
        End With
    Next
    
    'ボタンクリック時のイベントクラスを生成
    Dim ctrl() As New clsEvent
    ReDim ctrl(1 To max_num)
    'ボタンとイベントクラスを紐づける
    For i = 1 To max_num
        Set ctrl(i) = New clsEvent
        Set ctrl(i).Button = UserForm1.Controls("Button" & i)
    Next
    
    'FRAMEの高さを調節する
    UserForm1.Frame1.ScrollHeight = i * 25
    
    'マウススクロールを有効化
    EnableMouseScroll UserForm1
    'ユーザーフォーム表示
    UserForm1.Show
    'マウススクロール終了
    DisableMouseScroll UserForm1
    
End Sub

VBA全体の構成は以下のようになります。

メインのコード
入れ物となるユーザーフォーム
マウススクロールのためのクラスと標準モジュール
クリック時のイベントのためのクラス

以上により、下記のような機能を持ったユーザーフォームを
ループ文で動的に生成することができました。

応用例

上記の応用例として、
任意の数のキーワードに対して、複数の画像を検索し表示したり、リンクをクリックするとChromeで表示したり、といったような複雑なユーザーフォームを、ループ文で動的に作ることができます。

おわり

以上です。ユーザーフォームの動的な生成は敷居が高いですが、便利といえば便利なので、皆さんもチャレンジしてみてください。
お疲れ様でした。

【ExcelVBA】ユーザーフォームの部品をループ文で（動的に）に作る時のノウハウ