Excelで行が挿入したときに、自動的に別シートにも挿入したい

質問

お世話になります。

例えばシート１の11行目に行が挿入された場合、
シート２の11行目にも自動的に行が追加されるような
マクロを作りたいのですが、どうも検索で情報が
見つかりませんでした。

ご存知のかたがおられましたら、よろしく
お願いいたします。

回答No.5

Wendy02 · ベストアンサー

こんにちは。

ご質問は、簡単そうにみえて、あまり簡単ではありませんね。イベントには違いはないのですが、通常のイベントとは違います。インスタンスが必要です。

実は、今回、私は、いろんなところを手抜きをしているし、おまけにクラスモジュールを使わなかったのですが、たぶん、以下で可能なはずです。ボタン自体は、本来は1つで足りるはずですが、これも手抜きです。（もし、クラス・インスタンスが必要なら、作り直します。そのほうが安全性は高いです。）

行全体と、セル単独の挿入があり、また、右クリックメニューと、標準メニューの挿入の行からの命令がありますから、それらのコマンドバーボタンにイベントをつけてあげればよいわけです。

SettingInsertButton()　が、設定用ですが、後は、ThisWorkbook_Openで、自動的に設定するようにしてありますから、起動と同時に設定されます。ブックの区別はしてありませんので、必要なら、ThisWorkbook.Sheet1 というようにしてください。なお、Sheet1はオブジェクト・ネームで、シート名ではありませんから、お間違えないように。このコードは、シートモジュールや、標準モジュールに書くことはできません。（一部を除いて）

'<ThisWorkbook のみ>
Option Explicit

Private WithEvents myBtn As Office.CommandBarButton
Private WithEvents myBtn2 As Office.CommandBarButton
Private WithEvents myBtn3 As Office.CommandBarButton

Sub SettingInsertButton()
'ボタンの設定
　 With Application
　　　Set myBtn = .CommandBars("Cell") _
　　　.FindControl(, 3181)
　　　Set myBtn2 = .CommandBars("Row") _
　　　.FindControl(, 3183)
　　　Set myBtn3 = .CommandBars("Worksheet Menu Bar") _
　　　.Controls("挿入(&I)").Controls("行(&R)")
　 End With
End Sub

Private Sub myBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim myRng As Range
　'セルの挿入のイベント/コマンドバーを含む
　Set myRng = Selection
　Application.EnableEvents = False
　Call myProcedure(myRng)
　Application.EnableEvents = True
End Sub
Private Sub myBtn2_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
　Dim myRng As Range
　'行全体の挿入のイベント
　Set myRng = Selection
　Application.EnableEvents = False
　Call myProcedure(myRng)
　Application.EnableEvents = True
End Sub
Private Sub myBtn3_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
　Dim myRng As Range
　'メニューの挿入の行からのイベント
　Set myRng = Selection
　Application.EnableEvents = False
　Call myProcedure(myRng)
　Application.EnableEvents = True
End Sub

Private Sub myProcedure(arg As Range)
Dim myRngAdd As String
　'シート1でない場合は、除外
　If ActiveSheet.Name <> Sheet1.Name Then Exit Sub
　myRngAdd = arg.Address(0, 0)
　'行挿入
　If arg.Address(0, 0) = arg.EntireRow.Address(0, 0) Then
　'行全体
　　 Sheet2.Range(myRngAdd).EntireRow.Insert
　Else
　'セルの範囲
　　 Sheet2.Range(myRngAdd).Insert
　End If
End Sub
Private Sub Workbook_Open()
'起動時の設定
　 Call ThisWorkbook.SettingInsertButton
End Sub

回答No.9

imogasi

