えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

VBAで行の挿入を禁止する方法 part3 汎用化編

前回の「VBAで行の挿入を禁止する方法」が、行を自由に変更出来るようになりましたので紹介します。


前回の記事について

前回は行の挿入を禁止する方法ということで、モジュールの先頭にPrivate Const LockEndRow = 5と定義することで、シートの上から5行をユーザーに破壊されないような仕組みを作りました。

www.excel-chunchun.com

しかしながら、行数が固定されるというのは不便です。

変更するのはさほど手間ではありませんが、プロジェクトごとにソースコードが変わるのは避けたいところです。

なんとかして変動出来るようにする方法はないだろうか。

だからと言って、変にシートやモジュールを増やすのも避けたい・・・。

と、仕事に向かいながらちゅんちゅんしていたら、天界からアイディア降りてきました。キタ──ヽ('∀')ノ──!!

目的

それでは早速・・・と言いたいところですが、その前に初心に帰りましょう。

そもそも何故、行の挿入や削除を禁止する必要があったのでしょうか。

  1. ヘッダ部の位置を固定したい。(データ部の開始行を固定したい。)
  2. ヘッダ部の破壊を防ぎたい。
  3. データ部の先頭行への挿入を禁止したい。(データ先頭行の書式破壊を防ぎたい)
  4. 全ての行の挿入・削除を禁止したい。

1の要望が出る理由はいろいろ考えられますが、開発者の都合か、デザインの都合か、他の読み込みマクロとの互換性維持ですかね。

データの開始行の変動を認めるとなると、下手な書き方をしていると膨大な修正が現れそうです。

最初から定数で書いておけばいいんですが、それがなかなか実践出来ないんですよね。

2は当然っちゃ当然の話です。大前提であるヘッダが壊されたら元も子もありません。

3はエクセルの仕様のせいですね。データの先頭行で挿入するとヘッダ部の書式が付いてきて困るから禁止したいみたいな。

4はなんでしょうね。A4ページ単位で厳密にレイアウトの組まれた様式とか・・・?出力用の表を入力に使うなよって話ですが。ひとまず4は無視します。

1~2はテーブル使えば解決する話ですが、今回はテーブルを使わない前提で考えていきましょう。

テーブルならヘッダ部の削除は出来ませんし、データ先頭部の挿入によって書式がおかしくなることもありません。

そこで今回は2段階に分けて修正していきます。

  • 一つ目は、part2の行指定をノンコーディングで変えれるようにした方法(1~3を満たす方法)

  • もう一つは、さらにヘッダ部の行を自由に動かせるようにした方法(2と3を満たす方法)

改修

1~3の要件を満たすにあたって、ようは「ヘッダ部の行をシート上から取得」出来ればOKなわけです。

そしてヘッダ部には必ずオートフィルタがあります。

というわけで今回はオートフィルタを使って制限する範囲を動的に指定出来るようにしていきます。

ちなみに「テーブル」のオートフィルタは、シートのオートフィルタとは違うため使用できません。

一晩寝かせた割には別に凄くもなんともない発想でした。

1. データ部先頭行より上を固定する

概要

まずはデータ部の先頭行の位置を固定する化する例です。

f:id:Kotori-ChunChun:20190110212254p:plain

この変更のメリットは「ソースコードに手を入れなくても良い」という点でしょう。

  • ロックしたい行を変える時は、オートフィルタを解除すればOK
  • ロックする行が変わった後は、オートフィルタを設置すればOK

ソースコード

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

Private dataLockRng As Range
Private dataLockRow As Long

'オートフィルタを元に行とRangeをキャッシュ
Private Sub SaveCache()
    If Me.AutoFilter Is Nothing Then
        dataLockRow = 0
        Set dataLockRng = Nothing
    Else
        dataLockRow = Me.AutoFilter.Range.Cells(1, 1).Row + 1
        Set dataLockRng = Me.Range("A" & dataLockRow)
    End If
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call SaveCache
End Sub

Private Sub Worksheet_Activate()
    Call SaveCache
End Sub

'rngの行 エラー時0
Private Function GetRow(rng As Range) As Long
    On Error GoTo BrokenSkip
    GetRow = rng.Row
    On Error GoTo 0
BrokenSkip:
End Function

'セル変更時の処理
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 dataLockRow > 0 Then
            If GetRow(dataLockRng) <> dataLockRow Then
                '行の挿入前に戻す
                Application.EnableEvents = False
                Application.Undo
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub

解説

まずは監視セルをキャッシュする箇所が長くなったのでプロシージャに纏めました。

エクセルの仕様でワークシートにオートフィルタは一つしか定義できない。という条件があるのですが、それはさておきMe.AutoFilter Is Nothingとすることで、オートフィルタが定義されているかどうかの判定が可能です。

そしてdataLockRowにはロックする行番号(=データ部の先頭行)を、dataLockRngにはロックするセルをキャッシュさせます。

