エクセル2003(VBA)で違うフォームのチェックボックス配列を繰り返し処理したい

質問

エクセル2003(VBA)で違うフォームのチェックボックス配列を
元に順番に繰り返し処理したいのですが、うまくいきません。
どうかお知恵をお貸し下さい。
よろしくお願いします。

■<ユーザーフォーム1>(本体フォーム)
Private Sub CommandButton1_Click()

UserForm2.Show

Dim n As Integer
Dim ender As Integer
ender = 1
i = 1
For n = 1 To 20
If i > 4 Then i = 1 '4を超えたら1に戻る
If cb(i).Value = False Then 'チェックがなかったら飛ばす
i = i + 1 '次の配列へ
n = n - 1 'ループ回数をカウントしないように1引く
Else
Worksheets("sheet1").Cells(ender, 5).Select
Worksheets("sheet1").Cells(ender, 5) = "■" & cb(i).Caption & " = " & cb(i).Value
ender = ender + 1
i = i + 1
End If
Next n

End Sub

■<ユーザーフォーム2>(チェックボックスフォーム)
Private Sub CommandButton1_Click()

Const cb_num = 4 'チェックボックスを4個に設定
Dim cb(cb_num) As Control

Dim ctrl As Control
Dim i As Integer

i = 0
For Each ctrl In Me.Controls 'コントロールの数だけループする
If TypeName(ctrl) = "CheckBox" Then 'タイプがチェックボックスなら
i = i + 1
If i > cb_num Then Exit For 'チェックボックスの数を超えたコントロールは無視
Set cb(i) = ctrl 'コントロールを配列に代入
End If
Next ctrl
End Sub

回答No.3

fujillin · ベストアンサー

No1、No2です。

しかたがないので、こちらでも簡単なサンプルで試してみました。
（面倒くさがりですみません）

簡略化してますが、ユーザフォームを2個作りそれぞれにチェックボックスを5個とコマンドボタンを配置しておいて、サブのフォーム（UserForm2）でチェックした内容を、自動的に1に反映するというものです。
値は直接参照せずに、ご質問文の方法と同じように、一度cb()にContorolを代入して、再度そこから参照するという方法を取っています。

以下のコードで問題なく参照できます。
（フォーム1、2が交互に表示され、チェック内容が更新される。但し終了処理を作ってないので、無理やりとめることになります…）

さて、実験してみたところUserForm2を終了するのにUnloadを使用すると、
Catastrophic failureのエラーとなってしまいます。
（キチンと確認していませんが、多分、cb()の参照先がなくなっている）

Hideを用いていれば、問題なく参照できます。
あるいはcb()にはControl（の参照）を代入せずに、値を代入しておけばFormがUnloadされてもエラーにはならなくなるはずと思います。（確認してませんが）

ところで、No2のお礼などを見ていると、景品の数にもよりますが、Formを2つに分けなくても、1つのフォーム内の一部にチェック欄がある方が、使う人は操作がし易くないでしょうか？（一つのFormに独立しないと収まりきらないほど景品の種類が多ければ別ですが）
表示フォームの切り替えのためのボタン操作も減らすことができますし。
などと、余分なことまで考えてしまいましたが…

◆以下ご参考まで。
Public cb(4) As Control
Sub test()
UserForm1.Show
End Sub

◇<UserForm1>
Private Sub CommandButton1_Click()
UserForm2.Show
For i = 0 To 4
Me.Controls(i).Value = cb(i).Value
Next i
End Sub

◇<UserForm2>
Private Sub CommandButton1_Click()
For i = 0 To 4
Set cb(i) = Me.Controls(i)
Next i
Me.Hide
'Unload Me
End Sub

お礼

何回にも分けてのお付き合い、心から感謝致します。
本当にありがとうございました。

ご提示のあったコードを実行致しました。
見事にできました。!!
なんというか時折、素人丸出しな私に丁重なご対応、また素早いご対応
ありがとうございました。

>Unloadを使用すると、Catastrophic failureのエラーとなってしまいます。
どうも確認方法を間違えていたかも知れません。申し訳ございませんでした。

>1つのフォーム内の一部にチェック欄がある方が、使う人は操作がし易くないでしょうか？
ご提案感謝致します。実はご提案を頂いてから上司に相談しました所、
「時節により、景品種別が大幅に変わるらしくやはり別フォームにして今後の事を考慮したい。」との事でした。
※花火とか鯉のぼり(たぶんおもちゃでしょう)とか

回答No.4

myRange

回答１への補足のコードは変数宣言なども拙いですが
回答３で指摘されてるように
一番の問題点は、Userform2をUnloadしているところです。
Unloadするということは、なーんもない、ということなので
ないものを参照することはできませんよね。

既出の回答で解決でしょうが、一言付け加えると
既出の回答以外にもうひとつ方法があります。
整理すると。

（１）UserForm2をUnloadして、
　　　CheckBoxのCaption（又は、Value）を配列にする
（２）UserForm2をHideにして
　　　現在のように、CheckBoxオブジェクトを配列にする

●そしてもうひとつは
（３）UserForm2をHideにして
　　　CheckBoxオブジェクトを直接使う方法

　　　どういうことかというと
　　　UserForm2.Controls("CheckBox" & i).Value
　　　のように使うということです。

この方法の利点は、配列がいらないということです。

以上、参考までに。

お礼

目から鱗です。
早速、この方法も試してみたいと思います。解決済みではありますが
お力を貸していただきありがとうございます。

特に（３）の方法はコードが完成しだい現在のものから組み替えを
考えたいと思っております。

ありがとうございました。

回答No.2

fujillin

