フォームのシート間参照

質問

以前シート参照のブック間コピーについて質問した者です。
たびたびお世話になります。

今、ブック(test1.xls)に3枚のシートとVBAがあります。3枚のシートは生成される書類の雛形になっています。VBAはユーザフォームを表示させるためのもので、ユーザによる入力が終わり、コマンドボタンが押されるとtest1の全てのシートを新しいブックにコピーし、入力されたデータに基づいてセルが埋められる仕組みです。

シート間の参照が[test1.xls]temp!A1となってしまう(tempは3枚のシートのうちの1枚)問題は、皆さんのアドバイスで回避することができました。しかし。

3枚のシート内でフォーム(チェックボックスやオプションボタンなど)を使用しているのですが、それらのプロパティ、「リンクするセル」にも同じ現象が起きてしまいます。

「リンクするセル」も新しいブックのシートを参照するようにしたいのですが、VBAで１つずつ設定していくしかないでしょうか…？フォームを大量に利用しているので、手作業は避けたいです。

皆様のアドバイスをよろしくお願いいたします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

前回の1796942　を読ませていただきました。
少し分りにくかったですが、様子はわかりました。それは、#1さんのご指摘のように、本来は、本物のテンプレートにすれば、そのような問題は発生しなかったはずです。

それは、おいおい考えていただくしかありませんが、臨時措置としては、マクロにはマクロでしかないのではありませんか？

1796942の#3さんのご意見に従って進めれば解決すると思います。
＞(2)参照となるブック名（.xls）は判っているので、自分のブック名に置換する

単純なマクロですが、このようにすればよいかと思います。

Sub AlternationFormula()
Dim c As Range
Dim LeftPrcLocal As Integer
Dim tmpFormula As String
Dim shp As Shape
For Each c In ActiveWorkbook.ActiveSheet.UsedRange
　If c.HasFormula Then
　 LeftPrcLocal = InStr(c.FormulaLocal, "]")
　 If LeftPrcLocal > 0 Then
　　 tmpFormula = Mid$(c.FormulaLocal, LeftPrcLocal + 1)
　　 If IsError(Evaluate(tmpFormula)) Then MsgBox "シート間の依存関係か式に問題があります。": Exit Sub
　　 c.FormulaLocal = "=" & tmpFormula
　 End If
　End If
Next c
　tmpFormula = ""
　For Each shp In ActiveWorkbook.ActiveSheet.Shapes
　 If shp.AutoShapeType = msoShapeMixed Then
　　　On Error Resume Next
　　　LeftPrcLocal = 0
　　　LeftPrcLocal = InStr(shp.DrawingObject.LinkedCell, "]")
　　　On Error GoTo 0
　　　If LeftPrcLocal > 0 Then
　　　tmpFormula = Mid$(shp.DrawingObject.LinkedCell, LeftPrcLocal + 1)
　　　shp.DrawingObject.LinkedCell = "=" & tmpFormula
　　　End If
　 End If
　Next shp
End Sub

お礼

お返事が遅くなり、大変申し訳ありません。

コードまで載せていただいて、ありがとうございました。テンプレートも試してみたいと思います。
とても参考になりました。

回答No.1

hana-hana3

ブックをテンプレートとして保存しておく方が余計な処理を省くことが出来ると思いますが？

お礼

お返事が遅くなって、大変申し訳ありません。

ファイル数は１つという要求だったため、テンプレートは避けました。テンプレートを使った方法も試してみて、提案してみたいと思います。
ありがとうございました。