VBAを使って指定のVBEに書き込むにはこんな風に書きます。
Sub WriteTest()
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
.InsertLines 7, vbTab & "Debug.Print Now()"
End With
End Sub
これは Module1 の7行目に「Debug.Print Now()」と書き込むコードです。
これを利用して以下のように指定のVBEにコマンドボタンの操作を追加することもできます。
Sub WriteTest()
n = Worksheets("Sheet1").OLEObjects.Count
With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
.InsertLines 1, vbTab & "Sub CommandButton" & n & "_Click"
.InsertLines 2, vbTab & "msgbox ""under construction"""
.InsertLines 3, vbTab & "End sub"
End With
End Sub
n の位置にはいくつめのコマンドボタンなのかを代入します。上記のように
n = Worksheets("Sheet1").OLEObjects.Count
という風に書けば、追加されたActiveXオブジェクトの数を数えることができます。そのため、ActiveXオブジェクトが１つの場合は１、２つの場合は２になります。よって、これを利用すればどんどんActiveXオブジェクトを足していき、なおかつInsertLinesを利用することで自動的にそのオブジェクトの操作を追加することができます。
できますが、ただし、たくさんのボタンを追加し、そのボタンがほぼ同じ動きをするのであれば、こんなことをするよりはシート上のセルをボタンのように使うほうがより簡単ですしバグもでません。ボタンを押すたびにVBEにコードががんがん追加されるのは、少しでも狂えばその後のボタンも動かなくなりますし、あまり良くないと思います。なので、この目的では極力使わないほうがいいでしょう。
代わりに使うべきなのがボタンのように動くセルです。
セルをボタンのように使いたい場合、次のようにコードを書いてください（ボタン風セルがシート１にあるのであればシート１のコードエディターに、シート２にあるのであればシート２のコードエディターに貼り付けてください）。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Target.Row
b = Target.Column
If a >= 1 And a <= 5 And b = 1 Then
MsgBox "やっほー"
ElseIf a >= 1 And a <= 5 And b = 2 Then
MsgBox "はろー"
End If
End Sub
これは貼られたシート上のA1 - A5 までのセルが選択されると（クリックされると）「やっほー」というメッセージボックスを出し、シート上のB1 - B5 までのセルが選択されると（クリックされると）「はろー」というメッセージボックスを出すプログラムです。つまり、A1-A5のセルがクリックすると「やっほー」というメッセージを表示するボタンになり、B1 - B5 のセルがクリックすると「はろー」というメッセージを表示するボタンになったんだとも考えることができます。これをボタンがわりに使うほうが楽です。外見も色々自分で設定できますし。
さらに、一緒に使うと結構役立つ最終行の場所を取得するコードも記載しておきます。
lastRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
この変数lastRowはシート１のA1から出発して、データの入力されているセルのうち、一番下のセルの「行（row）」を取得します。間にデータの入っていないセルがあるとそこでセル数のカウントが止まってしまうので注意。
テーブルとかセル風ボタンの管理では、これを使えばテーブルの一番下の行が取得できるので便利です。
例えばこんな風にします。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
a = Target.Row
b = Target.Column
lastRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
If a >= 1 And a <= lastRow And b = 1 Then
MsgBox "やっほー"
ElseIf a >= 1 And a <= lastRow And b = 2 Then
MsgBox "はろー"
End If
End Sub
すると
A1から「データの入力されているセルのうち一番下のセル」まで
B1から「データの入力されているセルのうち一番下のセル」まで
のボタン風セルの操作の管理が一括してできます。
ちなみに、エクセルでテーブルを使う場合、テーブルをVBAやなんかを駆使して自作するよりエクセル2007以降には最初からついてるテーブル機能を使うほうが断然いいです。自分はテーブルを自作しようとして無駄に時間がかかったのでみなさんは注意してください。。
