VBA 別シートの表からプルダウンリストを作成したい

Question

Bシートにある表からデータを取得して、AシートRange("C2")にプルダウンリストを作成したいです。
最後の

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(items, ",")

のところでどうしても
実行時エラー1004
アプリケーション定義またはオブジェクト定義のエラーです
というエラーが出てしまいます。

回避方法を教えていただけますでしょうか。

====================================

Sub sample3()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim items() As Variant
Dim rng As Range

Set ws1 = ActiveWorkbook.Worksheets("A")
Set ws2 = ActiveWorkbook.Worksheets("B")

ws1.Unprotect
Application.ReferenceStyle = xlA1

Set rng = ws2.Range("A6:A32")
items = WorksheetFunction.Transpose(rng.Value)

With ws1.Range("C2").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(items, ",")
End With

End Sub

====================================

Comments

[m.ts10806]

コードはマークダウンのcode機能を利用してご提示ください。
https://teratail.com/questions/238564

Answer 1

入力規則に設定できる文字数は最大255文字です。超えていないでしょうか？
【エクセルVBA】255文字を超えたらどうなるの？入力規則のリスト設定方法あれこれ

下記のように直接アドレスを指定することができます。

With ws1.Range("C2").Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=B!$A$6:$A$32"
End With

Comments

[tapokotapo]

回答ありがとうございます。
文字数は最大でも15文字程度です。

[radames1000]

A6からA32の文字数合計（+区切りのカンマの数）が255文字を超えてはいけませんが、いかがでしょうか。

[radames1000]

なお、超えている場合は参照先にあるようにセル範囲や名前付き範囲で参照することにより対応可能です。

[tapokotapo]

337文字でした！
このような場合、どうしたらよいのでしょうか？
リスト表示はできないのでしょうか。

[kuma_kuma_]

質問者様
radames1000さんが回答されたURLに解決法載っていますよ？
(OFFSET関数とか)

[tapokotapo]

アドレスで取得できました。ありがとうございました。