これにより、Changeイベントが発生したときに「dataLockRowは変わらないが、dataLockRngの行は変わる」という現象が起こります。この現象の解説についてはpart1のクイズを参照してください。

www.excel-chunchun.com

Private Sub SaveCache()
    If Me.AutoFilter Is Nothing Then
        dataLockRow = 0
        Set dataLockRng = Nothing
    Else
        dataLockRow = Me.AutoFilter.Range.Cells(1, 1).Row + 1
        Set dataLockRng = Me.Range("A" & dataLockRow)
    End If
End Sub

rngのプロパティを取得する部分を汎用関数化しました。

Private Function GetRow(rng As Range) As Long
    On Error GoTo BrokenSkip
    GetRow = rng.Row
    On Error GoTo 0
BrokenSkip:
End Function

挿入・削除されたかどうかの判定ですが、下記の式に変更しました。

例えば4行目で挿入された時、dataLockRngはA4からA5に変化し、dataLockRowには4が格納されているため不一致となり、元に戻される形です。

If GetRow(dataLockRng) <> dataLockRow Then
    '元に戻す
End If

2. ヘッダの削除とデータ先頭行への挿入を禁止する

概要

次がヘッダ部が削除されないように保護し、データ部の先頭行への挿入だけを禁止する方法です。

f:id:Kotori-ChunChun:20190110212456p:plain

これによって、ヘッダより上の行を自由に変更出来るようになりました。

データ部の先頭への挿入に関しては、Undoで禁止する他にも

データ部2行目の書式を挿入された行へコピーする

という手段も有効ではないかと考えます。

※ただし元に戻すが使えなくなります。

動作風景

  1. 基本動作のテスト
  2. オートフィルタが無い時のテスト
  3. 再設定した時のテスト

f:id:Kotori-ChunChun:20190111005322g:plain

ソースコード

'ヘッダの削除とデータ先頭行への挿入を禁止するコード
Option Explicit

Private headLockRng As Range
Private dataLockRng As Range
Private headLockRow As Long
Private dataLockRow As Long

'オートフィルタを元に行とRangeをキャッシュ
Private Sub SaveCache()
    If Me.AutoFilter Is Nothing Then
        headLockRow = 0
        dataLockRow = 0
        Set headLockRng = Nothing
        Set dataLockRng = Nothing
    Else
        headLockRow = Me.AutoFilter.Range.Cells(1, 1).Row
        dataLockRow = headLockRow + 1
        Set headLockRng = Me.Range("A" & headLockRow)
        Set dataLockRng = Me.Range("A" & dataLockRow)
    End If
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call SaveCache
End Sub

Private Sub Worksheet_Activate()
    Call SaveCache
End Sub

'rngの行 エラー時0
Private Function GetRow(rng As Range) As Long
    On Error GoTo BrokenSkip
    GetRow = rng.Row
    On Error GoTo 0
BrokenSkip:
End Function

'セル変更時の処理
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 Not headLockRng Is Nothing Then
            If (GetRow(headLockRng) = 0) Or _
                (Target.Row = dataLockRow And GetRow(dataLockRng) <> 0 And GetRow(dataLockRng) <> dataLockRow And WorksheetFunction.CountA(Target) = 0) Then
                '行の挿入前に戻す
                Application.EnableEvents = False
                Application.Undo
                Application.EnableEvents = True
            End If
        End If
    End If
    Call SaveCache
End Sub

解説

重要なのはIFによる判定部分ですね。

ヘッダ行の削除だけを検知する式は簡単です。

If GetRow(headLockRng) = 0 Then

削除範囲内にヘッダ行が含まれる時:Target.Row <= headLockRow And headLockRow <= Target.Row + Target.Rows.Count - 1 Andなどと追記したいところですが、そもそもヘッダ部が削除された時点でGetRow(headLockRng)は失敗して0が返るので必要ないって寸法です。

データ先頭行への挿入を検知する式はこうなります。

If (Target.Row = dataLockRow And GetRow(dataLockRng) <> 0 And GetRow(dataLockRng) <> dataLockRow And WorksheetFunction.CountA(Target) = 0) Then

挿入は選択開始行を基準に発生しますからTarget.Row = dataLockRow、削除された時以外にしたいからGetRow(dataLockRng) <> 0、行数が変化したことを検知したいからGetRow(dataLockRng) <> dataLockRow、元に戻すで1行目を復元する時のためにWorksheetFunction.CountA(Target) = 0をANDでつなぎました。

まとめ

今回は「VBAで行の挿入を禁止する方法」を扱いやすいようにしたものを説明しました。

導入は簡単。シートモジュールにコピペして、オートフィルタを設定するだけ♪

以前より更に実務で使いやすくなりました。

テーブルを使ったほうが良い場面もありますので、その辺は自分にあった方法を使ってください。

以上


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

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

プライバシーポリシー