[[20181213185628]]　『入力規則違反』（すもも）　ページの最後に飛ぶ

『入力規則違反』（すもも）

Hセルに入力規則を設定してます。
御存知の方もいるかと思いますが、
コピペの場合は規則を突破されちゃいます。
そこで入力規則以外はエラーで注意喚起するようにしたいのです。
色々検索かけてみたのですが、整数で制限かける等ありましたが、
整数での制限は掛けれないとします。
基本的にあるシートからコピペします。
コピペした時点で規則以外の文字はエラーメッセージが出るとか、
すぐに気付くように出来ませんか？
みなさんどうかよろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

ざっと思いついた方法を上げていくと

VBAでセルの選択やシートの切り替えなどのイベント発生時にコピー状態を強制的に解除する。

 Private Sub Worksheet_Activate()
    Application.CutCopyMode = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CutCopyMode = False
End Sub
もうちょっと頑張ればクリップボードの内容をセルのコピーから文字列のコピーに書き換えるというのもありかもしれません。

入力規則を使うのをやめて、VBAのイベントで検査を行う方法
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range
    For Each r In Target
        If Not IsNumeric(r.Value) Then
            MsgBox r.Value & "は整数ではないので受け付けられません！", vbExclamation
            Application.Undo
        End If
    Next
End Sub

入力規則は使うが、入力規則が破壊されたことを検知して巻き戻す方法
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Range
    For Each r In Target
        If r.Address = Range("A1").Address Then
            On Error Resume Next
            If Intersect(Cells.SpecialCells(xlCellTypeAllValidation), r) Is Nothing Then
                MsgBox "A1の入力規則の破壊を検知しました！", vbExclamation
                Application.EnableEvents = False
                Application.Undo
                Application.EnableEvents = True
            End If
            On Error GoTo 0
        End If
    Next
End Sub

なんてのは如何でしょう。
（名無し） 2018/12/13(木) 19:37

名無しさまありがとうございます。
3通りの案の中からまず1番目と2番目なのですが
コピペが出来ないのと入力規則を使わないとのことなので断念です。
3番目の案でやりたいと思います。
ただこのコードの場合ですとA1のみの対象になりますよね？
説明不足でしたが、シート1のH列に入力規則を設定してるシートがあります。
別のシート2のA列からJ列をコピーしてシート1のA列から値で貼り付けます。
その時にH列の規則違反の場合警告するようにしたいのですが、
このコードのA1をH:Hに変えればとやってみたのですが普通に貼り付け出来てしまいます。
H列を対象にするにはどのようになりますか？
時間がありましたらお願いします

（すもも） 2018/12/14(金) 09:01

 If r.Address = Range("A1").Address Then
を
 If r.Column = Range("H1").Column Then
とすると宜しいかと。

あまり検証しておらず思いつきで上げた案なので、一応検証はよく行ってから運用して欲しい。
（名無し） 2018/12/14(金) 16:15

早速試してみましたが、コピペの場合は入力規則で
設定したのも含めてすべて”入力規則の破壊を検知しました”となります。
本来値で貼り付けたかったので
そちらを試したところ今度は規則違反でもなんでも貼り付け可能になります。
う～ん・・やり方が間違ってるのか、もう少し検証してみますね。
名無し様ありがとうございました。

（すもも） 2018/12/14(金) 18:39

もう少し具体的に言ってくれないと何がダメだったのか分からないのですが、ちょっと考え方を変えて入力規則の状態を自動で把握してくれるようにしてみました。

コードを差し替えた後や入力規則を書き換えた後は、シートを切り替えるなりエクセルを再起動するなりしてくださいね。
キャッシュする処理をSelectionChangeイベントに組み込めば楽なんでしょうけど、ペーストのタイミングで起きるイベントとの発生順序が怖いのでやめておきました。

    Option Explicit

    'シートがアクティブになった時、入力規則の状態をキャッシュしておく
    Private CacheValidation As Range

    Private Sub Worksheet_Activate()
        Set CacheValidation = Cells.SpecialCells(xlCellTypeAllValidation)
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim r As Range
        For Each r In Target
            On Error GoTo ErrSkip
            '-----特定のセルを対象とする場合
            'If Not Intersect(r, Range("H2,H3,H5,H6")) Is Nothing Then
            '-----H列全てを対象とする場合
            'If r.Column = Range("H1").Column Then
            '-----シートアクティブ時に入力規則が入っていたセル全てを対象とする場合
            If Not Intersect(CacheValidation, r) Is Nothing Then
                If Intersect(Cells.SpecialCells(xlCellTypeAllValidation), r) Is Nothing Then
                    MsgBox r.Address(False, False) & "の入力規則の破壊を検知しました！", vbExclamation
                    Application.EnableEvents = False
                    Application.Undo
                    Application.EnableEvents = True
                End If
            End If
ErrSkip:
            On Error GoTo 0
        Next
    End Sub

    'キャッシュ状況の確認用　無くてもOK
    Private Sub DebugCache()
        Dim r As Range
        For Each r In CacheValidation
            Debug.Print r.Address(False, False) & " ";
        Next
    End Sub

（名無し） 2018/12/14(金) 20:33

コメント返信：

[ 一覧(最新更新順) ]

[[20181213185628]] 『入力規則違反』（すもも） ページの最後に飛ぶ

[[20181213185628]]　『入力規則違反』（すもも）　ページの最後に飛ぶ