エクセルVBA データ→入力規制→リストの作り方・・・

質問

いつも皆様には大変お世話になっております。
またまた、教えて頂きたい事がありますので宜しくお願い致します。

VBAでとあるセルに入力規制のリストを作りたいのですが、

　　　A　　B　　C　　D
1　　あ　　　　　　　
2　　い
3　　う
4
5

となっている状況でD1のセルにAの列のリストを作りたいのです。
但し、A4やA5に今後名前が増えていくのです。
その増えたときに、増えた分の名前もD1のセルにリストとして乗せたいのです。宜しくお願い致します。

回答No.2

papayuka · ベストアンサー

シート名のタブを右クリックして「コードの表示」を選び、出てきたVBE画面にコピペします。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells(1, 1).Column > 1 Then Exit Sub
With Range("D1").Validation
　　.Delete
　　.Add Type:=xlValidateList, _
　　　Formula1:="=" & Range("A1", Range("A65536").End(xlUp)).Address
End With
End Sub

お礼

ありがとうございます。ばっちりうまく出来ました＾＾
いつもいつもありがとうございます。

回答No.4

marbin

papavukaさんのマクロですと、

リストを追加したたびに
入力規則のリストの設定
をしなおしされますので、

リストに空白ができません。

ですので、A1～A65536を前も
ってリストに設定しておくよ
りもいいですね。

よって、私の記述は無視して
ください。

お礼

親切丁寧なご回答ありがとうございました。
今回はpapavukaさんのやり方を、実施させていただきました。
また、質問載せると思いますので、その節はまたよろしくお願い致します。

回答No.3

marbin

＃1です。

考え方としては、
始めからリストの範囲を
Ａ１～Ａ６５５３６にしておくことです。
そうしておけば、いくらＡ列のデータが
増えても関係ありません。

↓の作業をマクロの自動記録して、
一部改造しました。

Ａ１～Ａ６５５３６に
"A"という名前を付ける

Ｄ1～Ｄ６５５３６の
入力規則の設定を
入力値の種類を""リスト"

元の値を"=A"

として"ＯＫ"をクリック

※セル範囲に名前を付ける、
入力規則の設定をする、
は手動でもほとんど手間
ではありません。

回答No.1

marbin

ほとんどマクロの自動記録そのままです。

Sub Macro1()
Dim R As Range
ActiveWorkbook.Names.Add Name:="A", RefersToR1C1:="=Sheet1!R1C1:R65536C1"
Set R = Sheets("SHEET1").Range("D1:D65536")
With R.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=A"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub

お礼

早速のお返事ありがとうございます。
今試してみましたが、これだとB4のセルに何か文字が入ると、
なぜか、D1のリストのところに空白部分が入ってしまいます。
Aの列のみを見てD１のリストというものは作れないのでしょうか？
わがまま言いまして申し訳ありませんが、宜しくお願い致します；；