VBA エクセルで挿入はできなくして 行コピーしてコピーしたセルの挿入はしたいです。

Question

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = Me.Columns.Count And _
    Target.CountLarge Mod Me.Columns.Count = 0 And _
    Target.Row = Selection.Row Then

    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox ("行の削除・追加不可です。")
    Exit Sub

End If

If Target.Rows.Count = Me.Rows.Count And _
    Target.CountLarge Mod Me.Rows.Count = 0 And _
    Target.Column = Selection.Column Then

    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    MsgBox ("列の削除・追加不可です。")
    Exit Sub

End If

l_end:

End Sub

行の挿入は禁止できますがコピーして挿入もできなくなってしまいます。
良い方法はないでしょうか？

Answer 1

Application.CutCopyModeはどうでしょうか

if(Application.CutCopyMode) がtrueのとき、切り取りorコピーの何れかが行われていると判定できます

Comments

[wada3]

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = Me.Columns.Count And _
Target.CountLarge Mod Me.Columns.Count = 0 And _
Target.Row = Selection.Row Then

'切り取りorコピー以外の挿入の場合は操作を取り消す
If Application.CutCopyMode = 0 Then
Application.EnableEvents = False
'ユーザーが最後に実行した操作を取り消します。
Application.Undo
Application.EnableEvents = True
MsgBox ("行の削除・追加不可です。")
End If

End If

l_end:

End Sub

上記のコードで挿入か切り取りorコピーでの挿入の制御が可能になりました。ありがとうございます

[pig_vba]

ふむ。その処理では少し無駄がありますね。
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CutCopyMode then Exit Sub

として、「一番最初にコピー状態だったらさっさと終了する」としましょう。処理速度と可読性、安全性の向上が見込めます。

この考え方を「早期リターン」といいます。暇があればぜひ調べてみてください

[wada3]

ありがとうございます。下記のように修正しました。
また、今のコードだと挿入と削除両方ともできなくなっています。
コピーなしの挿入はしたくないですが削除はしたいですが、そのようにするにはどうしたらよいでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)

'対象セルの列数＝シート全体のセルの列数の比較(行全体が対象か確認)
'対象セルの総数がシート全体のセルの列数で割り切れるかどうか(対象が複数行ある可能性を考慮)
If Target.Columns.Count = Me.Columns.Count And _
Target.CountLarge Mod Me.Columns.Count = 0 And _
Target.Row = Selection.Row Then

'コピー状態だったら終了する
If Application.CutCopyMode Then Exit Sub

Application.EnableEvents = False
'ユーザーが最後に実行した操作を取り消します。
Application.Undo
Application.EnableEvents = True
MsgBox ("行の追加不可です")

End If

End Sub

[pig_vba]

軽く調べてみましたが、削除そのものを検知するイベントはありませんでしたが、こちらのコードを利用すれば削除かどうかの判定はできそうです。弊環境に移植してみたところ挿入では反応しませんでした
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1290411357

ちなみに、私が言ってた早期リターンはこういうことです

Private Sub Worksheet_Change(ByVal Target As Range)

'コピー状態だったらなにもしない
If Application.CutCopyMode Then Exit Sub

'対象セルの列数＝シート全体のセルの列数の比較(行全体が対象か確認)
'対象セルの総数がシート全体のセルの列数で割り切れるかどうか(対象が複数行ある可能性を考慮)
If Target.Columns.Count = Me.Columns.Count And _
Target.CountLarge Mod Me.Columns.Count = 0 And _
Target.Row = Selection.Row Then

Application.EnableEvents = False
'ユーザーが最後に実行した操作を取り消します。
Application.Undo
Application.EnableEvents = True
MsgBox ("行の追加不可です")

End If

End Sub

[wada3]

なるほどです。削除のほうも見てみます。