VBA技術解説
入力規則への貼り付けを禁止する

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-09-17

入力規則への貼り付けを禁止する


ツイッターで以下の発言をしたのをきっかけに作成したマクロVBAです。

入力規則は便利なんだけどさ、値貼り付け出来ちゃダメでしょ!

では、問題です。
シートに入力規則のリストを設定しました。
VBAでそのデータを使ったら規則外のデータが入っていてエラーが出てしまいました。
そこでセルのデータをチェックすることになりました。
どんなVBAを書きますか?

私ならVBAの前に使用者にエクセルの使い方を指導します。
とは言っても間違った入力は完全には無くならないので、
Range.Validation.Valueでチェックします。

せっかくなので、ちょっと書いてみました。
こんな感じなら出来ないことは無いけど、実用としては微妙な感じだと思います。

VBA マクロ 入力規則 貼り付け 禁止


入力規則への貼り付けを禁止するVBA

ブックモジュール(ThisWorkbook)



Option Explicit

Private pWsName As String
Private pValiCnt As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  '最初の1回だけ、頻繁に動くと邪魔になる可能性もあるので
  If pWsName <> Sh.Name Then
    On Error Resume Next
    pWsName = Sh.Name
    pValiCnt = ""
    pValiCnt = Cells.SpecialCells(xlCellTypeAllValidation).Address
  End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  'イベント抑止
  Application.EnableEvents = False
  On Error Resume Next

  Dim rngVali As Range
  Set rngVali = Sh.Cells.SpecialCells(xlCellTypeAllValidation)

  '事前に保存してある入力規則数とチェック
  If rngVali.Address <> pValiCnt Then
    MsgBox "貼り付けしちゃダメ!"
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
  End If

  'もともと入力規則がない場合
  '元々はあって0になった場合は上でエラー判定される。
  If Err.Number <> 0 Then
    Application.EnableEvents = True
    Exit Sub
  End If

  '今回変更されたセル範囲の中で入力規則があるセルのみ
  Set rngVali = Intersect(Target, rngVali)
  If Err.Number <> 0 Or rngVali Is Nothing Then
    Application.EnableEvents = True
    Exit Sub
  End If

  '入力規則に反した値があるかをチェック
  Dim rng As Range
  For Each rng In rngVali
    If Not rng.Validation.Value Then
      MsgBox "違う値貼り付けダメ!"
      Application.Undo
      Application.EnableEvents = True
      Exit Sub
    End If
  Next

  'イベント再開
  Application.EnableEvents = True
End Sub



SelectionChangeイベントで、最初に1回だけ入力規則が設定されているセルのアドレスを保存しておきます。
(アドレスではなく、.Countで件数を入れても良いと思います。)
Changeイベントで、入力規則が設定されているセルのアドレスが変更されていないかを確認します。
さらに、Validation.Valueで入力規則に反する値が入れられていないかを確認しています。

入力規則が消されたり、規則外の値が入れられていた場合は、
メッセージボックスを出したうえで、Application.Undoで元に戻しています。
Application.Undoは、ユーザーが最後に実行した操作を取り消します。

※入力規則のセルを他の入力規則のセルに貼り付けた場合は禁止されません。


このようなイベントは実運用では危険が伴います。
予測外の動作が起こることも考えておく必要があると思います。

きちんとシート保護さえしておけば、入力規則が消えることはありませんので、
このようなイベントを入れることを考えるよりシート保護をした上で、
入力規則に反する値があるかどうかだけVBAでチェックするくらいが良いでしょう。
これらについては以下を参照してください。
入力規則(Validation)|VBA入門
入力規則は、Validationオブジェクトになります、ワークシート範囲の入力規則を表します、入力規則は、シート上で設定しておいた方が良い場合が多いですが、マクロVBAで設定する必要も出てきます。入力規則を設定するには、Validationオブジェクトを使います。
入力規則のリスト入力の妥当性判定
入力規則のリスト入力は、Excelの機能の中でも頻繁に使われる機能です。入力規則の目的として、リスト内のデータのみの入力に限定したいと言う事になりますが、コピペで値の貼り付けを行うと、どんな値でも入力出来てしまいます。このエクセルの仕様はとても不便なものですが仕方ありません。




同じテーマ「マクロVBA技術解説」の記事

VBAでWindowsMediaPlayerを使い動画再生する
VBAでWEBカメラ操作する
VBAで電光掲示板を作成
ユーザーに絶対に停止させたくない場合のVBA設定
列幅・行高をDPI取得しピクセルで指定する
VBAでWMIの使い方について
アクティブシート以外のWindowを設定できるWorksheetView
LSetとユーザー定義型のコピー(100桁の足し算)
省略可能なVariant引数の参照不可をラップ関数で利用
ブックのいろいろな開き方(GetObject,参照設定,アドイン)
入力規則への貼り付けを禁止する


新着記事NEW ・・・新着記事一覧を見る

import文(パッケージ・モジュールのインポート)|Python入門(9月24日)
例外処理(try文)とexception一覧|Python入門(9月23日)
リスト内包表記|Python入門(9月22日)
Pythonの引数は参照渡しだが・・・|Python入門(9月21日)
lambda(ラムダ式、無名関数)と三項演算子|Python入門(9月20日)
関数内関数(関数のネスト)とスコープ|Python入門(9月18日)
関数の定義(def文)と引数|Python入門(9月18日)
組み込み関数一覧|Python入門(9月17日)
辞書(dict型)|Python入門(9月16日)
入力規則への貼り付けを禁止する|VBA技術解説(9月16日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