VBAで入力規則のリスト参照元の値取得

質問

エクセル2007を使っています。

VBAツールを作っており、その処理で使われる一機能として
「任意のセルに設定されている入力規則のリストの元の値をVBAで取得する」処理を
コード中に書きたいと思っております。

■質問概要
　「入力規則のリスト」が設定されている任意のセルを選択したときに、そのセルに設定されている
　「入力規則のリスト」の「元の値」をVBAで取得する方法について教えてください。
　※取得した値は配列型の変数に代入するものとします

■想定イメージ
　たとえば、次のようなブックで使うことを想定しております（画像参照）
　※実際はもっとデータ量が多いブックで使う予定です
　・「リスト元の値」というシートで「選択言語」という名前でリストを定義化
　・「通常操作するシート」では値を入力したいセルに対し、「入力規則のリスト」の「元の値」を
　　「=選択言語」で設定

　VBAではこの「通常操作するシート」の入力欄のセルを選択している時に「選択言語」で定義されている
　データをすべて取得し配列型変数に代入するという動作をさせたいと思っています。

この方法について教えてくださいますようお願いします。

回答No.3

keithin · ベストアンサー

カンタンのためエラー処理は省略します
つまり
●マクロ作動時アクティブセルに「入力規則のリスト」の「元の値」を「=何某の名前」で設定済みである
●「何某の名前」は縦方向（たとえばシート３のＡ１：Ａ６）に設定済みである
●variant型の配列に格納する

sub macro1()
dim a as variant, ax as variant
a = application.transpose(names(mid(activecell.validation.formula1, 2)).referstorange.value)
' for each ax in a
' debug.print ax
' next
end sub

お礼

ありがとうございます！

回答No.4

kkkkkm

> どのリストを参照しているかを調べるということは可能なのでしょうか？

たとえばC8なら(画像でC8で入力規則してるように見えるので)
Mid(Range("C8").Validation.Formula1, 2)
で設定した「元の値」から=以降の値が取得できます。

お礼

追加のご回答をいただきありがとうございます！

回答No.2

kkkkkm

選択言語で名前つけしたセルのデータを取得したいということですよね。
以下でいかがでしょう。

Sub Example()
Dim ListData() As String
Dim c As Range
Dim i As Integer

i = 1
ReDim ListData(i)
For Each c In Range("選択言語")
ListData(i) = c.Value
i = i + 1
ReDim Preserve ListData(i)
Next c
End Sub

補足

ありがとうございます。
ちなみにリストがいくつかあって、それぞれ参照するリストの名前が異なる場合、どのリストを参照しているかを調べるということは可能なのでしょうか？
（先ほどの例だと、入力規則で「選択言語」という名前づけをされたセルが設定されているということをVBAで知る方法はないでしょうか？）

回答No.1

imogasi

質問が、ごちゃごちゃしていてよく読む気がしない。画像もぼやけている。
もし下記で、質問者のニーズに応用できるなら使ってみて。
ーー
A1：A10の各セルに入力規則の「リスト」を設定していて
リストはE3:E8の値
a
b
c
d
e
f
を使っているとする。
ーー
Sub Sample2()
a = Range("A2:A10").Validation.Formula1
MsgBox a
For Each cl In Range(a)
MsgBox cl
Next
End Sub
これでMsgbox　のところでa,b,c,・・ｆが都度表示されるから、好きなように料理して（組み立てるなりして）。
Range("A2:A10")．のところは、その範囲内の１セル指定でも同じやり方だろう。

お礼

スマートな書き方になってなくてすみません。
やり方を教えていただきありがとうございます。