#7です。そのお礼に関して
＞行が挿入されたということをマクロが知る
ためには、何かのイベントを使わなければいけない
のではないでしょうか？
挿入されたことを知る必要はある場合とない場合があるでしょう。
（１）プログラマが、たとえば挿入するVBAコードをプログラムで書いたときには、その後に、プログラムで、挿入されたかどうかのチェックプログラムをいれたりしません。「バッチ的な処理」（意味わかるかな）の場合の話です。
（２）イベントドリブン型の処理の場合は、行が挿入されたことを捕らえなければなりませんが、質問では、そういう内容に取れなかったです。
挿入操作をマクロに置き換える風な内容に質問内容をとりました。
　また色々な操作をしたことを、イベント的に捕らえるには、できないか初学者には、VBAでは捕らえられません。たとえば書式を変えたとき知らせろ
とか、列幅を広げたら知らせろ、なんていうのは、作るのが、難しいでしょう。
＞Test01()という関数はどのようにすれば呼ばれる
のでしょうか？
質問のこの部分の文字通りでは、Test1と、プログラムの中の必要な箇所に
書けばよいです。
全般的に当初質問からずれてきているように思うので、この辺で。

お礼

ご返答、ありがとうございました。

どうも、私の手にはあまるような、、、

回答No.8

Wendy02

こんばんは。Wendy02です。

＞ちょっと難しいので、必死で解読しております。

ところで、私の書いた <Class1> というのは、標準モジュールと派別に、Classモジュールを挿入して、そのClass1に貼り付けることですが、それは大丈夫ですか？マニュアルなどにはあまり載っていないと思います。私としては、ご要望にかなっているか、どうかなのですが。

行の挿入を知るトリガーは、セルのChangeのトリガーではできません。

Changeイベントも、SelectionChangeイベントも、それはワークシートの中だけのものです。行の挿入のように、ワークシートのフレームを越えてイベント付けをすることは、通常は出来ません。だから、そのコマンド自体に着目して、そのコマンドボタンを一度Classを通して、そのコマンドボタンにイベント付けをするのです。

お礼

ご返答、ありがとうございました。

どうも、私の手にはあまるような、、、

回答No.7

imogasi

＃３です。
その補足に関して。
私のはChangeイベントの中に挟むプログラムではありません。
Changeイベントの中にはさんでいませんか。
そうではなくて
Sub Test01()

End sub
にはさむ部分です。

＞教えていただいた
コードだと永遠と行挿入を自動的に繰り返して
しまいます。。。
私の意図していた通りだと、繰り返しはありえないと思います。
ニーズについては、私ので適していないかは別にして、
再確認をお願いします。

お礼

何度もご返答、ありがとうございます。

確かに、Changeイベントにはさんでいるから、永遠に
行挿入を繰り返しているのでしょうね。

しかし、行が挿入されたということをマクロが知る
ためには、何かのイベントを使わなければいけない
のではないでしょうか？

Test01()という関数はどのようにすれば呼ばれる
のでしょうか？

不勉強なため、基本的なことがわかっていないの
かもしれません。

どうかよろしくお願いいたします。

回答No.6

Wendy02

こんにちは。

#5で書いたWendy02です。

あまりにも、#5の内容が手抜きで、みっともないような気がしたので、書き直して、Classに取り付けました。ブックのコマンドボタンに取り付けたインスタンスですから、取り付けたブックにしか適用されません。

設定は、InsertRow_For_ClassEventですから、ThisWorkbook のOpenイベントや、Sheet_Activate イベントから、呼び出せばよいと思います。以下では、Auto_Openイベントを付けてあります。

'<標準モジュール>
Option Explicit
Sub InsertRow_For_ClassEvent()
'クラスイベント設定用
Static myClass1(0 To 2) As Class1
Dim myCtrl As CommandBarButton
Dim cl As Variant
　With Application
　　 Set myClass1(0) = New Class1
　　 Set myClass1(0).Btn = .CommandBars("Cell") _
　　　.FindControl(, 3181)
　　 Set myClass1(1) = New Class1
　　 Set myClass1(1).Btn = .CommandBars("Row") _
　　　.FindControl(, 3183)
　　 Set myClass1(2) = New Class1
　　 Set myClass1(2).Btn = .CommandBars("Worksheet Menu Bar") _
　　　.Controls("挿入(&I)").Controls("行(&R)")
　 End With
End Sub

