エクセルVBAでcmbBoxのプロパティの設定方法

質問

こんにちはよろしくお願いします。

ユーザーフォームにcomboBoxを作り，ドロップダウンするリストを設定するには，comboBoxのRowSourceプロパティに，アクティブシートのセルを a1:a5 などのようなかたちで設定しますよね。

このようなとき，ドロップダウンするデータをアクティブでない他のシートの中のセルを指定したいのですが，RowSourceプロパティにどのように設定すればよいのでしょうか？教えて下さい。
sheet5!a1:a5 などのように設定しても「プロパティの値が無効です。」というエラーが出てしまいます。どのように設定すればよいのでしょうか？教えて下さい。

回答No.4

keithin · ベストアンサー

調べてもどうしてもシート名の調べが付かない，また最初にお話したようにSheet1など間違いようのないシート名にしてやってみるのも出来ないようなら，

sub macro1()
dim i
for i = 1 to worksheets.count
worksheets(1).cells(i, 1) = """" & worksheets(i).name & """"
next i
end sub

というマクロを標準モジュールに書いて実行し，セルに書き出されたシート名をコピーしてマクロに貼り付けて利用してください。

お礼

keithinさんありがとうございました。
シート名の違いに間違いありませんでした。シート名をきちんと指定したら，プロパティボックスにきちんと入りました。お騒がせしてすみませんでした。
久しぶりにプログラミングしたところに，プロジェクトエクスプローラーのMicrosoft Excel Objectsのところに，
Sheet4（config）
と記載されていたものですから，configをシート名としなければならないところを，
sheet4をシート名として指定していたという間違いでした。

ComboBox1.RowSource = "config!a1:a5"

で解決しました。
お騒がせして，本当にすみませんでした。

回答No.3

noname#187541

こんばんは。

RowSourceプロパティに関しては既に指摘があるように「シート名」が間違っている（存在しない名前になっている）のだと思います。

それとは別に「ドロップダウンリスト」の設定方法には「AddItemメソッド」を使う方法もあります。

ComboBox1にsheet5のA1からA5を設定する場合

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 4
ComboBox1.AddItem (Sheets("Sheet1").Range("A" & i).Value)
Next i
End Sub

のようにすればできます。

お礼

M-SOFTさん，ありがとうございました。おっしゃる通りにシート名の指定が間違っていました。お騒がせしました。

回答No.2

keithin

＞いったいどういうことなのでしょうか？

先程もお話しした通り，あなたの書いたマクロのシート名が実際のそれと違っていると考えるのが，一番疑わしい状況です。
確認するようにお話ししましたが，どのように確認したのですか？
そもそもあなたのお手元の事実は，いったいどうなっているのでしょうか。
シート名にしても実際の（うまく動かない）マクロにしても，問題のエクセルの現物から，それぞれコピーして教えてください。

＞しかし，「書き込み出来ません。」というエラーが出ました。

先程の回答ではInitializeイベントの中に書いてみるようアドバイスをお話ししましたが，こちらはどんなマクロの中に書き込んだのですか？
もったいぶらずに（ジョークです），エラーを出したマクロの全文を掲示してみてください。

＞「プロパティが設定できません。プロパティの値が無効です」とのメッセージが出ました。

あなたが補足として書かれたそのマクロでは，またまたシート名が違っていますね。
キチンと確認し，正しく書いてください。
ご相談投稿や補足の情報提供についても，同じように「事実に即した内容」でお願いします。

回答No.1

keithin

ユーザーフォームに配置しているのですね。
デザイン時の設定としてプロパティウィンドウのRowSource欄に設定していますね。

であれば，
＞sheet5!a1:a5 などのように設定

それで合ってます。「アクティブシート」とかである必要も全くありません。

＞「プロパティの値が無効です。」というエラーが出てしまいます。

シート名を誤記していないか，再度よくよく確認してみてください。
または間違いのないSheet1などの名前で，まずは練習してみてください。

どうしてもどうしても上手く行かないときは，デザイン時ではなく実行時にユーザーフォームのInitializeイベントなどで，ComboboxのListプロパティに設定してしまうような手もあります。

me.combobox1.list = worksheets("正しいシート名").range("A1:A5").value

こちらも勿論，「アクティブシート」とかは全く無関係です。
ただしこの場合でも変わらず正しいシート名は必ず要りますので，シート名に間違いがあると上手く行かない事には違いありません。

補足

早速のお答え，ありがとうございました。
>デザイン時の設定としてプロパティウィンドウのRowSource欄に設定していますね。
はい，その通りです。sheet5!a1:a5 を"sheet5!a1:a5" のようにダブルクォーテーションはいらないですよね？？？ プロパティウィンドウの中ですから…。

ComboBox1.List = Worksheets("config").Range("A2:A13").Value
などのようにやってみました。しかし，「書き込み出来ません。」というエラーが出ました。
また
ComboBox1.RowSource = "Sheet4!A2:A13"
というふうにもやってみましたが，「プロパティが設定できません。プロパティの値が無効です」とのメッセージが出ました。
そこで，ComboBox1.RowSource = "A2:A13"とすると，きちんとアクティブシートの値を拾ってきます。いったいどういうことなのでしょうか？