エクセルVBAで、シートに条件を付けて処理させたい

質問

エクセルVBAで
『コピー用』シートと『あああ』シートと『いいい』シートと『ううう』シートがあり、
『あああ』シートと『いいい』シートと『ううう』シートの全ての情報を
『コピー用』シートにコピーしてまとめるようにしました。
マクロを処理するには、
『あああ』シートと『いいい』シートと『ううう』シートに設置したボタンを押すと処理する。
ここまでは実現することができました。

私がやりたいことは、
シートの内容を変更したのに、そのボタンを押し忘れてしまい、
そのまま『コピー用』シートを使用してしまうことを避けるために、

【「あああ」シートか「いいい」シートか「ううう」シートが変更されている】
かつ
【マクロを処理させるボタンを押していない】

この場合に、「コピー用」シートをアクティブにしたときに
マクロ処理をするかしないかを選択させるダイアログを表示させる。

シートが変更されていない
または
マクロを処理させるボタンを押した後にシートを変更していない

この場合は、「コピー用」シートをアクティブにしたときに
マクロ処理をするかしないかを選択させるダイアログは表示させない。

といった処理を可能にしたいです。

詳しくは↓のサンプルページを参考にして下さい。
http://blog-imgs-17.fc2.com/s/k/s/sksfiosjdijf34/sampledesu.htm

ボタンを押して全シートをコピーする処理のコードは↓のような感じで作りました。
ダイアログの処理は調べて作ってみましたが、ちゃんと動作しませんでした。

Microsoft Excel Objectsのsheet1（sheet2とsheet3とsheet4は空白）に

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim keizoku As Integer
keizoku = MsgBox("内容が変更されていますが、「あああ」シートか「いいい」シートか「ううう」シートのマクロ処理開始のボタンをまだ押していないためマクロ処理がされていません。処理しますか？", vbYesNo)
Select Case keizoku
Case vbYes
Application.EnableEvents = False
Call macro1
Application.EnableEvents = True
Case vbNo
myMsg = ""
Case Else
myMsg = ""
End Select
End Sub

を入れ、
次に、標準モジュールのModule1に

Sub macro1()
Worksheets("あああ").Range("B2:AD51").Copy _
Destination:=Worksheets("コピー用").Range("B1:AD50")

Worksheets("いいい").Range("B2:AD51").Copy _
Destination:=Worksheets("コピー用").Range("B51:AD100")

Worksheets("ううう").Range("B2:AD101").Copy _
Destination:=Worksheets("コピー用").Range("B101:AD200")

Application.ScreenUpdating = False
For RowCount = 400 To 1 Step -1
If Application.WorksheetFunction.CountA(Worksheets("コピー用").Rows(RowCount)) = 0 Then
Worksheets("コピー用").Rows(RowCount).Delete
End If
Next
Application.ScreenUpdating = True
Application.OnKey "a"
End Sub

を入れました。

詳しくは↓のサンプルページを参考にして下さい。
http://blog-imgs-17.fc2.com/s/k/s/sksfiosjdijf34/sampledesu.htm

アドバイスをお願いいたします。

回答No.1

imogasi · ベストアンサー

