エクセルＶＢＡユーザーフォームの変数の設定方法について

質問

すいません、エクセルＶＢＡのユーザーフォームの変数の設定方法について質問があります。

１　ユーザーフォームを２つ用意する。
２　それぞれにComboBox1をおく。
３　立ち上げたユーザーフォームについて、UserForm_InitializeでComboBox1に"a"のAddItemを作る。

この、「立ち上げたフォームのComboBox1に"a"のAddItemを作る」
という作業を各々のユーザーフォームに記載するのではなく、標準モジュールでまとめて記載する方法で躓いています。

Public m As String
Private Sub UserForm_Initialize()
’フォーム１を立ち上げた場合
　　m = "UserForm1"
　　Call Test1(m)
End Sub

Private Sub UserForm_Initialize()
’フォーム２を立ち上げた場合
　　m = "UserForm2"
　　Call Test1(m)
End Sub

↓
標準モジュールに記載
Sub Test1(m As String)
VBA.UserForms.Add(m).ComboBox1.AddItem "a"
End Sub

これだとUserForm_InitializeとTest1の間で無限ループが始まってしまい、うまく進んでくれません。
ヘルプを見ましたが、Add(変数)でユーザーフォームを変数で指定できるということ以上のことは発見できず行き詰っています。
　それぞれのフォームに書けばいいだけの話なのかもしれませんが、メンテを考えると出来ればまとめて記述しておきたいと考えています。
解決方法がありましたらどうぞよろしくご教示願います。

回答No.6

Wendy02 · ベストアンサー

こんにちは。

ご質問のComboBox からTreeView では、お話の主旨がまったく内容が変わってしまいましたので、かなり混乱をしてしまいます。最初のご質問の内容では、まったく目的と意味が違います。コントロール・オブジェクトは、あくまでもひとつで十分ですが、実際に、このようなコードでは、コードが通りますか？

MyList(0) というのは何ですか？

>For Each MyList(0) In Sheets("AAA").Range("B2:B100")　
　　'-- 略　--
>　　For Each MyList(1) In Sheets("AAA").Range("C2:C100")
　　'-- 略　--
>　　Next
>Next

一応、UserForm が複数ある場合で、TreeView の設定は同じ仕様を持っているとしたら、このようになるのかもしれません。今のままでは、どうしようもないとは思いますが。

'------------------------------------------
'標準モジュールか、クラスモジュール

Sub TreeViewSetting(uf As UserForm)
　　Dim c As Range
　　Dim d As Range
　　Dim sh As Worksheeet
　　Set sh = "AAA"
　　With uf.TreeView1
　　　 '--設定--
　　　　For Each c In sh.Range("B2", sh.Range("B65536").End(xlUp))
　　　　　 For Each d In sh.Range("C2", sh.Range("C65536").End(xlUp))
　　　　　 '--略　--
　　　　　 Nect d
　　　　Next c
　　End With
End Sub

お礼

度々ありがとうございます。
教えていただいた方法でかなりすっきりと書けることができました。
("UserForm" & i).ComboBox1～みたいな簡単な書き方があるのかと思い、コンボボックスもツリービューも一緒だろうと安易に考え、コンボボックスを例にしたのですが、何が問題点になるのかがわかっておらず、お手間をおかけしてしまいました。失礼いたしました。

回答No.5

imogasi

