Excel2007 VBA シートとユーザーフォームの値のやり取り

質問

1.シートからユーザーフォームを呼び出す。
2.シートからユーザーフォームへ値を渡す。
3.ユーザーフォームからシートへ値を返す。
この一連の処理を行いたいのですが。

TestSheet-----------
Dim oForm As TestForm
Set oForm = New TestForm
oForm.SetData("渡す値")
Call oForm.Show
Dim Result As String
'Result = oForm.GetResult

Set、GetメソッドはTestForm内に存在します。
しかし、ユーザーフォームでUnloadを行った時点でoFormが破棄されるらしく、Result = oForm.GetResultでエラーとなります。

変数とSet、Getメソッドを標準モジュールに記述すれば解決するのですが、スコープ範囲がこのシートとユーザコントロールに限定できないのでなるべく記述したくありません。
他にユーザフォームから値を受け取る方法はありませんでしょうか。

回答No.2

noname#144013 · ベストアンサー

こんにちは。

下記内容は一例です。

ユーザーフォーム内の処理で、

　　Unload Me

のように自フォームをUnloadしてしまうと、フォームのオブジェクトが破棄
されてしまうので、フォームを呼び出した側では、フォーム内のデータは
取得できなくなってしまいます。

Unloadの代わりに、ユーザーフォーム側で、

　　Me.Hide

のように、Hideメソッドを使用すれば、フォームが非表示になるだけで、
フォームのオブジェクト自体は破棄されずに、フォームを呼び出した側
に制御が戻ります。（※フォームは一見、終了したように見えます。）

ユーザーフォーム側で上記のようにしておいて、フォームを呼び出す側で、

　　'フォームを表示
　　UserForm1.Show (vbModal)

　　'フォーム上のデータ取得
　　Dim vDat As Variant
　　vDat = UserForm1.TextBox6.Value

　　'フォームを閉じる
　　Unload UserForm1

　　'取得したデータを表示
　　MsgBox "取得データ = " & vDat

のようにすれば、フォームを呼び出した側で、フォーム内のデータが取得
できると思います。
上記のように、フォームを閉じる処理は、フォームを呼び出した側に記述
します。

注）上記処理は、当方のExcel2000で確認してみた結果ですが、他の
　バージョンのExcelで、同様に動作するかどうかは判りません。
　もしも、上手く動作しなかった場合はすみません。

以上です。参考になれば幸いです。

お礼

成程、非表示ですか！これは思いつきませんでした。
試したところ、Excel2007でも問題なく実現できました。
誠に有難うございます。

回答No.1

avanzato

こんにちは。
標準モジュールに
'共通変数定義
Public DataBridge As String
としてフォームを閉じる前にDataBridgeに値を送ればフォームを閉じても消えないと思います。

お礼

>フォームを閉じる前にDataBridgeに値を送れば
やはり標準モジュールに変数を定義するしか方法が無いようですね。
ありがとうございます。