忙しい読者や回答者のことを考えて、質問をもっと短く、また要点に絞り、要領よくかけないのか。
たとえば家庭教師に要求するように、そっくり放り投げて、回答を頼んでないか。
ーー
３つのシートがあってコピーするときは、一部のシートに変更が無くても、３つにシートをコピーしてしまうのはどうか。
細かく言えば無駄なのだが、よい方法が見つからないとか、自分のプログラム作成力との兼ね合いで、そちらのほうを選ぶのも良いのでは。
（一部セルの値の変化なのだが、個別のセルのコピーは面倒なので、１シート全体をコピーするようなこともその例に当たる）
ーー
そもそも各シートの変更を捕まえる（それも前回コピー後に限る）仕組みはどうしたのか。
（If ActiveWorkbook.Saved = True Thenはブック全体のようなので不適か）
この点も含め、まずロジック（処理の仕組み）が、どういうのが良さそうか（どういうVBAの機能を使うべきか）、から質問しては。（もちろん自分でやってみることは大切なのだが）
初心者はこのへんの大切さを重視せず、少ない経験の中から、知っている範囲の我流でやろうとする。すると仕組みやコードは変に複雑になったり、難しくなる場合がある。
そして、そのコードは質問者が作り、行き詰った点に絞って質問すべきだ。
ーー
変化を捉えるのは（Change）イベントしかない
対象シートが３つあるので、Thisworkbookのシートのイベントを使う。変化したシートが判別できるから。
イベントプロシージュア以外で、変化の結果を使うので、フラグを、それもパブリック変数で、設けざるを得ない。
シートごとにフラグが必要かと思う。
このフラグはONで在ればコピーを必要とし、コピーしたら、コピーの処理の最後で、そのシートの分のフラグをOFFにする。
また上記Workbook_SheetChangeイベントは、どんな変化を拾うか。質問者のニーズに合うかどうか。
十分はテストして無いし、十分良いかどうかわからないが
ーー
Sheet1,Sheet2の２つで
Sheet1にボタン１つ設ける。
Private Sub CommandButton1_Click()
If chg1 = "y" Then
MsgBox "その後Sheet1変更されている"
chg1 = ""
ElseIf chg2 = "y" Then
MsgBox "その後Sheet2変更されている"
chg2 = ""
Else
MsgBox "変更なし"
End If
End Sub
標準モジュールに
Public chg1, chg2
（上記はボタンをクリックして、照会しただけで、処理は済んだことになるが。本来はSheet1などの変更を「コピー」用シートに
何衛反映する処理が必要だが略。）

補足

ありがとうございます。さっそく試してみました。

Microsoft Excel ObjectsのSheet1(コピー用)を

Private Sub CommandButton1_Click()
If chg1 = "y" Then
MsgBox "その後Sheet1変更されている"
chg1 = ""
ElseIf chg2 = "y" Then
MsgBox "その後Sheet2変更されている"
chg2 = ""
Else
MsgBox "変更なし"
End If
End Sub

に変更し、

標準モジュールを

Public chg1, chg2
Worksheets("あああ").Range("B2:AD51").Copy _
Destination:=Worksheets("コピー用").Range("B1:AD50")

Worksheets("いいい").Range("B2:AD51").Copy _
Destination:=Worksheets("コピー用").Range("B51:AD100")

Worksheets("ううう").Range("B2:AD101").Copy _
Destination:=Worksheets("コピー用").Range("B101:AD200")

Application.ScreenUpdating = False
For RowCount = 400 To 1 Step -1
If Application.WorksheetFunction.CountA(Worksheets("コピー用").Rows(RowCount)) = 0 Then
Worksheets("コピー用").Rows(RowCount).Delete
End If
Next
Application.ScreenUpdating = True
Application.OnKey "a"

End Sub

に変更するということでしょうか。
エラーになってしまうのですが、何が間違っているのでしょうか。

回答No.2

pascal3141

(1)一つのエクセル上の各シートデータを、マスターシートにコピーするのであれば、エクセルを立ち上げたときに自動的に３つのシートからマスターシートにコピー処理するようにすればＯＫです。こうすれば各シートに変更があろうが無かろうが、マスターシートは、常に最新の状態になります。
(2)さらに、マスターシートのところで、コピーボタンをつけて同じようにできる様にしておけば、作業時にも最新の状態にできます。
この２つを考えてみたらどうでしょうか。

補足

ありがとうございます。
すみません、自分の実現したいことと少し違うようです。

(1)エクセルを開く
　　↓
(2)シートの内容をいろいろ変更
　　↓
(3)マクロの処理の開始
　　↓
(4)変更ヶ所をみつけてまた変更
　　↓
(5)マクロの処理の開始を忘れる
　　↓
(6)コピー用のシートを印刷（更新されていないシート）
　　↓
(7)エクセルを閉じる
　　↓
(8)数ヵ月後にエクセルをまた開き同じ作業をする
　　↓
(1)エクセルを開く
　　↓

(5)が起きても、(6)のような状態にさせないための対策を実現する方法を知りたいです。