VBA-Workbook_Open()イベントで

質問

再度、お世話になります。

手順１.book1よりボタンをクリックしてbook2を開く
手順２.あるフォルダの.xlsファイル名をボタン名に貼り付ける
手順３.book2の戻るボタンでbook2を閉じ、book1を開く
手順４.手順１を実行する

という流れを行ったところ1回目のbook2の表示の際にはうまくファイル名を表示できるのですが、戻った後の2回目のbook2の表示ではファイル名が1回目のときのままになってしまいます。

一部
[book2での処理]
***Module1***
Public strPath as String

***ThisWorkbook***
Public Sub Workbook_Open()

strPath = "C:\" & Worksheets("Sheet1").Range("A1")

myName = Dir(strPath & "\*.xls", vbDirectory)

If myName <> "" Then
FCnt = FCnt + 1

Worksheets("Sheet1").OLEObjects("btn" & FCnt).Object.Caption = myName
Do
・・・（略）
Loop
End If

End Sub

***Sheet1***
Private Sub btnBack_Click()

Workbooks.Open Filename:="C:\book1.xls"

Workbooks("book2.xls").Close saveChanges:=False
End Sub

のようにしています。

お手数をおかけしますが、どなたかご教授お願いいたします。

回答No.2

Wendy02

こんばんは。Wendy02です。

＞コードをすべて貼り付けることに専念してしまいました・・・。

うーん。困りましたね。一応の説明をしていただければよいのですが、会社のものだから、守秘義務とかあるのでしょうか？肝心な設定部分を見せられないとすると、残念ながら、これ以上は回答できないのですね。

＞＞Module1 の
＞＞＞Public strPath as Stringのこれは、常に変るのですか？
＞→はい、常に変わります。

変りますって言われても、設定部分はどうしているのか分りません。おそらく、これにまつわる部分がおかしいのではありませんか？

雰囲気としては、だいたい、みなさんミスる所と同じところの部分のような気がしますが、それは、こちらの想像ですから、親切心がやぶへびになりかねません。

一応、ご自分で調べる方法は、Open イベントの途中で、Stop という命令を入れて、マクロを止めます。その後は、ステップモードと、ローカル・ウィンドウで確認しながら見ていきます。ただし、Public 変数では、その設定したブックだけですから、間違えないようにしてください。参照設定しなければ、やりとりはでません。

お礼

Wendy02様　ご回答ありがとうございます。

Wendy02様の過去の回答を拝見させていただき、Runメソッドを使用することで解決いたしました。

丁寧に回答していただき本当にありがとうございました。

回答No.1

Wendy02

こんばんは。Wendy02です。

Module1 の
＞Public strPath as String

のこれは、常に変るのですか？そうでなかったら、

Public Const STR_PATH As String = ○○○○
だと思いますが、ThisWorkbook.Path なら、Openイベントで設定するか、Public変数自体が必要ありませんね。

ここは、
＞Public Sub Workbook_Open()

Private です。もし、そうでないなら、Application イベントです。

＞myName = Dir(strPath & "\*.xls", vbDirectory)

vbDirectory は、「*.xls」にするなら必要ないはずです。

If myName <> "" Then
FCnt = FCnt + 1
Worksheets("Sheet1").OLEObjects("btn" & FCnt).Object.Caption = myName
Do
・・・（略）
Loop

全てのボタンにファイル名を入れるわけですよね。ボタンとファイル名の数が合っているのでしょうか？整合性が合わないとなると、たぶん、

＞Worksheets("Sheet1").OLEObjects("btn" & FCnt).Object.Caption = myName

こちら側にエラーが出るはずです。
ただ、設計としては、イベントで登録するよりも、ファイル名(Book）を固定させるべきですね。それに、登録したファイル名をCaption で使えなければ、意味がありません。

しかし、以下の部分は、直接、上記のこととは関係ありませんね。

＞手順３.book2の戻るボタンでbook2を閉じ、book1を開く
＞戻った後の2回目のbook2の表示ではファイル名が1回目のときのままになってしまいます。

その文面にある「ファイル名」とは、登録されたCaption 名のことでしょうか？それは、きちんとOpenイベントが動いていないということでしょうか？

前回と同じことですが、全体の構成が見えていないので、よく分りません。もしかして、それぞれのフォルダにあるブックによって、そのボタン名が変るということでしょうか？もし、そうなら、不可能とは言いませんが、それは、ちょっと設計のアイデア倒れになりかねませんね。

この部分を、もう少し「詳しい言葉」で説明していただけませんか？
そうすれば、もう少し他の人も答えやすいかとも思います。

補足

Wendy02様　度々ご回答ありがとうございます。

＞Module1 の
＞＞Public strPath as Stringのこれは、常に変るのですか？
→はい、常に変わります。

＞全てのボタンにファイル名を入れるわけですよね。ボタンとファイル名の数が合っているのでしょうか？
→ループ処理でボタンの数を超えるとメッセージを表示する処理を記述しています。

＞その文面にある「ファイル名」とは、登録されたCaption 名のことでしょうか？
→そのとおりです。Caption名で判断させています。

＞それは、きちんとOpenイベントが動いていないということでしょうか？
→おそらくそうだとにらんでいるのですが・・・。

＞それぞれのフォルダにあるブックによって、そのボタン名が変るということでしょうか？
→はい。フォルダに格納されているブックを次に開くbookのボタンのCaption名に表示させています。

＞不可能とは言いませんが、それは、ちょっと設計のアイデア倒れになりかねませんね。
→設計の改善が必要なのでしょうか・・・。

＞この部分を、もう少し「詳しい言葉」で説明していただけませんか？
→申し訳ございません。コードをすべて貼り付けることに専念してしまいました・・・。

大変恐縮ですがご指導お願いいたします。