VBA Parentを使ったらUserFormを完全復元できた

VBA Parentを使ったらUserFormを完全復元できた

VBAでUserFormの全プロパティ値を取得するについての内容を少しづつバージョンアップしています。前回は「VBA .Parentでコントロールをコンテナ単位で取得する」でコントロールのプロパティー値をコンテナ単位で取得できました。
今回は、取得できたプロパティ値のデータを使って、MultiPage のある UserForm を完全復元できるかどうか試していきます。ちなみに、MultiPage 内には Frame や ListView も入っています。

くるみこ
くるみこ

UserForm と一口に言っても千差万別にいろいろなコントロールを配置できます。
今回は、自分が使っている UserForm でテストしていきますが、なるべく汎用で使えるようにしていきたいと思ってます。それではさっそくやっていきましょう。

コンテナ内にコンテナが配置されていたりするパターンもありますよね。

どのように処理するのか楽しみです。よろしくお願いしますm(__)m

以前の記事「VBA 取得したプロパティ値でユーザーフォームを複製してみた」です。

この時は、簡単なユーザーフォームに対してのものでしたので、複雑なものには対応していませんでした。今回はこれをバージョンアップさせた内容になっていますので是非ご覧ください。

【この記事でわかることは】
・MultiPage 内の Page 追加と Page プロパティの設定方法
Parent でコンテナとなるオブジェクトにコントロールを配置していく方法
ListView コントロールの配置方法

コンテナ内にコンテナが設置されている場合の対処方法

VBA で UserForm を作成する場合の参考にもなるかと思います。

スポンサーリンク

はじめにVBAコードから

メインのVBAコードがこちらです

概要と補足についてはあとで説明します。コメントを含めて136行あります。

