≫ Amazon Excel関連のおすすめ書籍へのリンク! ≫≫≫ Clickで詳細表示 ≫≫≫

VBA 取得したプロパティ値でユーザーフォームを複製してみた

VBA取得したプロパティー値でUserformを複製してみた

UserForm のコピーを作成する方法について勉強を継続しています。前回記事で UserForm についての全プロパティ値を取得することができるようになりました。
今回は、せっかく取得できたプロパティ値を使って、VBAで同じユーザーフォームの複製を作成する方法について勉強しましょう。

くるみこ
くるみこ

ワークシートに取得できたプロパティ値を使って UserForm を複製していきます。
今まで勉強してきたことを使っていけば何とかなりそうですね(^^)

わかりました。多分ユーザーフォームを動的に作成する方法
の応用ですね。よろしくお願いしますm(__)m

「ユーザーフォームを使う時だけ動的に作成する」記事がこちらです。

【この記事でわかることは】
・シートに取得済みのプロパティ値を使ってユーザーフォームを複製する方法
・フレーム内にコントロールを配置する方法

スポンサーリンク

UserForm と配置コントロールをVBAで複製します

この UserForm をVBAで複製していきます。

UserFormにプログレスバー用のレベルを配置

UserForm1 には Frame1、Label1、Label2、CommandButton1 が配置されています。
取得できたプロパティ値は下図のようにシートに保存されています。

プロパティー値取得実行後のシート画像(一部)

保存済設定データからユーザーフォームを作成するコード

さっそく次のように設定しました。

'取得データからユーザーフォームを作成する
Sub UserFormAdd()
    Dim Frm As VBIDE.VBComponent
    Dim Ctrl As MSForms.Control
    
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim arr As Variant  '配列用変数
    Dim r As Long, i As Long
    Dim x As Long, y As Long
    
    Set wb = ActiveWorkbook
    Set sh = wb.Worksheets("Ctrl_SetValue")     '設定値保存シート
    r = sh.Cells(Rows.Count, 1).End(xlUp).Row   'UserForm設定値の最終行取得
    ReDim arr(1 To r, 1 To 2)   '2次元配列初期化セット
    'シートに保存した設定値を取得する
    For y = 1 To r          '行数分のループ
        For x = 1 To 2      '列数分のループ
            arr(y, x) = sh.Cells(y, x)  'セルからプロパティ設定を取得
        Next x
    Next y
   'ユーザフォームを追加
    Set Frm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    On Error Resume Next    '設定できない場合エラーでストップしないように
    With Frm
        For i = 2 To r
            .Properties(arr(i, 1)) = arr(i, 2)  'UserFormのプロパティ値セット
        Next i
        'ここからは各コントロールを設置する処理
        Dim c As Long, j As Long
        Dim chk As Long, n As Long
        Dim ctrlName As String, cName As String
        'コントロールのデータ最終列取得
        c = sh.Cells(1, Columns.Count).End(xlToLeft).Column
        'プロパティー数取得(3列目から)
        r = sh.Cells(Rows.Count, 3).End(xlUp).Row
        ReDim arr(1 To r, 1 To 2)   '配列初期化
        For j = 4 To c  '3列目がプロパティ名なので4列目からが設定値
            'シートに保存した設定値を取得する
            For y = 1 To r
                arr(y, 1) = sh.Cells(y, 3)  'プロパティ名
                arr(y, 2) = sh.Cells(y, j)  'プロパティ値
            Next y
            ctrlName = arr(1, 2)            'コントロール名
            cName = "Forms." & ctrlName & ".1"
            'コントロール設置
            Set Ctrl = .Designer.Controls.Add(cName)
            With Ctrl
                For i = 2 To r
                    'コントロールのプロパティ値設定処理へ
                    Call setPVal(arr(i, 1), Ctrl, arr(i, 2))
                    '子コントロールがあるかどうかチェック
                    If i = r Then chk = arr(i, 2)   'chk = 子コントロール数
                Next i
                'フレーム又はマルチページの場合内部のコントロール配置へ
                If ctrlName = "Frame" Or ctrlName = "MultiPage" Then
                    For n = 1 To chk  '内部に配置するコントロール数分ループ
                        ReDim arr(1 To r, 1 To 2)   '配列初期化
                        j = j + 1   '次の列指定へカウンター増加
                        'シートに保存した設定値を取得する
                        For y = 1 To r
                            arr(y, 1) = sh.Cells(y, 3)  'プロパティ名
                            arr(y, 2) = sh.Cells(y, j)  'プロパティ値
                        Next y
                        ctrlName = arr(1, 2)
                        cName = "Forms." & ctrlName & ".1"
                        '内部にコントロールを設置する
                        Set Ctrl = .Controls.Add(cName) 'コントロール内に配置
                        With Ctrl
                            For i = 2 To r
                                'コントロールのプロパティ値設定処理へ
                                Call setPVal(arr(i, 1), Ctrl, arr(i, 2))
                            Next i
                        End With
                    Next n
                End If
            End With
        Next j
    End With
    On Error GoTo 0
    Set Ctrl = Nothing
    Set Frm = Nothing
End Sub

