違うシートからリストを作成するには

質問

９７のエクセルです。
リストを作成するために下記のようにマクロを作ってます。(教えてもらいました)
Worksheets("sheet1").Range("m21:m100").Validation.Delete
Worksheets("sheet1").Range("m21:m100").Validation.AddType:=xlValidateList,_
Formula1:="=$A$1:$A$8"

これですと、Sheet1のA1～A8までをSheet1のm21～m100までに繁栄するリストができます。
このA1～A8をSheet2から選択してSheet1のm21～m100に繁栄させるにはどのようにすれば良いでしょうか？
また範囲の数字（m21とか）を変数にしたいのですが可能でしょうか？

宜しくお願いします。

回答No.2

nishi6 · ベストアンサー

他シートのリストを使った入力規則は通常のアドレス指定ではできなかったような気がします。（余り自信なし）
下では、Sheet2のリスト範囲(質問のSheet2のA1:A8)に自動的に範囲名(myList)を付け、それを入力規則の設定に使っています。
名前を手動で付ければ短くなりますが、マクロらしくないので内部で設定しています。変数にしたいという事なので、シート名やセル範囲を外部に出しています。

ご参考に。(当方、Excel97です)

Sub myValidationSet()
　　Dim inpSheet As String '入力規則を適用するシート名
　　Dim inpArea As String '入力規則を適用する範囲
　　　　'次の行にシート名と範囲を設定します
　　　　inpSheet = "Sheet1": inpArea = "M21:M100"

　　Dim lstSheet As String '入力規則のリストがあるシート名
　　Dim lstArea As String '入力規則のリスト範囲
　　　　'次の行にシート名と範囲を設定します
　　　　lstSheet = "Sheet2": lstArea = "A1:A8"

　　Application.ScreenUpdating = False

　　'シートが異なる時のために、入力規則のリスト範囲に範囲名を付ける。
　　Worksheets(lstSheet).Select
　　Range(lstArea).Select
　　'『myList』という範囲名を付けた
　　ActiveWorkbook.Names.Add Name:="myList", _
　　　　　　　　　　　　　　 RefersToLocal:="=" & Range(lstArea).Address

　　'入力規則を設定する
　　Worksheets(inpSheet).Select
　　With Range(inpArea).Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, Formula1:="=myList"
　　End With

　　Application.ScreenUpdating = True

End Sub

お礼

ありがとうございます。

できました。

回答No.1

Fat01ton

EXCEL2000での環境では以下のような感じで出来ました。
（名前は適当に作成しましたのでご自分の好みでお直しください）

Public Sub testPRG()

Dim シート As String
Dim エリア As String

シート = Worksheets("sheet1").Range("C1").Value
エリア = Worksheets("sheet1").Range("C2").Value
Worksheets(シート).Range(エリア).Validation.Delete
Worksheets(シート).Range(エリア).Validation.Add Type:=xlValidateList, Formula1:="=S2List"

End Sub

>A1～A8をSheet2から選択してSheet1のm21～m100に繁栄させるにはどのようにすれば良いでしょうか？

これは挿入→名前→定義で名前を設定（List）し参照範囲で=Sheet2!$A$1:$A$8とします。（任意の範囲）
元の式のFormula1:="=$A$1:$A$8"　""間を"List"にすれば出来ます。

>また範囲の数字（m21とか）を変数にしたいのですが可能でしょうか？

こちらにつきましては仮にSheet1のC1セルに入力した名称がシート名
C2に入力した文字列をリストを繁栄する範囲としています。
ですからC1に”sheet1" C2に"m21:m100"と入力していただければ現在のままになると思います。

お役に立てればよろしいのですが……

お礼

ありがとうございます。

試してみます。