ExcelVBA印刷範囲が変更されたことの検知

質問

環境
Windows7
Excel2010

印刷範囲が変更されたことを検知できる方法をお教え願います。

最終的に実現したいことは、「印刷範囲が変更されたら、その印刷範囲内のセルのみで特定の処理（=処理A）を行う」
現在は、シート上にコマンドボタンを配置し、ユーザーが印刷範囲を変更した後にそれを押すことで処理Aをさせています。

考えた方法としては、操作の際、ユーザーによる不意のセル選択変更操作があると考え、Selection_Chageイベントを利用することを含め、シートのほとんどのイベントプロシージャに処理Aを記述しました。
しかし当然ながら、網羅できませんでした。（印刷範囲を変更しただけではどのイベントも動かない）
クラスモジュールにも挑戦していますが、扱いきれていないこともあり、結局きっかけがないと変更を検知することはできないとの結論に至っています。

何かよい方法はありますでしょうか。
ご教授をお願いいたします。

回答No.3

real beatin（@realbeatin） · ベストアンサー

＃２です。

[数式]タブ[名前の管理]で確認できますが、
[印刷範囲]は、各シート毎に「Print_Area」という[名前]で管理されています。
（●[名前の管理]からでも[印刷範囲]を変更できる●ということでもありますが。）

この「Print_Area」という[名前]を
　　=IFERROR(Sheet1!Print_Area,"")
というワークシート上の数式で監視します。
IFERROR()関数を使うのは、
[印刷範囲]が設定されていないとか[クリア]された場合に対応させる為です。

すべてのシートの[印刷範囲]それぞれを、
上記の数式で参照します。

'印刷範囲監視'シートを新規に設けて、
（実使用時はシートごと非表示にします）
上記の数式群をシートの数だけ設定しておきます。

これで、[印刷範囲]の変更があった場合は、必ず、
'印刷範囲監視'シートにて、Worksheet_Calculate()イベントが
発行されるようになります。

Worksheet_Calculate()イベントは、[印刷範囲]の変更時以外にも、
[印刷範囲]内のセルを編集するだけでも発行されますから、
Worksheet_Change()イベントと連携して、
'印刷範囲監視'シートに記録した直近の変更時の[印刷範囲]と
比較して、[印刷範囲]の変更のタイミングを取得します。

●メニューから[印刷範囲]を変更する場合●だけならば、
_Change()イベントは無縁なのですが、
●セル範囲の[挿入]や[削除]●によって変更されることもあります。

全体を通して、ひとつひとつの技術は難しいものはないのですが、
様々な状況を想定して、組み合わせて設計していくのが難しいです。
例えば、
[挿入]した時は_Change()イベントの後に_Calculate()イベント、
[削除]した時は_Change()イベントの後に_Change()イベント、
と順序が逆だったり、
[印刷範囲]の外縁を削除した後に[元に戻す]操作をした場合に
_Change()イベントで捉えるセル範囲は
[印刷範囲]の外になってしまうこととか、、、
結構細かい対応が必要でした。
そういう意味では、一応書いてはみましたが、
開発の１段階めを終えた、という程度ですし、
こちらでの検証が十分とは言えないです。
もし採用して貰えるのだとしても、少し時間を掛けて、
動作の確認をしながら、場合によって手を加えることになるかも知れません。
組み合わせが難しいという意味では、
今回は、すべてThisWokrbookモジュールに一纏めにかいてありますが、
> シートのほとんどのイベントプロシージャに処理Aを記述しました。
各々Worksheetモジュールのイベントプロシージャとか、
他に処理Aを実行するプロシージャで[印刷範囲]を変更する記述がある場合とか、
組合わせには注意しないとなりません。

ですので、開発段階では差し当たり、他にＶＢＡを使っていないブックで、
テストしたりデバッグしたり試してから、他のものと組み合わせるように
した方がいいと思います。
部分的には、_Change()イベントと_Change()イベントの発行を押えれば、
私が書いたものは機能しませんから、他のプロシージャでは
　Application.EnableEvents = False
　[印刷範囲]を変更処理
　Application.EnableEvents = True
のようにしておけば、問題になることは無いです。

こちらで対策したポイントは、この説明文中で●●で括って強調しています。
●ページレイアウト／改ページプレビューからの変更●も
一応、対応出来ていると思います。

理解しておいて欲しいのは、
ExcelにはＶＢＡが手出しできない処の[デザインモード]の存在です。
[デザインモード]移行中はイベントの発行が止まりますし、
変数に使用するメモリも解放されてしまいます。
つまり[デザインモード]中は機能させることが出来ません。
今回意識したのは[デザインモード]後に
静的変数の再設定等、復旧処理が不必要な方法を採ることです。
その為に、直近の変更時の[印刷範囲]記録への参照に
[名前の定義]をシートの数だけ使用しています。

