Excel:入力規制で選択された値によって他のセルの入力規制リストを変更する方法

プルダウンリストで選択した値に対して、別のプルダウンを連動させようと
INDIRECTを使ったのですが、再選択をすると、最初に連動した内容が残ったままになります。

再選択をした際、連動した別のセルの入力規制のリストを変更する方法はないかと調べ、
VBAを利用する方法があり、まねしようとしてもうまくいきません。

VBAを利用しない方法が希望ですが、どうやら難しいようですので、
VBAの知識があまりなくてもできる方法を教えていただけると助かります。

よろしくお願いいたします。

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

• 丁寧にありがとうございます！
  誤字などに関しては、意味が通じていたので全く気になりませんでした。
  
  [種類]入力セル、[商品分類]入力セルの[入力規則][リスト]の連動
  については問題なく設定されております。
  
  さっそく試してみたのですが、エラーがでてうまくいきませんでした。
  同じエラーについて、cj_moverさんが他の質問者に答えていましたので
  拝見したのですが、難しく私には手におえそうにありませんでした。
  
  このエクセルファイルは私が作成したものではなく
  複数のシートと連携しているようですが、
  このシートのこの部分に関しては、いじっても大丈夫な部分とみて
  他の方に修正をお願いされている状態です。
  
  なお、プルダウンリストは、
  A1からD1まで名前の定義をしており、
  A1からD5までは「選択範囲から作成」で保存してあります。
  
  よろしくお願いいたします。

  
  No.4の回答に寄せられた補足コメントです。 補足日時：2015/03/25 10:52

No.1 ベストアンサー 回答者： cj_mover 回答日時： 2015/03/24 16:41

こんにちは。

理解が至っていないかも知れませんが、
> ... 再選択をすると、最初に連動した内容が残ったままになります。
[種類]を変更した時に、対応した[商品分類]セルの値を消去したい、
ということでしたら、それは、Excel一般機能では無理ですから、
完全にＶＢＡ/マクロでやるしかない範疇です。
質問者さんがやろうとしていることは、ＰＣプログラム作り、ですね。

２つの[入力規則][リスト]の連動については、現状のまま、
ただただ、
「[種類]入力セルを変更したら、[商品分類]入力セルを空にする」
ということを、
ＶＢＡで実現させれば十分であろうと思われます。

スクリプト■（１１行）
Private Sub Worksheet_Change(ByVal Target As Range)　'　▼この行から
Dim myTgt As Range
Dim c As Range
　　Set myTgt = Intersect(Target, Range("C2:C163"))　'　◆要指定１◆Range("種類を選択するセル範囲をＡ１形式で")
　　If myTgt Is Nothing Then Exit Sub
　　Application.EnableEvents = False
　　For Each c In myTgt
　　　　c.Offset(0, 2).ClearContents　'　◆要指定２◆.Offset(行オフセット, 列オフセット)
　　Next
　　Application.EnableEvents = True
End Sub　'　▲この行まで

手順０■上記スクリプトに対して、
　　　　質問の説明文で不足した情報（「何が何処にあるか」）について、
　　　　そちらで、２点、修正を加えてください。
　　　　◆要指定１◆には、
　　　　　[種類]入力セルとして[入力規則][リスト]が設定されたセル範囲すべてを
　　　　　"C2:C163"のように""ダヌルクオートで括ったセル参照を
　　　　　実際にお使いの条件に合わせて指定、修正します。
　　　　◆要指定２◆には、
　　　　　[種類]入力セルからみて[商品分類]入力セルの相対的な位置関係を指定します。
　　　　　.Offset(0, 2)と指定してあるのは、「同じ行の２列右」という意味です。
　　　　　実際にお使いの条件に合わせて指定、修正します。
手順１■修正済の上記スクリプトを（１１行全文、過不足なく）コピーしておく
手順２■対象のExcelシートの下部、シートタブを右クリックして
　　　　[コードの表示]を押す。
手順３■ＶＢＡの編集画面が現れたら、そもまま、[Ctrl]+[V]キーを押して、
　　　　上記にコードを貼り付ける。
手順４■ＶＢＡの編集画面[閉じる]ボタンでExcel画面に戻る。
手順５■動作を確認。
手順６■[上書き保存]。
　　　　但し、現在のブックがマクロを使えないファイル形式(*.xlsx)だった場合は、
　　　　[名前を付けて保存]から
　　　　　[ファイルの種類]を[Excel マクロ有効ブック (*.xlsm)]に指定して
　　　　　新たに保存し、以後、ここで保存したブックを使用する。