質問の意味あいまいなてんあり。
（したいことを文章でも添えよ。コードばかりに拘るな。）
コンボボックス１とコンボボックス２のアイテムは同じなのか。違うのでしょう。
あとを読むと同じらしいね。明記しておくこと。
●コンボボックスの所属するフォームをプログラムで指定したいと言うことか。
●アイテムは"a"だけ１つで、済むはずが無い。複数項目だと思うが。
カンマ区切りの文字列をSplitして各アイテムに分配できるが。
どういう内容のデータを受け渡すのか。
●イベントのコードではPrivate Sub UserForm_Initialize()
だが、あくまで特定のVBAProjectのUserForm1のイベントであるはず。
そこでなぜ
m = "UserForm1"
　　Call Test1(m)
とやるのか。
●AddAitemsをアイテム項目がフォームごとに同じなので、アイテムの設定処理を
１つにまとめたいと言うことか。
●デザインモードでユーザーフォームを増やしたりするには
参考
http://chibie2000.hp.infoseek.co.jp/excelvba/index.htm
●実行中にコンボのItem設定をやりたいのか。
エクセル（程度）では、普通はアイテムの設定はデザインモードの時に人手で終わらせる。
ーー
アイテムの設定は
USERForm1のコードの表示で
Private Sub UserForm_Initialize()
test01
End Sub
標準モジュールModule1に
Sub test01()
UserForm1.ComboBox1.RowSource = "A1:A4"
UserForm2.Show
UserForm2.ComboBox1.RowSource = "A1:A4"
End Sub
アイテムはシートのA1:A4に入れて置く例。
エクセルらしい点なのでこちらを掲示してみた・使った。
もちろん
Sub test02()
'UserForm1.Show
UserForm2.ComboBox1.AddItem "a"
UserForm2.ComboBox1.AddItem "b"
UserForm2.ComboBox1.AddItem "c"
UserForm2.ComboBox1.AddItem "d"
End Sub
の方法もある。
--
結局やりたいことへの、対処は下記のようなことかな？
ユーザーフォーム１，２，３を挿入しておく。
標準モジュールにおいて
各モジュールから使えるように各モジュール外の最初に
Dim uf(1 To 3) As Object
ーー
コードは
Sub test03()
Set uf(1) = UserForm1
Set uf(2) = UserForm2
Set uf(3) = UserForm3
test04 (3)
End Sub
ーーー
Sub test04(a)
uf(a).Show
End Sub
を入れておいて、test03を実行する。
これでtest03で指定したユーザーフォームが捉えられるから
uf(x).Combobox1.・・でうまく行かないか、やって見てください。

お礼

すいません、端的に標準モジュールでユーザーフォームの変数を指定する方法があるんじゃないかと思い、かなりざっくりとした質問にしてしまいました。
実際には
１　フォームを５枚用意する。
２　エクセルのシート１０枚にそれぞれデータを入れる
３　フォームを立ち上げる
４　コンボボックスで作成するツリーを選択し、ツリーを作成する