'シートに保存したプロパティ値からユーザーフォームを作成する
Sub UserFormAdd()
    Dim Frm As VBIDE.VBComponent    'UserForm用
    Dim mCtrl As MSForms.Control    'MultiPage用
    Dim fCtrl As MSForms.Control    'Frame用
    Dim Ctrl As MSForms.Control     '配置コントロール用
    Dim PVal As Variant             '設定値保存配列変数
    Dim r As Long                   '行数取得保存用
    Dim c As Long                   '最終列保存用
    Dim X As Long, Y As Long        '配列ループ処理用
    Dim i As Long, j As Long        'ループ処理用変数
    Dim P As Long                   'Pages(Index)用
    Dim chk As Long                 'Page数チェック用
    Dim ctrlName As String          'コントロール名
    Dim cName As String             'Addメソッド引数用
    Dim pName As String             'Parent名
    Dim pIndex As Long              'Pages(Index)取得用
    Dim wb As Workbook, sh As Worksheet
    Set wb = ActiveWorkbook
    Set sh = wb.Worksheets("Ctrl_SetValue") '設定値保存シート
    
    r = sh.Cells(Rows.Count, 1).End(xlUp).Row   'UserForm設定値の最終行取得
    ReDim PVal(1 To r, 1 To 2)      '2次元配列初期化
    'シートに保存した設定値を配列に保存する
    For Y = 1 To r                  '行数分のループ
        For X = 1 To 2              '列数分のループ
            PVal(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
        .Name = PVal(2, 2)  'オブジェクト名だけ先に設定
        For i = 3 To r
            .Properties(PVal(i, 1)) = PVal(i, 2)  'UserFormのプロパティ値セット
        Next i
        '取得保存データの最終列取得
        c = sh.Cells(1, Columns.Count).End(xlToLeft).Column
        'プロパティー保存最終行取得(3列目から)
        r = sh.Cells(Rows.Count, 3).End(xlUp).Row
        P = 0   'Pages(Index)用変数の初期化(0から始まる)
        For j = 4 To c  '3列目がプロパティ名なので4列目からが設定値
            'シートに保存した設定値を(関数で)配列に読み込む
            PVal = ReadPROP(r, j, sh)
            ctrlName = PVal(1, 2)                   'コントロール名(Type)
            cName = "Forms." & ctrlName & ".1"      'Addメソッドに渡す引数
            pName = PVal(3, 2)                      'Parent親オブジェクト名
            'コントロールのTYPEで処理を分岐
            Select Case True
                Case ctrlName Like "MultiPage*"     'MultiPageの場合
                    '親オブジェクトを確認して配置
                    Select Case True
                        Case pName Like "Page*"     'Pageの場合
                            pIndex = PagesIndex(mCtrl, pName) 'PageのIndexを取得
                            Set mCtrl = mCtrl.Pages(pIndex).Controls.Add(cName)
                        Case pName Like "Frame*"    'Frame内に配置
                            Set mCtrl = fCtrl.Controls.Add(cName)
                        Case Else                   'UserFormに配置
                            Set mCtrl = .Designer.Controls.Add(cName)
                    End Select
                    For i = 2 To r  'コントロールのプロパティ値設定
                        Call setPVal(PVal(i, 1), mCtrl, PVal(i, 2))
                    Next i
                    'Page数をチェックして不足分を追加する
                    chk = PVal(r, 2)   'chk = Pageの数
                    For i = 1 To chk - 2
                        mCtrl.Pages.Add
                    Next i
                Case ctrlName Like "Page*"          'Pageの場合
                    For i = 2 To r  'Pageのプロパティ値設定処理
                        If PVal(i, 2) <> "" Then 'PageではNULLの場合処理しない
                            Call setPVal(PVal(i, 1), mCtrl.Pages(P), PVal(i, 2))
                        End If
                    Next i
                    P = P + 1   'PageのIndexをカウントアップ
                Case ctrlName Like "Frame*"         'Frameの場合
                    Select Case True '親オブジェクトの種類で処理を分岐
                        Case pName Like "Page*"
                            pIndex = PagesIndex(mCtrl, pName)
                            'Pageにコントロール設置
                            Set fCtrl = mCtrl.Pages(pIndex).Controls.Add(cName)
                        Case pName Like "Frame*"
                            Set fCtrl = fCtrl.Controls.Add(cName)
                        Case Else   'UserFormに配置
                            Set fCtrl = .Designer.Controls.Add(cName)
                    End Select
                    For i = 2 To r  'コントロールのプロパティ値設定
                        Call setPVal(PVal(i, 1), fCtrl, PVal(i, 2))
                    Next i
                'ListViewの場合
                Case ctrlName Like "ListView*"
                    'ListViewの場合はFormsではなくMSComctlLibに変更
                    cName = "MSComctlLib.ListViewCtrl.2"
                    '親オブジェクトの種類で処理を分岐
                    Select Case True
                        Case pName Like "Page*"
                            pIndex = PagesIndex(mCtrl, pName)
                            'Pageにコントロール設置
                            Set Ctrl = mCtrl.Pages(pIndex).Controls.Add(cName)
                        Case pName Like "Frame*"
                            Set Ctrl = fCtrl.Controls.Add(cName)
                        Case Else   'UserFormに配置
                            Set Ctrl = .Designer.Controls.Add(cName)
                    End Select
                    For i = 2 To r  'コントロールのプロパティ値設定
                        Call setPVal(PVal(i, 1), Ctrl, PVal(i, 2))
                    Next i
                'その他のコントロール
                Case Else
                    '親オブジェクトの種類で処理を分岐
                    Select Case True
                        Case pName Like "Page*"
                            pIndex = PagesIndex(mCtrl, pName)
                            'Pageにコントロールを配置
                            Set Ctrl = mCtrl.Pages(pIndex).Controls.Add(cName)
                        Case pName Like "Frame*"
                            'Frameにコントロールを配置
                            Set Ctrl = fCtrl.Controls.Add(cName)
                        Case Else   'UserFormに配置
                            Set Ctrl = .Designer.Controls.Add(cName)
                    End Select
                    For i = 2 To r  'コントロールのプロパティ値設定
                        Call setPVal(PVal(i, 1), Ctrl, PVal(i, 2))
                    Next i
            End Select
        Next j
    End With
    On Error GoTo 0
    Set Ctrl = Nothing
    Set fCtrl = Nothing
    Set mCtrl = Nothing
    Set Frm = Nothing
    Set sh = Nothing
    Set wb = Nothing
End Sub

2つの Functionプロシージャで処理を分けています

Page名からPagesコレクションのインデックス番号を取得する関数

MultiPage の Pageを操作するには Pagesコレクションのインデックスで指定する必要があります。

'Pageオブジェクト名からPagesインデックスを取得する
Private Function PagesIndex(ByVal mCtrl As Object, pName As String) As Long
    Dim i As Long
    'MultiPageオブジェクトからPage数をカウントして調べる
    For i = 0 To mCtrl.Pages.Count - 1
        If mCtrl.Pages(i).Name = pName Then
            PagesIndex = i
            Exit For
        End If
    Next i
End Function

設定値保存シートから値を取得して配列に格納する関数

'設定値保存シートの指定列から値を取得して配列に保存する
Private Function ReadPROP(ByVal r As Long, j As Long, sh As Worksheet) As Variant
    Dim Y As Long
    Dim PVal As Variant  '配列用変数

    ReDim PVal(1 To r, 1 To 2)   '2次元配列初期化
    'シートに保存した設定値を配列に読み込む
    For Y = 1 To r
        PVal(Y, 1) = sh.Cells(Y, 3)  'プロパティ名
        PVal(Y, 2) = sh.Cells(Y, j)  'プロパティ値
    Next Y
    ReadPROP = PVal     '関数に配列を渡す
End Function

全体の流れを解説します

取得したプロパティ値が書き込まれたシートの設定がこちらです。

取得プロパティ値を保存しているシート

前提として、前回の「VBA .Parentでコントロールをコンテナ単位で取得する」で設定したコードでコントロールのプロパティー値をコンテナ単位で取得できている必要があります。

この取得した順番で UserForm を作成していきます。

コードの基本的な設定

・初めに UserForm を作成します。
・次に、順番どおりにコントロールを一つづつ配置していきます。
・配置するコントロールの Type により処理を次のように分岐します。
・「MultiPage」「Page」「ListView」「Frame」「その他」
・「MultiPage」では、必要に応じて Page を追加作成します。
 ※ MultiPage は設置の段階で自動的に2つのPageが配置されます。
・各コントロールの配置先は、Parent オブジェクトの下に配置するようにします。
コンテナとなるオブジェクトの種類ごとに変数を持たせるようにします。
UserForm = Frm」「MultiPage,Page = mCtrl」「Frame = fCtrl」です。
・コンテナの中にコンテナを配置する場合もありますが、配置と同時にオブジェクト変数が配置したコントロールに変更されるようにします。
・したがって、次に配置するコントロールはそのオブジェクトの下(中)に配置されることになるので問題なく配置できるようになります。
・配置できたコントロールは、配置後すぐにプロパティ値を設定します。

以上が基本的な処理の流れです。

コードの補足説明

コード内にコメントは入れていますが、補足が必要な部分だけ解説していきます。

メインコードの補足説明

36行目、UserFormのプロパティ値は .Properties(PVal(i, 1)) = PVal(i, 2)でセット

45行目、PVal = ReadPROP(r, j, sh) は別FuctionプロシージャReadPROPでシートに保存した設定値を配列に読み込んでいます。

46行目、取得した配列から ctrlName = コントロール名(Type)を変数に代入
47行目、cName = “Forms.” & ctrlName & “.1” でAddメソッドに渡す引数作成
48行目、pName = 親オブジェクト名を変数に代入

55行目、pIndex = PagesIndex(mCtrl, pName) 関数でPagesのIndexを取得
56行目、Page の操作は Pagesコレクションのインデックスで指定する必要があります。
Set mCtrl = mCtrl.Pages(pIndex).Controls.Add(cName)

59,85,103,120行目、Case Else で UserForm* として扱います。
※ 実は、34行目の .Name = PVal(2, 2) でオブジェクト名を変更していますが、失敗してしまうことが多いんです。(同じ名称のオブジェクト名が無い場合でもなぜ失敗してしまうのか、原因が不明です。最初は成功しても、2回目以降は失敗したりします。おそらく内部的にオブジェクト名が保持されたままになっているのか?)

65~69行目、Page数をチェックして不足分は Pages.Add メソッドで追加しています。

70~76行目、Page のプロパティ値設定では、値が NULL の場合無視します。
※ ここを無視せずに全て適用してしまうとMultiPageのプロパティに影響を与えてしまい設定が崩れてしまうことがわかったためです。

94行目、ListView は MSFormsコントロールではなく MSComctlLibのコントロールなので、引数となる変数を cName = “MSComctlLib.ListViewCtrl.2″ に変更しています。

2つの関数の補足説明

1つ目は、Page名からPagesコレクションのインデックス番号を取得する関数です。

Page名からPagesコレクションのインデックス番号を取得する関数

受け取る引数は次の2つです
 ByVal mCtrl As Object ⇒ コントロールオブジェクト(値渡し)
 pName As String ⇒ 親オブジェクト名

戻り値は、Pagesコレクションのインデックス
※ Pages コレクションのインデックスは「0」からスタート

2つ目は、該当のプロパティ値を保存シートから配列に取得格納する関数です。

設定値保存シートから値を取得して配列に格納する関数

受け取る引数は次の2つです
 ByVal r As Long ⇒ 保存プロパティ数(値渡し)
  j As Long ⇒ 取得対象の列番号
 sh As Worksheet ⇒ ワークシートオブジェクト

戻り値は、取得したプロパティ値の2次元配列

6行目で、2次元配列を初期化

9行目、オブジェクト名を取得し配列に格納

8~11行目は、シートのセルに保存しているプロパティ値を2次元配列(プロパティー名と値の2次元)に書き込むループ処理

12行目で、取得できた配列を関数に渡して戻り値としています。

作成したコードをテストする

今回のテストに使った UserForm は「Excel VBA UserForm パスワード生成管理ツール」のものを使いました。

テストに使用したUserFormの画像

4枚目の「Page5」は、今回のテスト用に追加で用意したものです。
Pageの中にTabStripを配置、その中にFrame6、さらにその中にFrame7を配置、
加えてFrame7の中にListViewMultiPageを配置して、
そのPage内にFrame8、さらにその中にCheckBox2を配置

このような複雑な設定でも VBAでそのまま復元させることができるのかテストします。

コード実行テスト時の動画

どうですか? ちゃんと復元できているでしょ(^^)

左側の VBAProject 内に UserForm が動的に作成されたのがわかると思います。

作成自体があっという間に終わってしまったので、各ページを開けて中身を確認しています。

全てのコントロールが、元どおりに配置されています。

もちろん、コンテナ内へも元どおりにコントロールが配置できていますでしょ(^^ゞ

 

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

 

スポンサーリンク
Amazonクーポンを利用してお得に購入!

Amazonギフト券のご利用ならこちらからどうぞ!

Amazon プライム会員なら特典がいっぱい!

Amazon Excel関連のおすすめ書籍へのリンク!

まとめ(おわりに)

以上、VBAで複雑にコントロールが配置されている UserForm を完全復元させる方法の解説でした。

UserForm や コントロールのイベントプロシージャに記述しているコードについては参照していただければ設定可能です。

別途、いつになるかわかりませんが、時間があるときにこの部分(VBAコード)について別記事にでもしてみようかと思います。

まとめと感想など

くるみこ
くるみこ

苦労しましたけどうまくいきましたね(^^♪
これならなんとか汎用で使うことができそうですね。
ただ、今回はUserFormやコントロールのイベントプロシージャに記述しているコードについては無視しています。別途、いつになるかわかりませんが、時間があるときにこの部分について別記事にでもしてみようかと思います。ひとまずお疲れさまでした(^^)/

他からUserFormを探してきて試してみたいと思います(^^)

【今回わかったことは】
・MultiPage 内の Page 追加と Page プロパティの設定方法と注意点がわかりました
・Parentでコンテナとなる親オブジェクトを特定してコントロール配置する方法
・MSComctlLib のコントロール「ListView」を配置する設定方法
・コンテナ内にコンテナが設置されている場合でも親オブジェクトを正しく指定すれば対処できることがわかりました。


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

【今後の記事について】

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

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

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

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

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

タイトルとURLをコピーしました