【Excel365】入力規制(特定の文字を含む)

質問

「特定の文字以外は入力できないようにする」場合はリストから選択するようにすればいいのですが、「特定の文字を含む文言を入力できないようにする」場合は入力規制でどのように設定すればいいのでしょうか？

回答No.5

SI299792 · ベストアンサー

A2: データの入力規則、カスタム
=SUM(1-ISERROR(FIND(A5:A13,A2)))=0

条件付き書式、新しいルール。数式…
=SUM(1-ISERROR(FIND(A5:A13,A2)))

お礼

ご回答ありがとうございました。

できました!!
別シートのリストからの参照もOKでした。

こちらを採用させていただきます。

回答No.7

kkkkkm

現状ではA2にもともと何か入力されていた場合も空白になりますので元のデータを維持したい場合は

Range("A2").Value = ""
Range("A2").Select

の2行をやめて以下に変更してください。

Application.Undo

なお、このUndo以前にこのプロシージャでセルにデータの入力などは行わないでください。

お礼

何度もご回答ありがとうございます。

規制をかけるセルが複数になってしまったので、No.5の入力規制を適用させていただきます。

提示いただいたマクロは今後使用することが出てくると思います。

お手数をお掛けしました。

回答No.6

kkkkkm

回答No.4は制限文字がリストの順にメッセージで表示されますが、こちらはA2に入力した文字列で出てくる順にメッセージに表示します。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Range("A2").Address Then
Exit Sub
End If

Dim mRng As Range
Dim flg As Boolean, i As Long
Dim mStr As String, mCha As String
Dim ListStr As String

Application.EnableEvents = False

flg = False
mStr = "": mCha = "": ListStr = ""

For Each mRng In Sheets("Sheet3").Range("A5:A12")
ListStr = ListStr & mRng.Value
Next
For i = 1 To Len(Range("A2").Value)
mCha = Mid(Range("A2").Value, i, 1)
If InStr(ListStr, mCha) >= 1 Then
mStr = mStr & mCha & " "
flg = True
End If
Next

If flg = True Then
MsgBox "制限された文字 [ " & mStr & "] が含まれています", vbCritical
Range("A2").Value = ""
Range("A2").Select
End If

Application.EnableEvents = True

End Sub

お礼

何度もご回答ありがとうございます。

回答No.4

kkkkkm

リストがSheet3で全ての文字を表示するパターンです。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Range("A2").Address Then
Exit Sub
End If

Dim mRng As Range
Dim flg As Boolean
Dim mStr As String

Application.EnableEvents = False

flg = False
mStr = ""

For Each mRng In Sheets("Sheet3").Range("A5:A12")
If InStr(Range("A2").Value, mRng.Value) >= 1 Then
mStr = mStr & mRng.Value & " "
flg = True
End If
Next

If flg = True Then
MsgBox "制限された文字 [ " & mStr & "] が含まれています", vbCritical
Range("A2").Value = ""
Range("A2").Select
End If

Application.EnableEvents = True

End Sub

お礼

何度もご回答ありがとうございます。

回答No.3

kkkkkm

VBAの場合入力制限になった時に制限している文字が何かわからないので

MsgBox "制限された文字が含まれています", vbCritical

を

MsgBox "制限された文字 [ " & mRng.Value & " ] が含まれています", vbCritical

にしておくと親切かもしれません。

お礼

何度もご回答ありがとうございます。

別シートにリストを用意した場合はこちらの方がいいですね。

テストしてみましたが、見本のように2文字以上禁止文字が入っている場合は1つ目の文字しか表示されませんでした。

すべての文字を表示するのは無理でしょうか？

回答No.2

kkkkkm

VBAのChangeイベントで対応すると値貼り付けでも反応する感じです。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Range("A2").Address Then
Exit Sub
End If

Dim mRng As Range

Application.EnableEvents = False

For Each mRng In Range("A5:A12")
If InStr(Range("A2").Value, mRng.Value) >= 1 Then
MsgBox "制限された文字が含まれています", vbCritical
Range("A2").Value = ""
Range("A2").Select
Exit For
End If
Next
Application.EnableEvents = True

End Sub

お礼

再度のご回答ありがとうございます。

なるほど、マクロでも制御できるのですね。こちらの方がいいですね。

わがままを言って申し訳ありませんが、リストを別シート(設定用)に用意した場合「For Each mRng In Range("A5:A12")」の部分はどのようになりますか？

回答No.1

kkkkkm

やたら長いのですがとりあえず以下でいけると思います。

入力規則の

ユーザー定義
数式で

=NOT(OR(IFERROR(FIND(A5,A2),IFERROR(FIND(A6,A2),IFERROR(FIND(A7,A2),IFERROR(FIND(A8,A2),IFERROR(FIND(A9,A2),IFERROR(FIND(A10,A2),IFERROR(FIND(A11,A2),IFERROR(FIND(A12,A2),FALSE))))))))))

お礼

毎回ご回答ありがとうございます。

ん～、確かに長いですね・・・