Excelのワークシートでのコンボボックスについて

Excelのワークシートでコンボボックスを設定する方法を教えてください。

「フォームコントロール」と「ActiveXコントロール」の違いがわかりません。

添付の画像の通りコンボボックスに西暦を入力(別シートに入力済みの値を表示するように設定)してあるのですが、ファイルを保存しているにも関わらず、再度ファイルを開くとコンボボックスの中のリストは空欄になってしまいます。

今は「ActiveXコントロール」のコンボボックスで設定しています。

コードは以下のように設定してみたのですが、設定内容や設定箇所が違うのでしょうか？

//////////////////////////////////////////////////////

Private Sub ComboBox1_DropButtonClick()

Dim sh As Worksheet
Set sh = Worksheets("マクロ")

Dim i As Integer
Dim lastRow As Integer
lastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
With ComboBox1
For i = 2 To lastRow
If ComboBox1 = "" Then
.AddItem sh.Cells(i, 1).Value
End If
Next i
End With

End Sub

//////////////////////////////////////////////////////

コンボボックスのリストの内容が消えてしまうので、
コードの内容は同じで以下のところにもコードを書いてみました。

//////////////////////////////////////////////////////

Private Sub Worksheet_Activate()

Dim sh As Worksheet
Set sh = Worksheets("マクロ")

Dim i As Integer
Dim lastRow As Integer
lastRow = sh.Cells(Rows.Count, 1).End(xlUp).Row
With ComboBox1
For i = 2 To lastRow
If ComboBox1 = "" Then
.AddItem sh.Cells(i, 1).Value
End If
Next i
End With

End Sub

//////////////////////////////////////////////////////

なんだかもう、訳がわからずぐちゃぐちゃです。

コンボボックスの中に値が入っていると、実行ボタンをクリックしたときは正常にやりたい結果を出すことが実現できます。

ファイルを閉じた後に再度開いてもコンボボックスの中に値があるようにするにはどうしたらよいのか、ド素人の私にご教授いただきたくお願いいたします。

No.1 ベストアンサー 回答者： cj_mover 回答日時： 2014/03/05 02:05

こんにちは。

> ファイルを閉じた後に再度開いてもコンボボックスの中に値があるようにするにはどうしたらよいのか、

真っ先に解決に向かいましょう。

ご提示の２つのプロシージャは使いません。無効にする必要があります。
（資料として残すなら、コメントブロックしておきます）
その他にもComboBox1のリストを設定するプロシージャがある場合はすべて無効にします。

ComboBox1　のListFillRangeを指定します。

◆手順◆
１）Excelをデザインモードに切り替え。
　　　VBE画面のメニューバー[Sub/ユーザーフォームの実行ボタン]の右横辺りに、
　　　三角定規みたいなアイコンの[デザインモード]ボタンがあるので、これをクリックして凹んだ状態にします。
２）（ComboBox1が配置された）シートを表示し、（ComboBox1）を右クリック
　　　表示されたポップアップの中から
３）[プロパティ]を選択
　　　表示されたプロパティウィンドウの中の
４）[ListFillRange]というプロパティ（項目）の右横の値を入力する欄（空欄）に
　　　リストの元になるデータ（テーブル）のアドレスをExcel数式のセル参照の要領で
　　　「マクロ!A1:A14」とか「マクロ!A2:A15」のように"A1形式"で指定してEnterキーで確定します。
　　　（例は、リストの元データのシート名が"マクロ"、1999から2012（14件）の例です。）
　　　今の時点で必要なだけの大きさで指定しておきます。
５）Excelのデザインモードを解除。
　　　手順１）で凹ませた[デザインモード]ボタンを再度クリックして元に戻します。
６）実際にComboBox1のリストを選択してComboBox1の値をしていします。
７）ブックを上書き保存。
手順、以上です。

あとは、年に１回、年次を増やす時に、上述の手順で、ListFillRangeを指定し直します。

///////
少しアドバイス。

通常は、リストの変更を忙しく繰り返すようなことはしません。
何か特別な事情があってのことなら、その事情についてよく考えて、相談すればいいと思います。
リストを変更する、ということは、選択した値も変えることになり兼ねないですし、
リストをクリア（ComboBox1.Clear）しないで.AddItemするのは、リストの重複を招くことになります。
それと、Private Sub ComboBox1_DropButtonClick()　というイベントプロシージャですが、
「選択項目のリストをドロップダウンで表示するか、または非表示にすると発生します。」
と、こちらも随分忙しいイベントなので、扱いは難しいですし、用途も限られます。
多分、このイベントに手を付けたことが今回一番の間違いです。
ComboBox1については、Private Sub ComboBox1_Change()　ぐらいで済ませられる内容のようにお見受けします。
///////

今回の回答では、ActiveXのComboBoxコントロールについて、
固定のリストと指定した値を保持するように
ListFillRangeプロパティの扱いを説明しました。
他にも方法は色々ありますが、こちらに伝えられている限りの"必要"
に対しては、この方法で"十分"かと考えます。
つまり。一番簡単な（そして保守的な）解決策です。
リストの設定に関して、都度都度.AddItemしなければならない"必要"
があるということでしたら、補足欄にでも書いてみてください。
理由がこちらにも理解出来内容でしたら、改めて対応します。

オマケ。

' ' ///
Private Sub ComboBox1_Change()
　　Dim vTgYear As Variant
　　vTgYear = ComboBox1.Value
　　Label1.Caption = vTgYear - 1 & "～" & vTgYear + 1 & "年"
End Sub
' ' ///

以上です。

この回答への補足

ご回答ありがとうございました。ワークシート上でのコンボボックスの扱いは解決しそうです。頂いたアドバイスを拝読致しまして、ユーザーフォームのコンボボックスの扱いについて不安になって参りました。【http://okwave.jp/qa/q8501720.html】にて質問を新たにさせていただきましたのでアドバイスを頂戴いたしましてもよろしいでしょうか？お忙しい中申し訳ございませんが、何卒よろしくお願い申し上げます。

補足日時：2014/03/05 23:14

この回答へのお礼

ありがとうございました。無事に解決しました。もう一つの質問の方にも回答いただきましてありがとうございました。本当に心より感謝申し上げます。

お礼日時：2014/03/06 12:01