※コード内にコメントを入れています。補足が必要な部分を解説します。
51行目で、コントロールのプロパティを設定する別プロシージャを Call しています。
     その別プロシージャーについては次の段で解説します。
53行目で、取得値の最終行の数値でコントロール内のコントロール数を把握します
56行目で、”Frame” か “MultiPage” だった場合の処理を分岐しています。
57行目で、53行目で取得したコントロール数分をループ処理する設定です。
68行目の Set Ctrl = .Controls.Add(cName) がコントロール内に配置設定です。
47行目の Set Ctrl =.Designer.Controls.Add(cName) と比較してください。

53行目で、取得値の最終行の数値でコントロール内のコントロール数を把握します
この部分については、下のとおり前回作成した「全プロパティ値を取得する」コードにフレーム内に配置しているコントロール数を把握して最後の行に数値を書き込むように変更しています。
※実際のコードはサンプルをダウンロードして確認してください。

'UserFormと配置コントロールの全プロパティ値を取得する
Sub getCtrlProperty()

       ~ 途中省略 ~

                If ctrlArry(0) = "Frame" Or ctrlArry(0) = "MultiPage" Then
           s = 0   '個数カウンター初期化
                    For Each inCtrl In c.Controls  '対象フレーム内のコントロール分ループ
                        s = s + 1
                    Next
                    ctrlArry(123) = s   'コントロール数を書き込む
                End If

       ~ 途中省略 ~

End Sub

【実行前の注意点】コントロールの作成順を設定します

注意点として、次のような場合は実行前にコントロールの作成順を設定しておく必要があります。
コントロールの作成は、左から順番に作成していく設定になっていますので、FrameMurtiPage 内に配置するコントロールがある場合、その順番を並べ変えておく必要があります!

コントロールの配置順を実行前に設定する

上のGIF画像では、Frame 内に配置する必要がある Labelの設定順番を入れ替えています。

コントロールのプロパティ値を設定するプロシージャ(抜粋)

長いコードなので中間部分は大幅に省略して抜粋しています。

'指定コントロールのプロパティ値をセットする
Private Function setPVal(ByVal pName As String, obj As Object, _
                               pVal As Variant) As Variant
    Select Case pName
        Case "Accelerator": obj.Accelerator = pVal
 
       ~ 中間省略 ~

        Case "Left": obj.Left = pVal

        ~ 中間省略 ~

        Case "Top": obj.Top = pVal

        ~ 中間省略 ~

        Case "Width": obj.Width = pVal

        ~ 中間省略 ~

    End Select
End Function

3行目の ‘Select Case’ でシートのセルから取得したプロパティ名を引き当てる設定です。
4行目以降で、該当するプロパティ値を取得して関数の値として返しています。

下の項で前回設定した「コントロール値」を取得するコードと比較してください。
式の左右を入れ替えているのがわかると思います。
大きな違いは、引数の数を変更している点です。
【前回】取得では、プロパティ名オブジェクト2つです。
に対して ↓
【今回】設定では、プロパティ名オブジェクト プロパティ値3つです。

コントロールの値を取得するコード(抜粋)

'指定コントロールの値を取得する
Private Function getPVal(ByVal pName As String, obj As Object) As Variant
    Select Case pName
        Case "Accelerator": getPVal = obj.Accelerator
        Case "Alignment": getPVal = obj.Alignment
        Case "AllowColumnReorder": getPVal = obj.AllowColumnReorder

        ~ 中間省略 ~

        Case "Width": getPVal = obj.Width
        Case "WordWrap": getPVal = obj.WordWrap
        Case "Zoom": getPVal = obj.Zoom
    End Select
End Function

動作確認してみました

Frame 内に配置する設定をしなかった場合と、今回のコードで設定した画像を比較します。

Label を Frame 内に配置しない設定で実行した場合の結果

Label を  Frame 内に配置しない設定で実行した場合の結果

完全に失敗していますね(^^;
Label1(青色)UserFormに直接配置されているのがわかります。

Label を Frame 内に配置する設定(今回のコード)で実行した結果

Frame 内に Label1 がちゃんと配置された画像

少しわかりにくいですが、Frame 内に Label がしっかり配置されています(^^)/

MultiPageなどの複雑な UserForm でもうまくできるのかまだ検証は不十分ですが、
とりあえず今回はうまくいきました!

 

スポンサーリンク

まとめ(おわりに)

以上、UserForm から取得した全プロパティ値(コントロールを含む)を使って、VBAで複製する方法の解説でした。

まとめと感想など

くるみこ
くるみこ

プロパティ値取得の時と同じように、無いプロパティも含めて全部を当たるようにしています。無いプロパティでエラーが発生しますが、On Error Resume Next でエラーを無視する方法としました。次回は MultiPage でテストしてみたいと思います。

MultiPage ならパスワード設定ツールで使ったので、それでテストするんですね。

うまくいくのか楽しみです(^^)

【今回わかったことは】
・取得しておいたプロパティ値を使ってユーザーフォームの複製を作成する方法
・フレーム内にコントロールを配置する方法


★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

リンク先に今回記事のサンプルファイルを登録しています!

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください