ＥＸＣＥＬ コンボボックスの一覧

質問

ＥＸＣＥＬのシート上にコンボボックスがあります。

ここには、ある列のＡ１からデータが入っている最後のセルまで（例えばＡ１４）の一覧を表示したいと思っています。

参照するセルの終わりが、Ａ１４やＡ２０など、どこまでデータが入力されているかによって可変で変わります。

その場合、少し大目にＡ１：Ａ５０としておくと、データが入っていない空白セルまでコンボボックスの一覧に表示されてきます。

こうならないように、データが入っている範囲だけとしたい場合、どのような設定にすればよいでしょうか。

よろしくお願いします。

回答No.3

Wendy02

こんにちは。Wendy02です。

>コンボボックスの横にリスト一覧のように、全ての選択肢が表示されてきます。

今は、良くイメージが沸かないのです。

私は、これらのコントロールの名称からデフォルトの名前以外で使用することもありませんので、どうしてもおっしゃっていることが理解できません。たぶん、コメントのような感じになっているのかなって思います。

仮に、ListFillRange の列を複数に登録しても、やはりコンボボックス(ComboBox)としては機能します。ただし、CodeList　が、ComboBoxではなく、ListBox の場合は、事情が違います。

ListRows が、100とか、かなり大きな値が入っているのかもしれませんが、リスト一覧が、右横に出る、というのはCombBox では記憶にありません。ListRows の値は、10ぐらいまでに戻していただくとして、他にあるとすれば、Excelのトラブルぐらいですね。

引用される（ListFillRange)のセル幅に影響を受けるか、もしくは、CombBox の置いてあるセル幅に影響を受けているか、ということになります。その場合は、一旦、標準幅に戻してあげ、それで保存してから、元の大きさに戻すとかすると良いかもしれません。ただし、この方法は、ComboBox ではなく、入力規則のドロップダウンリストの際のトラブルに用いる方法です。

CombBox では聞いたことがありません。しかし、それでも調子が悪いようなら、新たにデフォルトで作り直したほうが早いような気がします。

他に、思い当たる要素は考えられないのです。

回答No.2

Wendy02

こんにちは。Wendy02です。

>Set cell1 = Sheets("sheet1").Range("B2")
>Set cell2 = Sheets("shhet1").Range("B65536").End(xlUp)
'Sheets("shhet1") のSheet1 のつづりが違っています。

>CodeList.ListFillRange = Range(cell1, cell2).Address

>ここでエラーがでていまいます。
424: アプリケーションまたはオブジェクト定義のエラー

でしたら、違う場所に書いていらっしゃるようですが、「コントロールツールのコンボボックス」は、シートモジュールです。(Sheet1 なら、Sheet1のモジュールです)

424:オブジェクトが必要です
でしたら、もともと、CodeList の名称が違っていることになります。

だから、「CodeList」をコンボボックス名としたら、

シートモジュール、つまり、ワークシートのシートタブを右クリックすると、「コードの表示」というのがありますから、それをクリックして出てくるエディタウィンドウに書くわけです。

CodeList.ListFillRange =""
CodeList.ListFillRange = Range("B2", Range("B65536").End(xlUp)).Address

という二行は、そのまま生かせるはずですね。

補足

いつも丁寧な解説ありがとうございます。
シート名は、こちらに書き写すときにシート名を変更した時に間違えました。

教えて頂いたようにしてうまくいきました！
ただ、次のようになるのですが、これは仕方ないのでしょうか。

コンボボックスのイメージは、▼をクリックしたら、下にドロップダウンリストのように出てくると思っていたのですが、コンボボックスの横にリスト一覧のように、全ての選択肢が表示されてきます。

また選択肢が大変多いため、下のほうの選択肢が画面に表示されないのですが、これはどのようにすれば改善できるのでしょうか。

回答No.1

Wendy02

こんにちは。

コンボボックスには、二種類があります。
それぞれによって設定の仕方が違います。

フォームの場合は、
挿入－名前－定義

名前(W)
Hani （任意の名前)

参照範囲：

=INDIRECT("Sheet1!$A$1:$A$"&COUNTA(Sheet1!$A$1:$A$100))

100までをカウントする

「OK」
で

フォーム－右クリック-コントロールの書式設定

入力範囲(T) 「Hani」

'------------------------
コントロールツールのコンボボックスの場合

まず、コントロールツールバーを出して
「青い三角定規」ボタンをオンにして、編集可能にさせます。

右クリックで、プロパティ－ListFillRange が書かれていたら、それを消します。

次に、コンボボックスをダブルクリックして、画面を替えて、
Visual Basic Editor 画面に替え

Private Sub ComboBox1_Change()　'←予め出ている*　削除

End Sub '←予め出ている*　削除

*　この2行は削除してください。

以下を貼り付けてください。
'-----------------------------------------------------------------

Private Sub ComboBox1_DropButtonClick()
　ComboBox1.ListFillRange = ""
　ComboBox1.ListFillRange = Range("A1", Range("A65536").End(xlUp)).Address
End Sub

'-----------------------------------------------------------------
ただし、これは、A列は、他に利用しないことにしていますから、そうでない場合は、

100行までしか使わないなら、このようにしてもよいです。

　ComboBox1.ListFillRange = Range("A1", Range("A100").End(xlUp)).Address

Alt + Q で画面を閉じて、

「青い三角定規」ボタンをオフにして、編集不可にしてから、コントロールツールバーを閉じます。

後は、ボタンのクリックで、自動的に範囲が変わっています。

補足

丁寧なアドバイスありがとうございます。

コントロールツールボックスからですので、以下のとおりにやってみましたが、「Rangeメソッドは失敗しました」というメッセージが出てしまいます。

教えて頂いたのをアレンジしたのですが、記述が間違っているのでしょうか。

dim cell1 as range
dim cell2 as range

Set cell1 = Sheets("sheet1").Range("B2")
Set cell2 = Sheets("shhet1").Range("B65536").End(xlUp)

CodeList.ListFillRange = Range(cell1, cell2).Address
　　↑
　ここでエラーがでていまいます。

すみませんが、アドバイスお願いします。