手順、以上です。
動作確認、してくださいね。

特に説明が無かったので、
[種類]入力セルと[商品分類]入力セルとは一対一で対応している、
という前提で書いています。
これが、一対多で対応させているようでしたら、
上記のマクロでは出来ませんので、大幅な書換えが必要です。
「何が何処にあるか」
ということと、
「どれとどれが対応するのか」
という具体的な詳細についての洩れの無い補足が必要です。

以上です。

この回答へのお礼

つたない説明で、汲み取ってくださり、ありがとうございます！
どうしていいのやら状態だったので本当に助かりました。
補足部分を最初からいえばよかったですね。
無事解決できて、感謝ばかりです！

お礼日時：2015/03/25 17:40

No.2 回答者： moon-and-star 回答日時： 2015/03/24 16:42

以下を参考にして下さい。

http://kouritu.net/11777/

この回答へのお礼

ありがとうございます。
INDIRECT関数、今回初めて使いましたが、便利な機能ですね。
私がやった方法とは違う方法でしたので勉強になりました！

お礼日時：2015/03/25 17:38

No.3 回答者： mike_g 回答日時： 2015/03/24 16:44

添付図の Excelメニューのトップに[データの入力規則]とあるのに、貴方はなぜ「入力規制」と書くの？

この回答へのお礼

あ、本当ですね。大変失礼しました。以後気を付けますね。
ご指摘ありがとうございました。

お礼日時：2015/03/25 17:29

No.4 回答者： cj_mover 回答日時： 2015/03/24 17:24

＃１です。

訂正があります。

誤）
ダヌルクオート
正）
ダブルクオート

誤）２か所
> 手順３■ＶＢＡの編集画面が現れたら、そもまま、[Ctrl]+[V]キーを押して、
> 　　　　上記にコードを貼り付ける。
正）
手順３■ＶＢＡの編集画面が現れたら、そのまま、[Ctrl]+[V]キーを押して、
　　　　修正済の上記スクリプトを貼り付ける。

タイプミス２点、編集ミス２点、以上訂正をお願いします。

なお、私の回答は、
[種類]入力セル、[商品分類]入力セルの[入力規則][リスト]の連動
については既に問題なく設定されている、という前提で、
添付画像にある一文、
【「種類」を再選択すると「商品分類」が連動して空白になるようにしたいです。】
に対して、お応えしたものですので。

以上、訂正と追加説明です。訂正の件、失礼しました。

この回答へのお礼

丁寧に訂正ありがとうございます。
意味が通じていたので、全く気にならなかったです。
(大きな支障がなければ、気にならないタイプなので)
無事できるようになり、本当に感謝ばかりです。

お礼日時：2015/03/25 17:33

No.5 回答者： loadline 回答日時： 2015/03/24 22:42

ひょっとしたら選んだ先のリストがないのではないですか？

例えば果物を選んだ場合は果物だけのリストが要ります。
果物>オレンジ、イチゴ、パイナップル
といった具合に。
また表があっても、果物のリストが「名前を定義する」等でリストの名前を定義していない場合、
最初のドロップリストで果物を選択しても、その先が出てこないようです。
例で具体的に言うと、
果物、野菜、炭水化物、タンパク質の全てのリストが名前定義されている必要があるようです。
野菜のリストが名前定義されていると、「野菜」という文字列と同じリスト名が参照され、
その野菜のリスト項目が２番目のドロップダウンリストに出てきます。

