VBAで行の挿入を禁止する方法 part2 実用編

前回の「VBAで行の挿入を禁止する方法」を実用的なものに改良しましたので紹介します。

---

前回の記事について

前回は行の挿入を禁止する方法を説明しました。

www.excel-chunchun.com

実はというと、この方法は「シートの保護」と組み合わせるのが前提とした方法でした。

頭の良い方はお気づきかと思いますが、あのままでは固定した行より上に挿入・削除が出来るため、簡単にユーザーに破壊されてしまうものとなっていました。

昨日は偉そうに書いておいて、本当にすいません。

そこで、今回はシートの保護を使わずに指定した行以前に対しては挿入と削除を禁止するプログラムを書いてみます。

指定行以前の挿入・削除を禁止する

使用風景

ソースコード

'指定行以前の挿入・削除を禁止するコード
Option Explicit

Private rngCache As Range
Private Const LockEndRow = 5

'セル選択時にRange("A5")の示すインスタンスを捕まえておく
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set rngCache = Me.Range("A" & LockEndRow)
End Sub

'滅多にないがセル選択済みのまま保存した場合の対策
'ブック（シート）を開いた時にRange("A5")の示すインスタンスを捕まえておく
Private Sub Worksheet_Activate()
    Set rngCache = Me.Range("A" & LockEndRow)
End Sub

'編集(行挿入・削除）直前のA5の現在のアドレス
Private Property Get adrCache() As String
    '削除直後はrngA5Cacheの場合オブジェクトが崩壊している。
    On Error GoTo BrokenSkip
    'Debug.Print rngCache.Address & " : " & Me.Range("A" & LockEndRow).Address
    adrCache = rngCache.Address
    On Error GoTo 0
BrokenSkip:
End Property

'セル変更時の処理
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Columns.Count = Me.Columns.Count And _
        Target.CountLarge Mod Me.Columns.Count = 0 Then

        If adrCache <> Me.Range("A" & LockEndRow).Address Then
            '行の挿入前に戻す
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        End If
    End If
End Sub

解説（主な変更点）

(1) 固定したい行を定数にして、開発者が変更できるようにしました。

Private rngCache As Range
Private Const LockEndRow = 5

'・・・
    Set rngCache = Me.Range("A" & LockEndRow)
'・・・

(2) キャッシュしたRangeのアドレスを取り出す部分をプロパティに分離しました。

Private Property Get adrCache() As String
    On Error GoTo BrokenSkip
    Debug.Print rngCache.Address & " : " & Me.Range("A" & LockEndRow).Address
    On Error GoTo 0
    adrCache = rngCache.Address
BrokenSkip:
End Property

(3) アドレスを確認する比較式を修正しました。

        If adrCache <> Me.Range("A" & LockEndRow).Address Then

以上、短いですが今回はこれで終わりです。

参考資料

なんと@furyuteiさんが、かなり実用的なソースコードを書かれていました。

私のと違って、ユーザーが表のヘッダ部分(5行目)より上に行を挿入／削除したら動的にロックする範囲が変化する超スグレモノとなっております。

表の1列に制御用の文字を入れることで、判定しているようです。

その辺りの理解もしておく必要があるため、導入すハードルが多少上がるかもしれません。

目的に応じて上手く使い分けるなり、改良するなりすれば良いと思います。

まとめ

これで実務でも使えるようになりました。

エクセルでのシステム開発においては「データの開始行」は一つの重要なキーになってくるので、それを保証出来るというのは大変価値があると思います。

普段使っている方法とは大分乖離してきたので、まだまだ改良の余地はあるかもしれませんが、少しでも参考になればと思います。

2019/1/10 続編書きました。

www.excel-chunchun.com

---

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)