入力規則への貼り付けを禁止する
ツイッターで以下の発言をしたのをきっかけに作成したマクロVBAです。
入力規則は便利なんだけどさ、値貼り付け出来ちゃダメでしょ！
では、問題です。
シートに入力規則のリストを設定しました。
VBAでそのデータを使ったら規則外のデータが入っていてエラーが出てしまいました。
そこでセルのデータをチェックすることになりました。
どんなVBAを書きますか?
私ならVBAの前に使用者にエクセルの使い方を指導します。
とは言っても間違った入力は完全には無くならないので、
Range.Validation.Valueでチェックします。
せっかくなので、ちょっと書いてみました。
こんな感じなら出来ないことは無いけど、実用としては微妙な感じだと思います。
シートに入力規則のリストを設定しました。
VBAでそのデータを使ったら規則外のデータが入っていてエラーが出てしまいました。
そこでセルのデータをチェックすることになりました。
どんなVBAを書きますか?
とは言っても間違った入力は完全には無くならないので、
Range.Validation.Valueでチェックします。
こんな感じなら出来ないことは無いけど、実用としては微妙な感じだと思います。
入力規則への貼り付けを禁止するVBA
ブックモジュール（ThisWorkbook）
SelectionChangeイベントで、最初に1回だけ入力規則が設定されているセルのアドレスを保存しておきます。
（アドレスではなく、.Countで件数を入れても良いと思います。）
Changeイベントで、入力規則が設定されているセルのアドレスが変更されていないかを確認します。
さらに、Validation.Valueで入力規則に反する値が入れられていないかを確認しています。
入力規則が消されたり、規則外の値が入れられていた場合は、
メッセージボックスを出したうえで、Application.Undoで元に戻しています。
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でチェックするくらいが良いでしょう。
これらについては以下を参照してください。
予測外の動作が起こることも考えておく必要があると思います。
このようなイベントを入れることを考えるよりシート保護をした上で、
入力規則に反する値があるかどうかだけVBAでチェックするくらいが良いでしょう。
これらについては以下を参照してください。
入力規則（Validation）｜VBA入門
・Validationオブジェクト ・入力規則（Validation）の使用例 ・入力規則を設定しても無効データが入力されてしまう場合への対処
入力規則のリスト入力の妥当性判定・入力規則に反するデータかの判定Function ・入力規則のリスト入力の妥当性を判定Function ・最後に
同じテーマ「マクロVBA技術解説」の記事
・数値データ型の数値範囲 ・100桁数値の足し算VBA ・100桁数値の足し算VBAの使い方と結果 ・100桁数値の足し算VBAの解説
省略可能なVariant引数の参照不可をラップ関数で利用省略可能(Optional)なVariant引数を省略した場合、その引数は「参照不可」となります。この「参照不可」の状態について説明します。そして、これをあえて利用してワークシート関数のラップ関数を作成してみます。
ブックのいろいろな開き方（GetObject,参照設定,アドイン）・以下のサンプルVBAの実行確認 ・普通に開く ・別のインスタンスで開く ・GetObjectで開く ・参照設定で開く ・アドインで開く
入力規則への貼り付けを禁止するSelect Caseでの短絡評価（ショートサーキット）の使い方
・Select Caseの基本 ・短絡評価（ショートサーキット）について ・Ifステートメントのネストの書き換え ・Select Case での短絡評価（ショートサーキット） ・Select Case を使った短絡評価使用の是非
RangeオブジェクトのFor EachとAreasについて・正解は ・RangeオブジェクトをFor Eachで取得すると ・RangeオブジェクトをItemで取得すると ・RangeオブジェクトのFor EachとAreasの最後に
画像が行列削除についてこない場合の対処複数ブック・複数シートを次々に処理していくようなVBAにおいて、行削除や列削除をした時に、画像がセルの移動についてこない場合があります。プロパティは、「セルに合わせて移動やサイズ変更をする」になっている場合です。プロパティで「セルに合わせて移動する」になっているにも関わらず、画像が行列削除で移動しないといった現象が…
新関数SORTBYをVBAで利用するラップ関数を作成新関数はスピルに対応していてとても便利です。新関数はVBAからもWorksheetFunctionで利用できます。シート関数には件数制限があるので使用する場合は注意が必要ですが、その範囲内ならVBAでもとても便利に利用できます。
LAMBDA以降の新関数はVBAで使えるか・TEXTSPLIT関数 ・TEXTBEFORE関数,TEXTAFTER関数 ・TOROW関数,TOCOL関数 ・WRAPROWS関数,WRAPCOLS関数 ・VSTACK関数,HSTACK関数
数字（1～50）を丸付き数字に変換するVBA数値には丸付き数字があります。ただし1～50までしかありません。①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳㉑㉒㉓㉔㉕㉖㉗㉘㉙ChrW(12…
文字列のプロパティ名でオブジェクトを操作する方法・CallByName関数 ・Propertyについて ・文字列のプロパティ名でオブジェクトを操作するVBAサンプル
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 入力規則への貼り付けを禁止する
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。