Excel、入力規則のリストについて

質問

現在、A1とA2のセル(仮)に入力規則を設定し、それぞれ名前定義した範囲をリストとして表示するようにしています。
※ただしくは、A1とA2は連動しており、A1で選択された値によってA2に表示されるようになっています。

A1の値を切り替えると、A2のリスト項目は問題なく切り替わるのですが、
A2のリストから選択して値を選択してあげるまで、A2は以前の値が保持されたままになります。

A1の値を切り替えると同時に、A2にA2のリスト項目の一番先頭のものを表示させることは出来ませんでしょうか。

お願いします。

回答No.2

mshr1962

#1です。
>Target.Offset(1, 0).Value = Range(Target).Value
Targetは入力したセル（この場合はA1）で
Target.Offset(1, 0)は一つしたのセル(A2)になります。
Range(Target).ValueはA1の名前の範囲の先頭の値です。
ですので入力したセルの、一つ下のセルに名前の範囲の先頭の値をセットになります。

>例えばB1とB2,C1とC2,D1とD2…
If Target.Address = "$A$1" Then
でA1限定にしていますので、この部分を
If Target.Row = 1 Then
とすれば1行目に入力するとこのマクロが実行されます。
列も指定するなら
If Target.Row = 1 And Traget.Column < 5 Then
上記でA1:E1に対するマクロになります。

お礼

自己解決といっていいのかはわかりませんが、

If Range("A1") <> "" Then
Range("AB2:AB5").Name = Range("A1")
End If

で名前を定義してみました。ただし、これだとA1の値をいじるたび(記入ミスも含め)無限に名前が増えていきますよね…。
どうすればいいんでしょうか。。

補足

ありがとうございます。
複数にも対応可能ということで安心して使用させていただけます。

さて、
お教えいただいたマクロを実行するために、連動をINDIRECTに切り替えたのですが、ここで1点問題が発生します。

INDIRECTでA1を指定する＝A1のセルに入っている文字列と、リストの範囲の名前が同一になるかと思います。(エクセル初心者なので違っていたらどんどん突っ込んでください。)

今制作しているものに関しまして、A1のセルの値はユーザーが記入するものであり、値を指定することが出来ないので、範囲名を想定して定義しておくことが出来ません。。

何かいい方法はありますでしょうか。

回答No.1

mshr1962

Excelの標準機能では出来ないのでマクロになりますが良いのでしょうか？
使用しているシートの見出しを右クリックして「コードの表示」を選択
表示された右上の枠に下記のマクロをコピーして貼り付けてください。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Target.Offset(1, 0).Value = Range(Target).Value
End If
End Sub

お礼

すいません、
INDIRECTを使わず連動させていたので、マクロの効果が得られなかったようです。サンプルでINDIRECTを利用して連動させたものに当ててみると、無事に動きました。
ありがとうございます。

ちなみに、今お聞きさせていただいたのは、A1とA2の連動なのですが、
同一シートないに、このような箇所が複数あります。
例えばB1とB2,C1とC2,D1とD2…
こういった場合は、

If Target.Address = "$A$1" Then
Target.Offset(1, 0).Value = Range(Target).Value
End If

を複数行書き、$A$1の値を変えることで対応出来ますでしょうか。

補足

お返事ありがとうございます。

マクロでも全然問題ありません。
ご指示通りに貼り付けてみたのですが、A2のセルは切りかわりません。
説明不足でわかりにくいところも多々ありますがお願いします。

ちなみに下記はどういう意味でしょうか。
Target.Offset(1, 0).Value = Range(Target).Value