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

combobox-yearコンボボックス
既定値にも指定する

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

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

このように疑問点を並べて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回使うだけでできる

combobox-rowsource

コンボボックスを始めて使う時などリスト表示させる項目を取得するのに、プロパティの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の使い方はこちらに詳細を記載していますので、ここでは省略します。

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/28Date
2021Year(Date)
1Month(Date)
28Day(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行付け加えたら完成しました。

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

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

combo既定値

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

この設定をすることにより、自動的に現在の日付から今年を自動的に選択状態で表示させるには、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解説
  1. ユーザーフォームを表示した時に自動取得する
  2. 変数「i」を宣言し、-1から121までとする
  3. ユーザーフォーム1に配置したコンボボックス1の初期項目を現在の日付から西暦を1づづ引いていく
  4. 繰り返し処理をする
  5. コンボボックス1の既定値を1とする
  6. 変数「i」を宣言し、1から12までとする
  7. ユーザーフォーム1に配置したコンボボックス2の初期項目を「i」とする
  8. 繰り返し処理をする
  9. コンボボックス1の既定値を今月から-1する
  10. マクロ記録終了

これで現在日付から西暦をコンボボックスにリスト化することができました。

ついでに和暦で同じようにできたらもっと便利かもしれませんが、Format関数だけでは和暦変換できないのでご注意してください。

コンボボックスで選択したリストをセルに記載する

Initializeを使って自動表示させたリストから西暦をプルダウンで選んだら、指定したセルに記載しておくことができます。

もちろんリストを選びなおせば上書きされるようにしますが、このVBAコードはとてもシンプルです。

選択値をセルに記載するVBAコード

Private Sub ComboBox1_Change()
Range(“A1”).Value = ComboBox1.Value
End Sub

VBA解説
  1. コンボボックスの選択値が変わったら
  2. セルA1にコンボボックスの選択値を記載する
  3. マクロ記録終了

同じように「月」も隣のセルB1に記載すれば、年・月が自動的に取り出せまし、日付も取得するならばDayを使ってVBAコードを追加すればできますよね。

とりっぷぼうる
とりっぷぼうる

このように西暦を現在日付から自動取得できることができたので、ワークシートに準備する手間が省けるようになります。

それでは自作カレンダーフォーム作成ページに戻りましょう。

スポンサーリンク


コンボボックス日付・時刻
スポンサーリンク
参考になりましたらシェア宜しくお願い致します

山頂テラスデッキからの三段紅葉(2019年11月)

フリーパスって助成金つぎ込んでいいの?

コロナ禍のGo To トラベル以外にも助成金だらけでうらやましいね。

安くなったフリーパスで遊びに行きたいけど、給与も減って余裕ないよね。

Excel VBA