ブックに適用した[名前の定義]を利用すれば、
シート名を変更しても機能するように書くことも出来るのですが、
今回は省略しています。
＃２のまま使う場合はブックの保護をした方が、
トラブルを事前に回避できます。
　＝＝＝　初期設定　　／　　Sub Prep()
を実行すると、'印刷範囲監視'シートの挿入、上記の数式設定、
[名前の定義]等、自動で設定されるように書きました。

＃ Function の所は明らかに冗長ですが、やり出すとキリが無いので。

細かい技術的な補足や、スクリプトへのコメント等は、
量的な理由で書くことができませんが、
何かお訊ねあれば、補足欄にでも書いてみて下さい。

以上です。

お礼

放置しておりました。大変申し訳ありません。
ありがとうございました。

回答No.2

real beatin（@realbeatin）

こんにちは。
とりあえず書いたので上げてみます。

' ' ＝＝＝　ThisWokrbookモジュール　＝＝＝
' ' ＝＝＝　宣言部
Private wstWatch As Worksheet
Private flg As Long
Private Const S_LIST_NAME = "印刷範囲監視"
Private Const S_REF_P_AREA = "PrtArea"
' ' ＝＝＝　イベント
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
　　If Sh.Name <> S_LIST_NAME Then Exit Sub
　　If flg And 2 Then flg = flg Or 1 Else flg = 1
　　Application.OnTime Now, "ThisWorkbook.Rcv"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sRPA As String
Dim sPA As String
Dim flgE As Boolean
　　If Sh.Name = S_LIST_NAME Then Exit Sub
　　sRPA = Range(S_REF_P_AREA & Sh.Name).Value
　　If sRPA = "" Then Exit Sub
　　flgE = Intersect(Sh.Range(sRPA), Target) Is Nothing
　　sPA = Sh.PageSetup.PrintArea
　　If sPA <> "" Then flgE = flgE And Intersect(Sh.Range(sPA), Target) Is Nothing
　　If flgE Then
　　　　flg = flg Or 2
　　　　Application.OnTime Now, "ThisWorkbook.Rcv"
　　　　Exit Sub
　　End If
　　If flg And 2 Then flg = flg Xor 2
　　Set wstWatch = Sh
End Sub
' ' ＝＝＝　印刷範囲が変更された場合の処理（変更されなかった場合の処理）
Private Sub Rcv()
Dim sPrevPA As String
　　If flg = 1 Then
　　　　If GetWksPAreaHasChanged(sPrevPA) Then　'　印刷範囲が変更されたならば

　　　　　　' ' ◆◆◆　【 処理A 】　◆◆◆　ココ！！

　　　　　　' ' 印刷範囲が変更されたシート ＝ wstWatch（As Worksheet）
　　　　　　' ' 旧　印刷範囲 ＝ sPrevPA（As String）
　　　　　　' ' 新　印刷範囲 ＝ wstWatch.PageSetup.PrintArea（As String）

　　　　　　' '　　↓　確認用のメッセージ（実践では削除）
　　　　　　MsgBox wstWatch.Name & vbTab & "印刷範囲が変更されました" & vbLf _
　　　　　　　　& vbTab & sPrevPA & vbLf _
　　　　　　　　& vbTab & "↓" & vbLf _
　　　　　　　　& vbTab & wstWatch.PageSetup.PrintArea, vbInformation
　　　　End If
　　End If
　　flg = False
　　Set wstWatch = Nothing
End Sub
Private Function GetWksPAreaHasChanged(sPrevPA) As Boolean
Dim wks As Worksheet
Dim sName As String
Dim sPArea As String
　　If wstWatch Is Nothing Then
　　　　For Each wstWatch In Me.Worksheets
　　　　　　sName = wstWatch.Name
　　　　　　sPArea = wstWatch.PageSetup.PrintArea
　　　　　　With Range(S_REF_P_AREA & sName)
　　　　　　　　sPrevPA = .Value
　　　　　　　　If sPrevPA <> sPArea Then
　　　　　　　　　　GetWksPAreaHasChanged = True
　　　　　　　　　　Application.EnableEvents = False
　　　　　　　　　　.Value = sPArea
　　　　　　　　　　Application.EnableEvents = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　End With
　　　　Next
　　Else
　　　　sName = wstWatch.Name
　　　　sPArea = wstWatch.PageSetup.PrintArea
　　　　With Range(S_REF_P_AREA & sName)
　　　　　　sPrevPA = .Value
　　　　　　If sPrevPA <> sPArea Then
　　　　　　　　GetWksPAreaHasChanged = True
　　　　　　　　Application.EnableEvents = False
　　　　　　　　.Value = sPArea
　　　　　　　　Application.EnableEvents = True
　　　　　　End If
　　　　End With
　　End If
End Function
' ' ＝＝＝