ひょっとしたら的外れかもしれません。その場合はスルーして下さい。

参考ページを以下に。「Excel 連動リスト」で検索しました。
http://www.use-eng.co.jp/mt_use/2008/04/post_292 …
名前の定義は「数式」タブより「名前の定義」か「選択範囲から作成」を使って下さい。
選択範囲から作る場合は項目名から選択しないと、デフォルトで一番上の項目がリスト名になってしまいます。

この回答へのお礼

ご指導ありがとうございます。
いつも、そもそもができておらずに、うまくいかないタイプですが
今回は、ご指摘の箇所大丈夫でした！

お礼日時：2015/03/25 17:35

No.6 回答者： CoalTar 回答日時： 2015/03/25 11:42

商品分類を削除するように連動させるにはVBAを使うしかありませんが、

条件付き書式で、エラーであるとわかるように背景を赤くしたり、
文字を白くしたりして、再選択を促すようにするといいかもしれませんね

G163セルに条件付き書式
[数式が] =ISNA(MATCH($G$163,INDIRECT($C$163),0))
[書式]を設定する

この回答へのお礼

そんな方法があるのですね！
私に変更の依頼をしてきた人の要望がかなえられたので、今回は使用しませんでしたが、
この方法は他で使いたいと思います。
教えてくださり、ありがとうございます！

お礼日時：2015/03/25 17:28

No.7 回答者： cj_mover 回答日時： 2015/03/25 12:56

＃１、４です。

＃４への補足、拝見しました。

添付画像、解り易いですね。

エラーの原因は結合セルであることを想定していなかった点です。
ＶＢＡにとって結合セルは鬼門のボスキャラ的存在です。
ただ、このケースでは、問題を適切に解消出来ます。
エラーになっている
　　　　c.Offset(0, 3).ClearContents
の一行を、以下の記述に差し換えてください。
　　　　c.Offset(0, 2).MergeArea.ClearContents
.Offset(0, 2)に書き換えるのは、
起点となるセルcが結合セルである為に、右端セルに機転が移り、
補足欄の添付画像で言えば、
E163から右に２つズレた位置にあるG163を指定する意味で、
.MergeAreaを追加することで、より確実に
G163:I163の結合セルを捉えるようにします。
上記のスクリプト書換えによって、当座のエラーは解消されます。

> 種類]入力セル、[商品分類]入力セルの[入力規則][リスト]の連動
> については問題なく設定されております。
了解済でしたが、ご確認、ありがとうございます。

> 同じエラーについて、cj_moverさんが他の質問者に答えていましたので
> 拝見したのですが、難しく私には手におえそうにありませんでした。
実行時エラー '1004' というエラーコードは、
多数のエラー内容に付けられる番号ですし、原因も多種多彩です。
ですので、今回の件とはあまり関係ないのです。

> このエクセルファイルは私が作成したものではなく
人が作ったものに手を入れるのは大変ですね。頑張ってください。

> なお、プルダウンリストは、
> A1からD1まで名前の定義をしており、
> A1からD5までは「選択範囲から作成」で保存してあります。
ここまでの私が担っている事象については、この部分は関係なさそう、
と思っています。

以上です。

この回答へのお礼

すごい！出来ました！！！！！！
ありがとうございます！

VBAの世界にもボスキャラがいるんですね。

あまりに凝ったつくりをしているエクセルで
今まで誰も手を付けていなかったそうですが、あれもこれもと要望が出てきて、
このところこのエクセルへの対応に振り回されています。
(まさかVBAなるものを使うようになるとは）

全く分からない私にも丁寧なご指導、本当にありがとうございました！
助かりましたー！

お礼日時：2015/03/25 17:25