コンボボックスに現在の日付から西暦だけを新しい順に表示する

生年月日など西暦を入力する時に、インターネットなどでよく見るリストから西暦をリストから選択する方法をエクセルでもできますが、こんなことで悩みませんでしたか？

わざわざセルに事前に西暦を用意するのが面倒
AddItemを使って初期項目を準備するのが面倒
現在の2021年がリストの一番上にならず昇順になってしまう
AddItemを現在の日付から西暦だけ・月だけ自動取得したい
現在の西暦を既定値に自動選択させたい
現在の月も自動取得しているのに翌月が表示される

このように疑問点を並べてVBAコードを考えて調べても、なかなか解決できず試してみるとどんどん複雑になっていくのですが、こんな感じでシンプルなVBAコードで解決します。

とりっぷぼうる

Private Sub UserForm_Initialize()
For i = -1 To 121
UserForm1.ComboBox1.AddItem Year(Date) – i
Next i
ComboBox1.ListIndex = 1
End Sub

これだけなので、詳しく説明します。

発想変えて変数iを1回使うだけでできる

発想変えて変数iを1回使うだけでできる

コンボボックスを始めて使う時などリスト表示させる項目を取得するのに、プロパティのRowSourceに項目追加したいセルを入力します。

西暦を入力すると1945年から現在2021年までとしても、70行以上もセルに入力作業が必要で、来年2022年以降毎回セルに入力しなくてはいけません。

そしてRowSourceもその度に変更が必要になるので面倒です。

これを解消するのにはAddItemを使ってVBAコードで項目設定をします。

AddItemを使って初期項目を準備

コンボボックスの初期項目をRowSourceを使った時の面倒を解消するには、AddItemメソッドを使ってユーザーフォームが開いた時に自動的にリスト化します。

ここでは2022年～2015年の8年分をAddItemを使ってコンボボックスの項目を設定します。

AddItemでリスト化

Private Sub UserForm_Initialize()
With ComboBox1
.AddItem “2022”
.AddItem “2021”
.AddItem “2020”
.AddItem “2019”
.AddItem “2018”
.AddItem “2017”
.AddItem “2016”
.AddItem “2015”
End With
End Sub

これでユーザーフォームが表示された時に、コンボボックスに2022～2015の8年分がリスト化されて選択できるようになります。

これにより、2023年になった時には.AddItem “2023”を付け加えるだけなので、プロパティのRowSourceを使った時よりはセルの入力作業が必要ない分面倒が減りますが、生年月日で使おうとすれば、1920年ぐらいからは必要なので2015年まででは足りません。

そうすると100行も書き続けるのはとても面倒なので、項目が多い時は実用的ではありませんから繰り返し処理ができるFor～Nextを使いましょう。

ユーザーフォームが開いた時に自動的にリスト化させるInitializeの使い方はこちらに詳細を記載していますので、ここでは省略します。

UserForm_Initializeでセル値を取得してから表示する方法

ユーザーフォームを開いた瞬間に複数セルのデータを次々と連続で読み込みこんでラベルやコマンドボタンのCaptionが書き換わっていたら、手間が省けて作業効率アップできますよ。

For～Nextで西暦を繰り返し処理で初期項目設定

コンボボックスにリスト化する西暦を1920年～2022年とし、繰り返し処理の変数「i」としたら、3行書いたらVBAコードは出来上がります。

For～Next

Private Sub UserForm_Initialize()
For i = 1920 To 2022
UserForm3.ComboBox1.AddItem i
Next i
End Sub

これで完成です。

For～Nextを使わないと8年分で10行もVBAコードを書いたのに、102年分が3行です。

できたような気がするけど、2023年になったら2022を2023に書き換えれば済むのですが、リストが1920年が一番上で2022年がリストの一番下になり、できれば2022年を先頭に下に行くほど古くなってくれた方がいいので、このままでは使い勝手が悪い。

とりっぷぼうる

無駄ですよ！

変数の部分を2022 To 1920に代えても！

結論として、このまま諦めて使う？

変数の数字を西暦4桁入力では解決できないので、日付から取得させる方法を考えましょう。

AddItemを現在の日付から西暦年・月を自動取得

エクセルVBAでは、パソコンに設定されている現在の日付を取得するには、Date関数を使う事で可能になります。

例えば、このページを書いている今日の日付で2021年1月28日をDate関数を使うと・・・

2021/1/28	Date
2021	Year(Date)
1	Month(Date)
28	Day(Date)

このようにDate関数で年（西暦）が取得できるので、変数「i」を西暦ではなく、1 To 100として次にAddItem iをAddItem Year(Date)+1とすると・・・

2022年～2121年になってしまい失敗で、-100 To 1にすると1921年から2022年になりましたが、やはり一番上に1921年でリストに一番下までスクロールするとやっと2022年が表示されるのは変わりません。

マイナスが使えるという事は、Year(Date)+1だから昇順になるのでマイナスを使えば降順になり、リストの上が2022年となり見事成功です。

降順になるので、1 To 100の部分は微調整をして-1 To 100にすると2022年から始まってくれます。

とりっぷぼうる

変数はマイナスも0も使えるんです。

これでほぼ目的達成できましたか？

降順の西暦VBA

Private Sub UserForm_Initialize()
For i = -1 To 100
Me.ComboBox1.AddItem Year(Date) – i
Next i
End Sub

現在の日付から「月」を取得する

YearをMonthに変えればいいのですが、先ほどの西暦（年）との違いは1～12の12か月しかない点と、降順ではなく1月から順に表示する昇順でいいという点です。

その為に変数「i」は1 To 12になり、AddItemは (i)と書き換えれば完成で、書き換えたVBAコードは下記のようになります。

昇順の月VBA

Private Sub UserForm_Initialize()
For i = -1 To 121
Me.ComboBox1.AddItem Year(Date) – i
Next i
For i = 1 To 12
Me.ComboBox2.AddItem (i)
Next i
End Sub

3行付け加えたら完成しました。

どうせならば、今年・今月を既定値として選択されている状態にしてユーザーフォームが表示されると便利になります。

現在の西暦を既定値に自動選択させて表示する

コンボボックスの初期項目の中から既定値（デフォルト）を指定することができます。

この設定をすることにより、自動的に現在の日付から今年を自動的に選択状態で表示させるには、ListIndexを使い、何行目のリストを既定値にするかを決められます。

ここでは一番上に表示されるのが2022年なので2021年は2行目と言いたいところなのですが、下記のように決まっています。

空欄（未選択）	-1
リストの一番上	0
リスト2番目	1

という事で、先ほどのVBAコードにComboBox1.ListIndex = 1を1行付け加えれば、ユーザーフォームが開いたらコンボボックスに現在の日付から西暦を降順に表示させて、さらに今年と今月を既定値として選択状態にさせることができました。

完成したVBAコード

Private Sub UserForm_Initialize()
For i = -1 To 121
UserForm1.ComboBox1.AddItem Year(Date) – i
Next i
ComboBox1.ListIndex = 1
For i = 1 To 12
UserForm1.ComboBox2.AddItem (i)
Next i
ComboBox2.ListIndex = Month(Date) – 1
End Sub

VBA解説