エクセル2010 シート上のコンボボックス

質問

エクセル２０１０を使用しております。
シート１にコンボボックス１（アクティブＸコントロール）を設置しており、
コードは下記です。

Private Sub ComboBox1_DropButtonClick()

Dim i As Long

With Worksheets("シート操作").OLEObjects("ComboBox1").Object

.Clear

For i = 1 To 100

.AddItem i

Next i

End With

End Sub

コンボボックス１のドロップボタンで
リスト１～１００の数字の中から、任意の数字を選び
テキスト表示部分に表示させたいだけなのですが、
上記コードではリストから選択しても何も表示されません。
コードの誤りと詳しい説明を希望します。
よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

VBAのヘルプでDropButtonClickイベントについて確認してみると
---
選択項目のリストをドロップダウンで表示するか、または非表示にすると発生します。
---
のように，動作の説明が書かれています。

つまりドロップダウンから選択してリストを「閉じたとき」にも再度イベントが発生し，何を選んだのかを含めてclearしてしまっているのが直接の原因です。

1から100までの選択肢の設定，つまり初期化動作を何か別の手段で行う（たとえばWorkbookのOpenイベント，たとえばワークシートのセルに記載しておいてRowSourceとして設定しておく）のが適当と思いますが，敢えて今の仕込みで工夫してみるなら

Private Sub ComboBox1_DropButtonClick()
　Dim i As Long
　With Worksheets("シート操作").OLEObjects("ComboBox1").Object
　if .listcount > 0 then exit sub
　For i = 1 To 100
　　.AddItem i
　Next i
　End With
End Sub

などのような風でも出来ます。

お礼

お礼が遅くなり申し訳ありません。
上記コードで希望通り動作しました。
ワークシート上のコントロールの設定が今一つ勉強不足でした。
上記コードプラスワークブックのＯＰＥＮイベントで値をクリアする
方法でとりあえずは対処しました。
１００ある選択肢でもやはりRowSourceとしてセルに設定したほうがいいのですか？
「プログラムだけで対処できたほうがスマートかな」
と思っておりました。（RowSourceの設定方法が１～１００を入力する方法しか知らないもので・・・）
今は時間がないので、今後おっしゃるような方法を勉強したいと思います。
大変ありがとうございました。

回答No.2

imogasi

２００２だが
表示ーツールバーーコントロールツールボックスーコンボボックス、を選ぶ。
標準モジュールに
Sub test01()
With Worksheets("Sheet1").OLEObjects("ComboBox1").Object
For i = 1 To 5
.AddItem i
Next i
End With
End Sub
を入れて実行。
これでコンボのアイテムで１－５が出ました。
これを参考に２０１０の場合をチェックしたら。
ーー
・数字を選らばせるのに、１００個もあるのに、コンボを使うというのはおかしいのではないですか。１００に近い数字を選ぶのが大変でしょう。テキストボックスで良いのでは。
・アイテムの設定にイベントを使うのもあまりやらないのでは。
・またエクセルＶＢＡでコンボを使いアイテムを使うのは、プロパティでＬｉｓｔＦｉｌｌＲａｎｇｅを使ってセルにある文字列や数をアイテムを使うのがエクセルの既定路線です。

お礼

ご回答ありがとうございます。
実は、もう一つテキストボックスを設置しており、入力して検索させております。
（テキストボックスは顧客名での検索）
できるだけマウスだけの操作で事が運べるように努力しておりまして、顧客番号が
わかるお客様は、マウスだけで番号検索できるようにしたかったのです。
Ｎｏ１の回答の方もおっしゃるように今後既定路線で少しづつ組みなおしていこうかと思います。
ありがとうございました。