エクセルで他のBookのデータを入力規則のリストに…

質問

エクセル（Excel2002）の入力規則についての質問です。
仮にBook1のsheet1のA列1～10行に元の値となるデータを作成し，これに「データ」と名前をつけます。
このデータをBook1とは異なるBook2のA列1行において入力規則の元のデータに設定しドロップダウンリストから選択することは可能でしょうか？
Book1とBook2は同一フォルダにあります。
ご教示ください。

回答No.3

KenKen_SP

こんにちは。KenKen_SP です。

下記の VBA コードで一応ですが、実現できました。

しかし、コードのコメントにも書いておきましたが、入力規則のリストで
セル範囲を指定するのではなく、値指定できる長さには制限があるみたい
です。あまり長いリストは設定できません。

ですから、”一応”になってしまうのですが、、、

例外処理もいくつか含めておきましたので、参考にはなるかもしれません。

下記コードを標準モジュールにコピー＆ペーストして下さい。

Sub Auto_Open()

　　Call SetValidation

End Sub

Sub SetValidation()

　　Dim Buf　　　As Variant
　　Dim WB　　　 As Workbook
　　Dim aryDat() As String
　　Dim strDat　 As String
　　Dim strMes　 As String
　　Dim i　　　　As Long

　　Const DatBookName As String = "Book1.xls"
　　Const DatRangName As String = "データ"

　　On Error Resume Next
　　'ダミーアクセスでブックの状態をチェック
　　Workbooks(DatBookName).Activate
　　If Err.Number > 0 Then
　　　　'エラー発生ならブックを開く
　　　　Err.Clear
　　　　Application.ScreenUpdating = False
　　　　Workbooks.Open Filename:=ThisWorkbook.Path & "\" & DatBookName
　　　　If Err.Number > 0 Then
　　　　　　strMes = "参照先ブック：" & DatBookName & "が見つかりません"
　　　　　　GoTo ErrorHandler
　　　　End If
　　End If
　　Err.Clear

　　'名前「データ」の値を配列にバッファ
　　Set WB = Workbooks(DatBookName)
　　Buf = WB.Names("データ").RefersToRange.Value
　　If Err.Number > 0 Then
　　　　strMes = "名前：" & DatRangName & " が定義されてません"
　　　　GoTo ErrorHandler
　　End If
　　WB.Close
　　On Error GoTo 0

　　'一次元配列に格納し直して、さらに文字列で連結（汗）
　　ReDim aryDat(UBound(Buf) - 1)
　　For i = 0 To UBound(Buf) - 1
　　　　aryDat(i) = Buf(i + 1, 1)
　　Next i
　　strDat = Join$(aryDat, ",")

　　'シート1のA列に入力規則を設定
　　With ThisWorkbook.Sheets("Sheet1").Columns(1).Validation
　　　　.Delete
　　　　'次行でリストを設定するのですが、、
　　　　'配列を受け付けないので、カンマ区切りの文字列で設定しています。
　　　　'リストが大きすぎるとエラーになりますので注意。
　　　　.Add Type:=xlValidateList, Formula1:=strDat
　　　　.IgnoreBlank = True　　'空白値の入力を許可
　　　　.InCellDropdown = True 'ドロップダウンリスト表示
　　End With

Terminate:
　　Set WB = Nothing
　　Exit Sub
ErrorHandler:
　　Application.ScreenUpdating = True
　　MsgBox strMes, vbCritical
　　GoTo Terminate
End Sub

補足

KenKen_SP様；
おはようございます。ご丁寧なご回答をいただきありがとうございます。
やはりVBAを使わなければ無理ですか…。
今回，元の値となるデータは，13列分，1列あたり50～60種類，1リストは短くても10文字程度から最長で80文字程度の文字列です。このデータを100個ほどのExcelファイル（Book）で使いたく質問させていただきました。
またデータ（リスト）は頻繁に更新されます。
自分がやろうとしていることは，ExcelではなくAccessでやるべきことなのでしょうか！？