Excel VBAでオブジェクト名を変更する方法

質問

Excelでシートを新規追加するマクロを作っています。この時、下記のようなコードでシート名を変更するのですが、同時にオブジェクト名も変更する事は可能でしょうか？

Sheets.Add
ActiveSheet.Name = "テスト"

なぜオブジェクト名も変更したいのかと言いますと、動的にイベントプロシージャを作成する際、下記のようなコードを記述してるのですが、シート名とオブジェクト名が一致してないとエラーが出てしまうんです。

Dim cdMoj as CodeModule
Dim Ln as Long

Set cdMoj = ThisWorkBook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
Ln = cdMoj.CreateEventProc("Click", "Command1")
cdMoj.InsertLines Ln + 1, "MsgBox ""ＶＢＡで追加したマクロです。"""

なにか良い方法がありましたらご教授下さい。どうぞ宜しくお願いします。

回答No.3

nishi6 · ベストアンサー

今度の質問での変更点は
（１）シートを追加して、その追加したシートにコントロールを追加する。
（２）追加するコントロールのコントロール名を指定した名前にしたい。
でしょうか。（２）の意味合いがよく分かりませんが・・・・"Command1"が使ってありますね。
（２）は行わなくてもエラーは出ないような気がしますが・・・。コントロール名をVB風にしたいなら話は別ですが。

追記と修正をしてみました。『'//////////』が変更箇所です。

（１）については、シートを追加し、シート名『テスト』にして、
　　　追加したのでVBE内の sht.Name を sht.CodeName に変更しています。
（２）については、ボタンを追加した後、コードを書き込む前に、コントロール名を変更しました。

Excel2000です。ご参考に。（コードを見て、前回の質問<321427>に対する回答を使いました。）

Sub Add_ButtonAndMacro()
　　Dim bk As Workbook
　　Dim sht As Worksheet
　　Dim rg As Range
　　Dim obj As OLEObject
　　Dim cdMoj As CodeModule
　　Dim Ln As Long

　　Application.ScreenUpdating = False
　　Set bk = ThisWorkbook
　　Sheets.Add '////////// シートを追加
　　Set sht = ActiveSheet
　　ActiveSheet.Name = "テスト" '////////// シート名を変更
　　Set rg = sht.Range("C4:G6")
　　With rg
　　　　Set obj = sht.OLEObjects.Add(classtype:="Forms.CommandButton.1", _
　　　　　　　　　　　　　　　　link:=False, displayasicon:=False, _
　　　　　　　　　　　　　　　　Left:=.Left, Top:=.Top, _
　　　　　　　　　　　　　　　　Width:=.Width, Height:=.Height)
　　End With
　　obj.Object.TakeFocusOnClick = False
　　obj.Object.Caption = "VBAで書いたメッセージ呼出し"
　　obj.Select '////////// 追加したボタンを選択
　　Selection.Name = "Command1" '////////// ボタン名を変更する

　　'////////// sht.Name→sht.CodeName に変更
　　Set cdMoj = ThisWorkbook.VBProject.VBComponents(sht.CodeName).CodeModule
　　Ln = cdMoj.CreateEventProc("Click", obj.Name)
　　cdMoj.InsertLines Ln + 1, "MsgBox ""ＶＢＡで追加したマクロです。"""

　　'VBE画面を閉める。
　　Application.VBE.MainWindow.Visible = False
　　rg.Select '////////// この状態ボタンを選択しているので、それを解除する
　　Application.ScreenUpdating = True
End Sub

お礼

前回に引き続き、詳細なご回答を頂き本当に感謝しております！ 今Excelで作成しているのは、ガントチャート作成マクロです。フォーム上で日付を指定すると、シート上に帯（オビ）を作成します。で、その帯をクリックすると編集できるような仕組みにしようと思い、奮闘していました。ここでのご回答がすごく参考になり、だいぶ出来上がってきました。本当にありがとうございます！

回答No.4

imogasi

補足頂いてお手数をお掛けしました。
●私の例では当初BookにはSheet１～Sheet４まで作成される
設定になっています（前提）。
ActiveWorkbook.Worksheets.Add.Name = "ａａａ"で追加されるシートはSheet5（Sheet（５）で（４）ではなかったと思う）であって、そのVBAProject１（Book1)のSheet5の（）内は
（aaa)となっていますが。
Sheet1(Sheet1)をSheet1(bbb)にしたいなら、
Worksheets("sheet1").Name = "bbb"
を実行すればそうなりますが。（テスト済み）これでは
ダメですか。
●「動的にイベントプロシージャを作成する」とは、
普通にイベントプロシージャを作ることに過ぎず、操作者
がクリックするとかのとき実行されるわけで、「動的」なのは
どのイベントプロシージャも動的なのでは。

お礼

ご回答ありがとうございます！ 動的なイベントプロシージャ作成という表現は、正しいかどうかよく分からず使っていました。今後またなにかヘンな表現があったらご指摘下さい(^^;

回答No.2

FlossenEngel

オブジェクト名はCodeNameプロパティ(値の取得のみ可能)で取得できますから
VBComponents(ActiveSheet.CodeName)
で指定できます。

お礼

ご回答ありがとうございます！CodeNameプロパティを使用してみたところうまく動作しました！オブジェクト名自体を変更しなくてもこれでうまくいきそうです。ありがとうございました。

回答No.1

imogasi

●Sub test01()
ActiveWorkbook.Worksheets.Add.Name = "aaa"
End Sub
または
Sub test02()
ThisWorkbook.Worksheets.Add.Name = "bbb"
End Sub
でaaa（またはbbb)というワークシート名のワークシートが加えられますが、こう言う質問ではないでしょうね。
aaa,bbbは既に同名のシートが存在しないこと。
●さて
VBAでのオブジェクトはWorkbook、Worksheet、Rangeなど
色々ありますが、ここでいう「オブジェクト」とは何を
指すのですか。コードモジュール？VBAProject？VBComponent？
「動的にイベントプロシージャを作成する」とはどういうニーズのことですか。

お礼

ご回答ありがとうございました！。 オブジェクトについてですが、Excel VBAのプロジェクトウィンドウを開くと「Microsoft Excel Objects」というフォルダがあって、その中に Sheet1 (Sheet1) のような表示がありますよね？ で、シート名を変更すると Sheet1 (aaa) のようになります。この状態だとシートの名前は aaa になっても、シートのオブジェクト名は Sheet1 のままです。このオブジェクト名も aaa に変更する方法についての質問でした。

「動的にイベントプロシージャを作成する」というのは、「クリックするとメッセージボックスを表示するボタン」の作成を、マクロで実行するための動作です。