' ' ＝＝＝　初期設定
Private Sub Prep()
Dim wksM As Worksheet
Dim wks As Worksheet
Dim s As String
Dim cn As Long
　　Application.EnableEvents = False
　　On Error GoTo AddWks_
　　Set wksM = Sheets(S_LIST_NAME)
　　On Error GoTo 0
　　With wksM
　　　　.Cells.ClearContents
　　　　.Visible = xlSheetVisible
　　　　.Select
　　　　.Range("A1:C1").Value = Split("シート名 印刷範囲 ↓印刷範囲監視用関数↓" & vbLf & "※監視不要シートは数式を消す")
　　　　cn = 1
　　　　For Each wks In Worksheets
　　　　　　s = wks.Name
　　　　　　cn = cn + 1
　　　　　　.Cells(cn, "A") = s
　　　　　　.Cells(cn, "B") = wks.PageSetup.PrintArea
　　　　　　AddOrMod_NameOfBk S_REF_P_AREA & s, "='" & S_LIST_NAME & "'!$B$" & cn
　　　　　　If wks.Name <> .Name Then .Cells(cn, "C") = "=IFERROR('" & s & "'!Print_Area,"""")"
　　　　Next
　　　　ActiveWindow.DisplayFormulas = True
　　　　.Range("A1:C1").EntireColumn.AutoFit
　　　　MsgBox "印刷範囲監視用シートの作成完了しました。" & vbLf & vbLf _
　　　　　　& "このシートを非表示にします。" & vbLf _
　　　　　　& "一覧にあるシートの内、印刷範囲監視から除外したいシート" & vbLf _
　　　　　　& "がある場合は、このシートを再表示して" & vbLf _
　　　　　　& "除外したいシート名の行のＣ列の数式を消去してください。", vbInformation
　　　　.Visible = xlSheetHidden
　　End With
　　Application.EnableEvents = True
Exit Sub
AddWks_:
　　With Worksheets.Add(Before:=Worksheets(1))
　　　　.Name = S_LIST_NAME
　　　　.Range("C1:C2").Merge
　　End With
　　Resume
End Sub
Private Sub AddOrMod_NameOfBk(ByVal sName As String, ByVal sRef As String)
　　If Application.Evaluate("ISREF(" & sName & ")") Then
　　　　Me.Names(sName).RefersTo = sRef
　　Else
　　　　Me.Names.Add sName, sRef
　　End If
End Sub
' ' ＝＝＝

お礼

放置しておりました。大変申し訳ありません。
ありがとうございました。

回答No.1

imogasi

正面からとらえるイベントはエクセルＶＢＡにないと思う。
下記は苦肉の策で、うまくいかないかもしれないが。
以下エクセル２０１３の例。
ーー
Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox ActiveSheet.Name & " " & ActiveSheet.PageSetup.PrintArea
End Sub
のようにWorkbook_BeforePrintイベントがあるが、上記でとらえられるのは、
印刷する前に、ページレイアウトタブで印刷範囲を選び、次に印刷範囲の設定を選び、
設定しているセル範囲が出る。設定しなおさないと範囲指定が変わらないようだ。
これが使えるなら、上記で表示されるＳｈｅｅｔ2 $A$2:$C$10（仮定例）という文字列と
質問者の考える普通の、印刷セル範囲Ｓｈｅｅｔ２　$A$2:$C12と比べれば
範囲を変えたか判定できるのではないか。
ただシートの印刷の選択範囲を変えて印刷操作に入るだけではPrintAreaが変わらないようだ。
するとActiveSheet.PageSetup.PrintAreaの部分が空白になってしまうようだ。
＞ユーザーによる不意のセル選択変更操作
の後の印刷は、どのような操作で行われていますか？
ーー
範囲を変えたとき印刷と切り離してイベントが走るように設計変更できないですか。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub
でSelectionchangeしたセル範囲がわかる。すると
質問者の考える普通の、印刷セル範囲Ｓｈｅｅｔ２　$A$2:$C12（仮定例）と比べれば
範囲を変えたか判定できる。そのサインをPublic変数で立てて、BeforePrintのイベントで
そのサインを見て、＞その印刷範囲内のセルのみで特定の処理（=処理A）を行う」
ーー
１つボタンを設けて、印刷範囲設定を必ずそのボタンを押すことでやらせて、そのイベントで特定の処理（=処理A）を行うのをくみこむとか。

お礼

放置しておりました。大変申し訳ありません。
ありがとうございました。