Sub Auto_Open()
　Call InsertRow_For_ClassEvent
End Sub

'<Class1>
Private WithEvents myBtn As CommandBarButton

Public Property Set Btn(ByVal myNewBtn As CommandBarButton)
　　Set myBtn = myNewBtn
End Property

Private Sub myBtn_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
Dim myRng As Range
Dim myRngAdd As String
　'シート1でない場合は、除外
　If ActiveSheet.Name <> Sheet1.Name Then Exit Sub
　Set myRng = Selection
　myRngAdd = myRng.Address(0, 0)
　'行挿入
　If myRng.Address(0, 0) = myRng.EntireRow.Address(0, 0) Then
　'行全体
　　 Sheet2.Range(myRngAdd).EntireRow.Insert
　Else
　'セルの範囲
　　 Sheet2.Range(myRngAdd).Insert
　End If
End Sub

お礼

Wendy02さん

詳しいご解説ありがとうございました。
なにぶんくVBAに詳しくないもので、、、
必死です。＾＾；；；

前にいただいたアドバイスだと、永遠と行を勝手に
追加してしまうので、それだとまずいということ
ですよね。

Wendy02さんに書いていただいたコードは
ちょっと難しいので、必死で解読しております。

回答No.4

masa_019

No.2に補足です
書き忘れましたが、

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Range(Target.Address).Insert Shift:=xlDown
End Sub

これだけだと、シート1を編集するだけで、
シート2に行が挿入されるので、条件によって、
動作を規制するコードを付け足さないと
使い物にはなりません。
あくまで、シート1の行挿入をトリガーにして
シート2に行を挿入できかを試しただけです。

お礼

masa_019さん

補足、ありがとうございました。

その規制するコードがわからないのですが、、、
＃５の回答を参考になんとか、、、、、
と思っております。

回答No.3

imogasi

下記操作をして、マクロの記録をとれば判ります。
ツール-マクロ-新しいマクロの記録。
シートタブ部分をShiftキーを押しながら、マウスで複数シートクリックし
行挿入を一番上（左）のシートで行う。
終わればマクロの記録終了。
マクロの記録を見る。
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
Selection.Insert Shift:=xlDown
Arrayの中を相対化するには
下記のX,YをX=InputBox（"シート名は？"）と関数で聞いて、指定。
３つ以上でも同じ。
Sub Macro2()
x = "sheet1"
y = "sheet3"
Sheets(Array(x, y)).Select
Sheets("Sheet1").Activate
Selection.Insert Shift:=xlDown
End Sub

お礼

imogasiさん

詳しいご回答ありがとうございました。
教えていただいた方法でできました。

補足

ごめんなさい。
できたと思ったのは間違いで、教えていただいた
コードだと永遠と行挿入を自動的に繰り返して
しまいます。。。

回答No.2

masa_019

No.1の方もおっしゃっていますが、シート1とシート2を、
同時に選択した状態で、行を挿入すればよいと思いますが、
マクロで、ということなので、ワークシートのチェンジイベントを使って、

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Range(Target.Address).Insert Shift:=xlDown
End Sub

あまり、検証してませんが、
こんな感じで、出来そうです。

お礼

masa_019さん

ご回答ありがとうございました。

なるほど、チェンジイベントで拾えるわけですね。
わかりました。
やってみます。

回答No.1

shabushabu

マクロではないとダメなのでしょうか？

他のシートに同時に行を挿入したい場合は複数の
シートをCtrlで選択し、行の挿入等を実行すれば
できます。

ご存知でしたすいません。

お礼

shabushabuさん

ご回答ありがとうございました。

いえ、知りませんでした。＾＾；

この操作をマクロの記録で記録すればいいですね。