エクセルのグラフの不動化（３）

質問

この質問は先ほど締め切った質問のご回答への追加の質問になります。

何度もすみません。
一応不具合の詳細パターンを検証しようと再度ファイルを開いてみたら同じエラーが発生しました。

原因は、ファイル保存時に空白セル以外に保護がかかる下記のVBAのせいだと思います。
（一部当方で追加した不細工なコードも入っています）

単純にこのVBAを削除すれば良いのですが、このVBAは今回の課題よりもはるかに優先すべきマクロで現行の全てのBookに入れて有りますので削除やシート操作時の無効化は検討外なのですが、何とかなりませんかね！？
無理ならこちらを諦めますのでご容赦！！

従来は上の何行目かを固定表示にしてその上部の固定部分に小さなグラフを置いていました。
入力者がノートPCなので入力時の表示範囲が相当に小さくなってしまうので、グラフタイトルが不要なグラフはタイトルを削除し、タイトルが必要になったら「グラフデサイン」（グラフの書式設定タブ）からタイトルを追加していました。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Sheets("成績表(提出)").Select
Range("A1").Select
Selection.ClearContents

Sheets("入力表").Select
Range("D2:L2").Select
Selection.ClearContents

Const MyPassword = "" 'パスワード（省略可）
Dim sh As Worksheet
On Error Resume Next
For Each sh In Worksheets
sh.Unprotect Password:=MyPassword
With sh.Cells
'全セルのロックを外す
.Locked = False
'定数が含まれているセルにロックを掛ける
.SpecialCells(xlCellTypeConstants).Locked = True
'数式が含まれているセルにロックを掛ける
.SpecialCells(xlCellTypeFormulas).Locked = True
End With
sh.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password:=MyPassword
Next
On Error GoTo 0

Sheets("入力表").Select
End Sub

回答No.1

HohoPapa · ベストアンサー

>「指定された値は境界を越えています」
このエラーが起きるのは、

Private Sub Workbook_BeforeSave
このイベント、つまり、
ブックを保存するときにシートを保護している一方で、

その後、保存したブックを開き、グラフの配置されたシート選び
適当なセルを選択すると、
Worksheet_SelectionChange
このイベントが走り、グラフの表示位置を変更しようとします。

つまり、シートが保護されているにもかかわらず
シート上のグラフの表示位置を変更しようとしているからです。

シートが保護されている場合であっても、
グラフの表示位置の変更など、
オブジェクトの変更を許す対応が考えられます。

ソースコードで言えば、以下です。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

　　Sheets("成績表(提出)").Select
　　Range("A1").Select
　　Selection.ClearContents

　　Sheets("入力表").Select
　　Range("D2:L2").Select
　　Selection.ClearContents

　　Const MyPassword = "" 'パスワード（省略可）
　　Dim sh As Worksheet
　　On Error Resume Next
　　For Each sh In Worksheets
　　　　sh.Unprotect Password:=MyPassword
　　　　With sh.Cells
　　　　　　'全セルのロックを外す
　　　　　　.Locked = False
　　　　　　'定数が含まれているセルにロックを掛ける
　　　　　　.SpecialCells(xlCellTypeConstants).Locked = True
　　　　　　'数式が含まれているセルにロックを掛ける
　　　　　　.SpecialCells(xlCellTypeFormulas).Locked = True
　　　　End With
'　　　　sh.Protect DrawingObjects:=True, Contents:=True, _
'　　　　　　Scenarios:=True, Password:=MyPassword
　　　　sh.Protect DrawingObjects:=False, Contents:=True, _
　　　　　　Scenarios:=True, Password:=MyPassword
　　Next
　　On Error GoTo 0

　　Sheets("入力表").Select
End Sub

お礼

朝一で試そうとして、ご回答と現行のコードの違いが分からず、もしかして質問のコードを間違って貼り付けた？と思いワードに新旧をコピペして比べてビックリ！
保護シート上のグラフだけ保護解除が、２行で出来るとは！
実際のBookで動作確認も完了しました。

何でもダメ元で聞いてみるべき（人の迷惑顧みず）と再度実感しました。