コンボボックス で年月を選ぶ方法

エクセルのシート上にコンボボックス を設置しています。
ここから年月（例：2020年6月）を選ぶ方法を教えていただけませんでしょうか？

別なところに年月の一覧を作って入力範囲にすることはできるのですが、
そうすると手動で毎月年月を更新しなければならず、
そうではなく、常に最新の年月が表示されるようにしたいと思っています。

ユーザーフォームで作ったとき、
ComboBox1.Value = Format(ComboBox1.Value,"yyyy年m月")
とすることで常にコンボボックスに最新月が表示されるようにできました。

これと同じことをユーザーフォームを使わずに行いたいのですが、方法がわかりません。

ご教授のほど、よろしくお願いいたします。

No.8 ベストアンサー 回答者： fujillin 回答日時： 2020/06/03 21:51

No5です。

＞A1に2020年6月、A102に2012年1月になっている場合、
＞スタートセル位置がA1でカウント開始日付が2012年1月、でよろしいでしょうか？
スタートセル位置は$A$1で良いですが、リストが別シートにある場合には、「シート名!」を付けるのを忘れないでください。
開始日（＝一番古い月）が2012年1月の場合、カウント開始日付は「"2012/1/1"」のような日付を表す文字列にしておけばよいです。
（リストに設定した関数式が、上記よりも広い範囲までフィルコピーされていることが条件です）

この回答へのお礼

fujillinさん、ありがとうございます。
無事できました！
ありがとうございました。

お礼日時：2020/06/04 09:51

No.7 回答者： tom04 回答日時： 2020/06/03 18:42

No.1・6です。

すでに配置してある「コンボボックス」がフォームコントロールのコンボボックスなのかActiveXのコンボボックスなのかを知りたい！
というコトですね。

簡単な方法としては
メニュー　→　開発　→　「デザインモード」をクリック
これで簡単に選択できればActiveXのコンボボックスになりますので、
プロパティをクリックするとオブジェクト名が判ります。

単に指のマークが表示される場合はフォームコントロールのコンボボックスになるはずです。
この場合は結構厄介です。

あれこれオブジェクト名を探してみました。
「ドロップ1」のようになるようですが、それをそのままオブジェクト名にはできないようです。

後者の場合は当方の力不足で、お役に立てないかも・・・m(_ _)m

この回答へのお礼

tom04さん、ありがとうございます。
教えていただいたように確認してみたところ、フォームコントロールで間違いないようです。
でもせっかくなので教えていただいたコードも参考にさせていただきます。
ありがとうございます。

お礼日時：2020/06/03 18:47

No.6 回答者： tom04 回答日時： 2020/06/03 17:57

No.1です。

前回はActiveXコントロールの「コンボボックス」がシート上に配置してある！という前提のコードでした。

数か月分をドロップダウン候補にしたい！ということのようなので・・・

今回もシートモジュールにしてください。
シート上にはActiveXコントロールのコンボボックスが配置してある！という前提です。
とりあえず2年分を候補にするよにしてみました。

Private Sub Worksheet_Activate()
　Dim myDate As Date
　Dim i As Long
　　myDate = Date
　　　ComboBox1 = Format(myDate, "yyyy年m月")
　　　　For i = 1 To 24 '//★//
　　　　　ComboBox1.AddItem Format(DateAdd("m", i, myDate), "yyyy年m月")
　　　　Next i
End Sub

※　シートがアクティブになった時点で実行されます。

※　ドロップダウン表示したい数はコード内の「★」の行で調整してみてください。m(_ _)m

この回答へのお礼

tom04さん、ありがとうございます。

シート上に設置したコンボボックス（フォームコントロール）がcomboBox1かどうかがわからないのですが、
どのようにしたら確認できるか教えていただけませんでしょうか？

お礼日時：2020/06/03 18:30

No.5 回答者： fujillin 回答日時： 2020/06/03 17:53

No4です

＞これは来月はどうなるのでしょうか？
来月になると、一番上のセルの値が「2020年7月」に変わり、以下一か月ずつ繰り上がるはずです。

＞一番下の2012年1月は変わらず、毎月データが増えるようになりますでしょうか？
選択肢が月ごとに増えてゆけば良いという意味と解釈しました。
入力規則の設定を可変にすることで可能と思います。
いらぬおせっかいではありますが、2012年から2020年まで（以降増え続ける）となると、選択肢が多すぎて使いにくいような気がしますが・・・？

さて、本題ですが、例えば開始日を固定したければ、現在からそれまでの経過月数＋１が選択肢として表示したいセルすうろいうことになります。
まずリストの方を十分に下方迄（余分なくらいに）フィルコピーしておきます。

次に入力規則の設定蘭でセルの範囲を関数で指定します。
=OFFSET(スタートセル位置,0,0,DATEDIF("カウント開始の日付",TODAY(),"M")+1)
としておくことで実現できます。

「スタートセル位置」、「カウント開始日付」は質問者様はご存知ですが、私にはわかりませんので、適宜設定してください。

この回答へのお礼

fujillinさん、ありがとうございます。
実行してみたのですが、「この数式内の範囲の参照または定義された名前が見つかりません」と出てしまいました。
A1に2020年6月、A102に2012年1月になっている場合、
スタートセル位置がA1でカウント開始日付が2012年1月、でよろしいでしょうか？