No1です。
フォームをこちらでも作って試してみればわかるのですが、面倒なので…　手抜きで、すみません。

1）チェックボックスのフォーム処理でcbにキチンと値（オブジェクト）が
　入っているかを確認する。
2）本体フォーム側で、cbの値が読めるか確認する

のような手順で、どこに原因があるかの範囲を徐々に狭めていけば、問題点が発見できると思いますが…

あと、最初のご質問文には
　Unload Me
のステートメントはなかったけれど、Unloadしたときに、もとのオブジェクトの値は残っているのだろうか??
　（↑確認していないので不明：消えていそうな気がする）
そのあたりにも原因は考えられないでしょうか？

ところで、中身を見てみると、チェックボックスフォームのイベントって（ユーザにとって）何のためにあるのでしょうか？
フォーム2があるのだから、本体フォームのイベント処理で直接読み込んでしまえば、cbという変数も不要になるのではないでしょうか？

お礼

ご返答、ありがとうございます。下記にお答えします。

>1）チェックボックスのフォーム処理でcbにキチンと値（オブジェクト）が
　入っているかを確認する。
はい、チェックボックスのフォームのEnd subをブレイクポイントとしてウォッチ式で確認したところcb()はきちんと入っていました。

>2）本体フォーム側で、cbの値が読めるか確認する
ここが読めません；；

>Unloadしたときに、もとのオブジェクトの値は残っているのだろうか??
どうもこれが原因か分かりませんがとりあえず他の方法も考えて見ます。

>直接読み込んでしまえば、cbという変数も不要になるのではないでしょうか？
画面遷移がよく分からないのですが可能ならそれでも構いません。

>（ユーザにとって）何のためにあるのでしょうか？
仰るとおりで、可能ならば是非そうしたいところです。
仕様として簡単にお答えするならば、「子ども会の景品配布プログラム」なのですが、
本体フォームの対象ボタンは「現在在庫のある景品の配布先を決める」です。
チェックボックスフォームの対象ボタンは「現在在庫のある景品をチェックする」です。
したい事はシート上の「子ども一覧」に
「現在在庫のある景品を偏ることなく配る(景品をループさす。)」上から順番に代入さす。と言う事なんです。
また、本体フォームではその他色々な処理をしております。

例:)
「現在在庫のある景品をチェックして下さい。」
□おかき　□あめ　□おもちゃ　□ふうせん
↓　(仮におかき、あめ、おもちゃをチェックしたとすると)
シート「子ども一覧」
田中くん　おかき
佐藤くん　あめ
鈴木くん　おもちゃ
武田さん　おかき
角田くん　あめ
・・・・・
のようになります。

補足

すいません。記載漏れです。

>直接読み込んでしまえば、cbという変数も不要になるのではないでしょうか？
やはりこのチェックボックスフォームを使いまわして、本体フォーム側での
色々な処理(例えばチェックのついている品を別シートへ書き出しとか・・)
をしようと考えており、cb()配列を本体に渡すようにしたく思います。

後記
Unloadはどうも配列が消えることとは関係なかったようです。
ウォッチ式で確認しました。

回答No.1

fujillin

＞順番に繰り返し処理したいのですが、うまくいきません。
どううまくいかないのか、書いてないので不明ですが…

想像すると、受け渡しに使おうとしている（？）配列cb()がローカル変数になっているようなので、本体フォームのルーティンの中から参照できていないだけではないのでしょうか？
途中で、内容を確認するなどして、値がとれているかどうか確認してみれば？

お礼

＞どううまくいかないのか、書いてないので不明ですが…
すみませんでした。少し言葉足らずでした。要は
「チェックボックスフォームで作った配列cb()が本体フォームで動かない。」
事がうまくいかないと言ってしまったのです。
申し訳ございませんでした。

＞受け渡しに使おうとしている（？）配列cb()
はい、その通りです。

＞本体フォームのルーティンの中から参照できていないだけ
そうみたいです。当然、上記コードを一つのフォームで実行するとうまく動くのですが
2つのフォーム(本体とチェックボックス)に分けるとできません。

補足

素早いご対応、感謝感謝です。
仰るとおり、目を皿にしてデバック・ウォッチしました。色々見えてきたのですが、
どうしてもフォームを切り替え時に配列が消えてしまいます。
どうか解決法のご教授をお願い致します。
また、アドバイス通りにローカルをグローバルに変えてみました。

■標準モジュール
Option Explicit
Public cb() As Control
Public cb_num As Long
Public ctrl As Control
Public cbint As Integer

■<ユーザーフォーム1>(本体フォーム)
Sub CommandButton1_Click()

Dim ctrl As Control
Dim cbint As Integer

UserForm2.Show

Dim n As Integer
Dim ender As Integer
ender = 1
cbint = 0
For n = 1 To 20
If cbint >= UBound(cb) + 1 Then cbint = 0 'チェック数を超えたら0に戻る
Worksheets("sheet1").Cells(ender, 5).Select
Worksheets("sheet1").Cells(ender, 5) = "■" & cb(cbint).Caption & " = " & cb(cbint).Value
ender = ender + 1
cbint = cbint + 1
Next n

End Sub

■<ユーザーフォーム2>(チェックボックスフォーム)
Public Sub CommandButton1_Click()

cbint = 0
For Each ctrl In Me.Controls 'コントロールの数だけループする
If TypeName(ctrl) = "CheckBox" And (ctrl) = True Then 'タイプがチェックボックスでTrueなら
ReDim Preserve cb(cbint)
Set cb(cbint) = ctrl 'コントロールを配列に代入
cbint = cbint + 1
End If
Next ctrl

Unload Me
End Sub