【Excel VBA】入力規則を強制！貼り付け時の入力チェックでデータを完全保護する方法
こんにちは！大阪市を拠点に活動している『縁紡ぐ』の稲垣です。
SWELLを使ったホームページ制作や、Excel、ACCESS、RPAなどのシステム開発を行っています。
また、Excel、Word、Outlookの研修や、情報セキュリティ研修も行っています。身近なITの相談相手になりたいと思っています。
お気軽にお問い合わせください。
入力規則は貼り付けで無視される？
VBAを使っている時、入力規則を設定しているのに貼り付けることができちゃった
稲垣コピペをすると入力規則を設定していてもできるか、改めて確認してみよう！
手入力での動作確認
A1のセルに「1～10」の整数が入力できる入力規則が設定されている
稲垣ここに数字で11を入力してみよう！
入力規則に従った値の動作も確認
稲垣次は数字で10を入力してみよう！
手入力だとちゃんと入力規則に従って動くことが確認できた
入力規則外の値をコピペで貼り付ける
稲垣それではA1のセル以外に入力されている、11をA1にコピペしてみるよ
そうなの！これ貼りついてしまうの。
値の貼り付けをしても、入力規則が残ったまま貼り付いちゃって困っています
入力規則が設定されていも、コピペで値の貼り付けをしても入力規則にしたがっていない値が貼りつきます
Excelの開発会社であるMicrosoft社は、ユーザーが意図的に値を貼り付けたり、通常のコピー＆ペーストであれば、入力規則を含めて貼り付けることを想定しているものと思われます。
稲垣VBAでシートをユーザーインターフェースで使うときには、この問題は解決する必要があるんだ
🔍 入力規則が効かないケース
| 操作 | 入力規則の動作 |
|---|---|
| 手入力 | ✅ チェックされる |
| 「値のみ貼り付け」 | ❌ 無視される |
| 書式/数式の貼り付け | ❌ 無視される |
解決策：VBAで貼り付けを強制チェック
稲垣ワークシートのWorksheet_Changeイベントを利用し、貼り付け後に入力規則をチェックするマクロを記述します。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim bInvalid As Boolean
'変更されたセルをチェック
For Each cell In Target
If Not cell.Validation Is Nothing Then
If Not cell.Validation.Value Then
MsgBox "入力規則違反です！", vbExclamation
bInvalid = True
Exit For
End If
End If
Next cell
'入力規則違反があれば元に戻す
If bInvalid Then
Application.EnableEvents = False '★重要★
Application.Undo
Application.EnableEvents = True
End If
End Sub1. Worksheet_Changeイベント
稲垣セルの内容が変更されるたびに自動実行されるので、これを利用しているよ
入力規則のチェック
稲垣入力規則のチェックは、下記のコードで行っています
If Not cell.Validation.Value Thenセルに設定された入力規則に違反しているか判定している。
3. 無限ループ防止（超重要！）
稲垣元の値に変更した時に無限ループしなよう、イベントの発生を下記の部分で防いでいます
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True数式の貼り付けもブロック
稲垣書式・数式の貼り付けもブロックして、さらに強化することができます
数式貼り付け防止を追加したコード
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim bInvalid As Boolean
'変更されたセルを1つずつチェック
For Each cell In Target
'--- 入力規則チェック ---
If Not cell.Validation Is Nothing Then
If Not cell.Validation.Value Then
MsgBox "入力規則違反です！セル: " & cell.Address, vbExclamation
bInvalid = True
Exit For
End If
End If
'--- 数式貼り付けチェック---
If cell.HasFormula Then
MsgBox "数式の貼り付けは禁止されています！セル: " & cell.Address, vbCritical
bInvalid = True
Exit For
End If
Next cell
'違反があれば元に戻す
If bInvalid Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Subまとめ
- たった1つのマクロで「値のみ貼り付け」も完全ブロック
EnableEventsの制御で安全に運用- 応用すれば書式や数式の貼り付けも制限可能
稲垣シートを使ってのデータ入力を行う際に、ぜひ参考にしてみてくださいね
稲垣
- Excel、ACCESSでのシステム開発が得意
- ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
- 自家焙煎するほどのコーヒー好き
- 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
- 保有資格 Kintoneアソシエイト、日商簿記検定２級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
- 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
- お客様にとって本当に良いことかを第一に考える人
コメント