お礼日時：2020/06/03 18:26

No.4 回答者： fujillin 回答日時： 2020/06/03 16:30

No2です

＞という選択肢をコンボ ボックスに入れたかったのですが、
＞この場合はどのようにしたらよろしいのでしょうか？
通常の入力規則を設定しておいて、リストの方に同様の関数式を設定しておけば可能になります。
ただし、この場合は、選択肢となっているリストの方はブックを開くと自動的に更新（＝自動計算になっている場合）されますが、コンボボックスの値の方は自動更新されません。（エクセルの通常仕様なので）
ドロップダウンすれば、選択肢は新しい内容のものになって表示されます。

値が文字列なのかシリアル値なのか不明のままですが、とりあえず以下に文字列と仮定した場合の関数式を記しておきます。（シリアル値の場合もほぼ同様です。No2を参照）
・リスト表示の選択肢がどこか別のセル範囲にあるとして、１列に（縦に）
　ならんだセル範囲であると仮定します。
・選択肢は当月を先頭に、一月前、二月前…と下に並ぶものとします。

選択肢の先頭セルに
　=TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1-ROW(A1),1),"yyyy年m月")
の関数式を入力し、下方に必要な数だけフィルコピーでいかがでしょうか？

この回答へのお礼

fujillinさん、ありがとうございます。
別シートに関数式を入れてコピーをしたら希望の年月ができました。
A1（2020年6月）からA102（2012年1月）まで年月が入っておりますが、これは来月はどうなるのでしょうか？
A1が2020年7月になって、A102が2012年2月になるのでしょうか？
一番下の2012年1月は変わらず、毎月データが増えるようになりますでしょうか？

お礼日時：2020/06/03 16:40

No.3 回答者： めぐみん_ 回答日時： 2020/06/03 15:25

敢えて質問文に『年月を選ぶ』と記載されているのですし『別なところに年月の一覧を作って』とあるわけですから、そのComboboxに入れる値は『当月』を含めて前後数ヶ月なのか、過去のみか、未来のみなのかを明確に提示された方が宜しいのでは？と思いますよ。

多分ですがユーザーフォームの時は『年月日』で取得してたものを選択した際に『年月』にされたとか？

この回答へのお礼

めぐみん_さん、ありがとうございます。

説明不足ですみません。
コンボボックス には、
2020年6月
2020年5月
2020年4月
2020年3月
・
・
・
と表示させるようにしたく、2012年1月まで選べるようにしたいと思っています。

ユーザーフォームのときは、

Private Sub ComboBox1_Change()
ComboBox1.Value = Format(ComboBox1.Value,"yyyy年m月")
End Sub

としていました。

これと同じことをシート上に直接設置したコンボボックス でもやりたかったのですが、
どのようにしたらよいのかわからず質問させていただきました。

お礼日時：2020/06/03 15:50

No.2 回答者： fujillin 回答日時： 2020/06/03 15:03

こんにちは

ブックを開いた時の日付が表示される（別の日に開くとその日になる）のでよければ、コンボボックスではなく、当該セルに
　=YEAR(TODAY())&"年"&MONTH(TODAY())&"月"
という式を入力しておくことで可能と思います。

上式の結果は文字列になっていますので、シリアル値（エクセルが日付と認識する値）としたければ、
　=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
という式にしておけばよろしいかと。
セルの書式はあらかじめ「yyyy年m月」としておきます。

※ ご質問文に記載がありませんでしたので、シリアル値の場合の日（日数）の部分は１（ついたち）になるようにしてあります。
もしも、当日そのままの日付でよければ、
　=TODAY()
だけでよくなります。


一度入力した値は、別の日にブックを開いてもそのまま（変化しない）ようにしたい場合は、
　・ボタンを押すとその日の日付が設定される
または
　・当該セルをダブルクリックすると日付が設定される
といったマクロを作成しておくことで実現可能です。

この回答へのお礼

fujillinさん、ありがとうございます。
セルに入力する方法で開いたときの年月の確認ができました。

コンボボックス にした理由は開いた時より過去の月を選択する場合があったためでした。
2020年6月
2020年5月
2020年4月
2020年3月
・
・
・
という選択肢をコンボ ボックスに入れたかったのですが、
この場合はどのようにしたらよろしいのでしょうか？

お礼日時：2020/06/03 15:46

No.1 回答者： tom04 回答日時： 2020/06/03 14:32

こんにちは！

あるシート上にコンボボックス1を配置しているのですね。
そして常に最新日（本日）を表示したい！と解釈しました。

「あるシート」がアクティブになった時点で表示するのはどうでしょうか？
「あるシート」のシートモジュールを

Private Sub Worksheet_Activate()
　ComboBox1 = Format(Date, "yyyy年m月")
End Sub

としてみてはどうでしょうか？

※　単に一つの日付だけを表示するのであれば
コンボボックスでなくて、テキストボックスで用を足すとは思いますが。

的外れならごめんなさい。m(_ _)m

この回答へのお礼

tom04さん、ありがとうございます。
フォームコントロールでシート上に設置したので、コンボボックス の名称がわかりません。
確認方法があれば教えていただけませんでしょうか？

お礼日時：2020/06/03 15:40