エクセルの入力規則における条件設定

エクセルで入力規則を設定して入力内容に制限を付けたいですが、
条件によりリスト参照する場合と任意入力ができる場合とにしたいのですが
可能でしょうか？

可能であれば、その方法をご教示願います。

具体的には、A列が0の場合、Ｂ列にリスト（別シート）からの選択、1の場合には任意入力

A列　　　　　B列
0　　　　　　クリスマス（別シートからの選択）
0　　　　　　大晦日（別シートからの選択）
1　　　　　　ABC-1234（任意手入力）
0　　　　　　正月（別シートからの選択）
1　　　　　　XYZ-7890（任意手入力）

以上、よろしくお願い致します。

質問者からの補足コメント

• 該当シートには、下記マクロが設定済みです。
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r, rng As Range
  Set rng = Intersect(Target, Columns(1))
  If Not rng Is Nothing Then
  If ActiveSheet.ProtectContents = True Then
  ActiveSheet.Unprotect
  End If
  For Each r In rng
  If r.Value = "確定" Or r.Value = "キャンセル" Then
  （省略）
  これが上手く作動しない理由でしょうか？
  「Ｒ」の2重定義？
  
  以上、わかりにくく申し訳ございませんがよろしくお願い致します。

    補足日時：2016/12/24 17:19

No.2 ベストアンサー 回答者： ママチャリ 回答日時： 2016/12/24 17:20

「If r.Offset(0, -1).Value = 0 Then」の意味は、goda_nobitaさんの理解で正しいです。

でも、BY列はＪ列から67番目ですよ！！
しかし、「B列の1列前」なら分かりやすいので問題ないですが、「J列の67列後」では、何列か数えるのが面倒くさいです。そこで、次のようにすることをお勧めします。

If Cells(r.Row, "BY").Value = 0 Then

それから、「Range("B:B")」が2ヶ所あると思いますが、ここも直してますよね？

さらに、後出しで申し訳ないのですが、もうちょっと改善すべきところがあったので、コードを再提示しました。興味があったら改良点を確認して、何故、こうしたか考えてみて下さい。前回のコードを使っていれば、いづれ気づくことになると思いますが、その時に後悔しても遅いので…。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Range("J:J"), Me.UsedRange) Is Nothing Then Exit Sub
On Error Resume Next
For Each r In Intersect(Target, Range("J:J"), Me.UsedRange)
If Cells(r.Row, "BY").Value = 0 Then
r.Validation.InCellDropdown = True
r.Validation.ShowError = True
Else
r.Validation.InCellDropdown = False
r.Validation.ShowError = False
End If
Next r
End Sub

この回答へのお礼

ママチャリ様

ありがとうございます。

ありがとうございました。
上手く作動しました。

「Range("B:B")」が2ヶ所あると思いますが、ここも直してますよね？
→　直してました。

でも、BY列はＪ列から67番目ですよ！！
→　vlookup を使う時のカウント（当該列も含める）と混同してました。

新しいコードについては、後程、考えてみます。

本当に助かりました。
ありがとうございます。

少し早いですが、よいお年を！！！

お礼日時：2016/12/26 09:02

No.1 回答者： ママチャリ 回答日時： 2016/12/24 15:17

ご要望の要件を満たすには、動的に入力規則を変更する必要があると思います（わたしの知識の範囲では…）。

よって、マクロになってしまいます。
次のマクロを該当シートのシートモジュールに張り付けて下さい。
ちなみに、このマクロは設定済みの入力規則を動的に変更するだけです。よって、該当セルには通常通り、入力規則が設定されていなければなりません。

それから、1点、気になることがあります。
A列が１で、B列に任意の値が入力された後に、A列を０に変更した場合、B列は任意の値が入力されたままで特にエラーにはなりません。エラーはB列を変更しようとしたときに検知されることになります。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Dim r As Range
On Error Resume Next
For Each r In Intersect(Target, Range("B:B"))
If r.Offset(0, -1).Value = 0 Then
r.Validation.InCellDropdown = True
r.Validation.ShowError = True
Else
r.Validation.InCellDropdown = False
r.Validation.ShowError = False
End If
Next r
End Sub

この回答へのお礼

ママチャリ様

早速のご回答ありがとうございます。

補足にて教えて頂きたい点があるのですが・・・

If r.Offset(0, -1).Value = 0 Then
→　どのような意味合いでしょうか？

実際には、Ｊ列にて選択もしくは入力を行いたく
また、その為の判断は、Ａ列でなくBY列に文字としてあります。

教えて頂いたマクロの上記部分の
0=B列
-1=A列
と推測し、

If r.Offset(0, 68).Value = "○○" Then

68は、BY列がＪ列から68番目

としてみましたが、うまく作動致しません。

マクロの知識がありませんので見当違いの事を行っているかと
思いますが、よろしくお願い致します。

お礼日時：2016/12/24 16:06