という作業になります。
Sub TreeViewmakeA(()
Dim MyList(3), MyNumber
With TreeView1
.Nodes.Clear
For Each MyList(0) In Sheets("AAA").Range("B2:B100")
If MyList(0, 0).Value <> MyList(-1, 0) Then
MyNumber = MyNumber + 1
.Nodes.Add(Key:=MyNumber, Text:=MyList(0).Offset(0, 0)).Expanded = True
For Each MyList(1) In Sheets("AAA").Range("C2:C100")
.Nodes.Add Relative:=MyNumber, Relationship:=tvwChild, _
Key:=MyNumber, Text:=MyList(1).Offset(0, 0)
Next MyList(1)
Else: ・・・

みたいのを全部のフォームにごちゃごちゃ貼り付けていくと、非常に観づらくなる上に、
ツリーの作成ルールが結構頻繁に変わるので、全部のフォームについてコードを正しく直したかどうかをチェックするのが大変なので、
ツリーの作成は標準モジュールで一回だけ記述し、
ユーザーフォーム内はコンボボックスのチェンジに応じて
mText="UserForm1"
Case A
Call TreeViewmakeA(mText)
Case B
Call TreeViewmakeB(mText)
で、標準モジュールのプロシージャを呼び出し
Sub TreeViewmakeA(mText as String)
With mText.TreeView1
・・・・

Sub TreeViewmakeB(mText as String)
With mText.TreeView1
・・・・

というようなイメージで処理ができないものかと思いました。
とりあえずツリーを渡すという方法でうまくまとめられるか試してみます。
如何せん素人で基本的なプログラムの仕組みが中々わからず
質問も要領を得ないものになってしまいます。気をつけます。
ご回答ありがとうございます。

回答No.4

Wendy02

#2の回答者です。

ご質問の元のコードを活かせばコントロールを渡すことになりますが、一般的に、全体のプロジェクトの中で共有するものは、オブジェクトではなくて、データなのだと思います。コントロールは、一旦設定してしまえば、固定化してしまいますが、データは、ユーザーによる変更が起こるものとして変数にして渡します。それは、よくあることです。

そうすると、CombBox に変数として渡すものとして、別にある固定データを渡すことが多いのではないでしょうか。複数ある コントロールの名前が変更するというのは、出来上がってからでは、論外というか、話になりませんが、複数のコントロールがある場合は、まったく違う方法を取りますね。今は、そうした質問には想定していません。

お礼

ありがとうございます。大変勉強になりました。
実際には、複数のフォームにツリービューを１個づつ設定し、フォーム上に置いたコンボボックスの選択で作るツリーを選択するという作業を想定しています。
ツリーの元となるリストも１０枚程度のシートがあり、それぞれ親と子の数が違ったりするので、TreeView1のNodes.Addの種類も１０とおり用意しています。

コンボボックスの値が"a"なら
TreeView1のNodes.Addのパターン１を呼び出し
コンボボックスの値が"b"なら
TreeView1のNodes.Addのパターン２を呼び出し
・・・・
というような処理を各フォームでで行うことになります。
　ツリーの作成が自分には複雑な作業で、全部のフォームに書くとかなり巨大な量になってしまい、ツリーの作成ルールの変更があったときにも全部書き直さないといけないので、出来ればまとめてしましまいたいと考えています。
作るツリーはどのフォームでも共通のものなのでフォームを分けたくないのですが、ツリー作成後の作業パターンも多く、どうしても一枚のフォームには収まりそうにありません。
ツリービューを渡すという方法でまとめられるよう今からやってみます。

回答No.3

cistronezk

「UserForms.Add(m)」は、変数ｍで指定した名前のUserFormを作成します。つまり、Initializeイベント発生するので無限ループになります。

すでに回答されてるように、ComboBoxを渡した方がいいでしょう。でないと、test1()を使うためにはコンボボックス名が「ComboBox1」に制約されてしまいます。コンボボックスの名前の変更が必要になった場合、test()も書き換えるないといけなくなります。

お礼

ありがとうございました。
実際にはツリービューを置くことを予定しており、ツリービューの名前が変わることは恐らく無いのですが、
ツリービューを渡すという方法で検討してみます。

回答No.2

Wendy02

こんにちは。

ご質問に関しては、以下ようにすればよいのです。意味は分かりますが、あまり見られない方法ですね。

なお、変数は、m は、設定されているか分かりにくいので、mTxt にしました。複数の場合は、配列変数にしてください。AddItem ではなく、List にして配列変数を受けます。

これ自体は、標準モジュールを使う意味があるとは思えませんが、もう少し、お話を聞かせていただかないと分かりません。

'標準モジュール
'-------------------------------------------

Public mTxt As String
Sub Test1()
　mTxt = "a"
End Sub

'-------------------------------------------
'UserForm モジュール

Private Sub UserForm_Initialize()
　Call Test1
　ComboBox1.AddItem mTxt
End Sub

'もうひとつのユーザーフォーム側は、Public 変数に入れている限りは、Call Test1 は必要ありません。どこかで、Call Test1 は、一回きりで済みます。後は、VBAコードはいじらなければ、変数は確保されます。
'-------------------------------------------

回答No.1

masa_019

こんにちは。
イマイチ質問の意味を理解できていないかもしれませんが、ふたつの UserForm_Initialize から 標準モジュールの Test1 を呼び出して、コンボボックスのリスト項目を設定したいということでよろしいのでしょうか？だとすると、

フォームモジュールに
(UserForm1、UserForm2ともに)

Private Sub UserForm_Initialize()
Test1 Me.ComboBox1
End Sub

標準モジュールに

Sub Test1(ByVal cbox As MSForms.ComboBox)
cbox.AddItem "a"
End Sub

とすればできると思いますが、いかがでしょうか。

お礼

ありがとうございます。
実際にはツリービューをおくということを予定しているのですが、
コンボボックスでもツリービューでも同じだと思いますので、
ツリーを渡すという方法でまとめてみたいと思います。
ツリーの作成というのが自分にはやたら複雑に感じ、子供の数が多いとキーの設定やらなんやらで長大なコードになってしまいます。
今は全部のフォームにそれぞれ書いているのですが、メンテが大変できちんと全てのフォームが直っているかチェックするのに手間がかかっています。
また何かありましたらよろしくお願いいたします。