VBA シートをコピー後、ボタンにマクロ登録

質問

お世話になっております。
Excel2003を使用しております。
分かる人にはすぐ分かる問題かもしれませんが、
良くわからないため教えて下さい。

シートを別ブックにコピーします。（ここまでは出来ています。）
元々あったボタンを選択し、
マクロを登録しなおそうとするも、上手く行かず…

Dim　StaticBook as　Workbook　と宣言し、
コピー先のブックを選別？できるようにしています。

そのため、シートをコピーした後に
With　StaticBook.ActiveSheet
　 .Shapes("Button 1").Select　　　　　
Selection.OnAction = StaticBOOK.Name & "!Sheet2.保存_Click"
.Shapes("Button 2").Select
Selection.OnAction = StaticBOOK.Name & "!Sheet2.再編集_Click"
.Range("A1").Select
End With
としてみたのですが、エラーが表示されます。

ButtonクラスのOnActionプロパティを設定できません。と表示されます。

元々、コピーすれば上手く動くとおもっていたのですが、
ボタンをクリックしたときの参照先が
コピー元のブックのままになっておりこれの改善方法がしりたいのです。

調べてみてもあまり良く分からず…
回答お願い致します！

回答No.2

WindFaller · ベストアンサー

こんにちは。

そのようなコードは書いたことがありませんし、きちんと考えたことがありませんでしたが、
コードをよく観ると

>StaticBOOK.Name & "!Sheet2.保存_Click"
このSheet2 は、オブジェクト名ですから、ただしく、Sheet2であればよいのですが、そうでない可能性が高いです。もちろん、シートに一意のオブジェクト名をつければよいはずですが、コピーをされるシートですから、名前付け管理していくのは面倒だと思います。それより、システム側のオブジェクト名を取るほうが早いと思います。例えば、このようなスタイルになるのではないでしょうか。
以下の　.CodeName というのが、シートのオブジェクト名になります。

'//
Sub Test1()
　　Workbooks("Origine.xls").Activate　'元のシートをアクティブにする
　　ActiveWorkbook.Worksheets("Sheet2").Copy Before:=Workbooks("Destin.xls").Sheets(1)

　　With ActiveSheet
　　　.Shapes("Button 1").OnAction = .Parent.Name & "!" & .CodeName & ".保存_Click"
　　End With
End Sub
'//

お礼

回答ありがとうございました！
解決いたしました＾＾

どうしても上手く行かない…と思い
出来たシートに手入力でマクロ登録をし、
その部分をマクロ記録してみて、
フルパスや、設定の違いを確認してみました。

確認したところ、
ブック名の両側に
''　が必要なことが判明しました。

なので、
.Shapes("Button 1").OnAction = "'"&.Parent.Name & "'!" & .CodeName & ".保存_Click"
というコードに書き換えたところ上手く動作してくれました＾＾

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

回答No.1

eden3616

型宣言をした変数（StaticBook）に
現在アクティブなブック（ActiveWorkbook）をセットする必要があります

Sub マクロの再登録()
'型宣言
Dim StaticBook As Workbook

'宣言したWorkbook変数にアクティブブックをセットします
Set StaticBook = ActiveWorkbook

'StaticBookのアクティブシートに対しての処理
With StaticBook.ActiveSheet
.Shapes("Button 1").Select
Selection.OnAction = StaticBook.Name & "!Sheet2.保存_Click"
.Shapes("Button 2").Select
Selection.OnAction = StaticBook.Name & "!Sheet2.再編集_Click"
.Range("A1").Select
End With
End Sub

お礼

回答ありがとうございました！
解決いたしました＾＾

補足

回答ありがとうございます。
元々,workbookとして設定し、
ブック名も設定してあり使えているのですが、
再度、ActiveWorkbookとして定義しなおさなければ
ならないのでしょうか？

実行してみたところ、上手く行きませんでした。
どうしても、同じようにエラーになっていまいます…

コピーした後のシートは、Sheet2とは限らないみたいで、
元々Sheet1~3は作成されていることがあり、
4以上も作成されている可能性があります。

試してみたところ、私の場合だと
コピー先にはSheet1,2,3が既にアリ、
コピーした後はSheet4(シート名)という設定になっていました。
このオブジェクト名？は取得できないのでしょうか…