ユーザーフォームカレンダー機能を使って日付入力がオブジェクトエラーになる

祝日対応カレンダー コマンドボタン
祝日対応カレンダー
スポンサーリンク

祝日対応カレンダーフォーム

ワークシート中心で作成
祝日判定付き自作カレンダーフォーム
祝日判定付き自作カレンダーフォーム

左が先ほどまで紹介していたワークシートに日付取得などエクセル関数を活用して作成した2018年当時作成のカレンダーで、右が今回作成した祝日対応して2月以外の日付は不要なので消去したカレンダーです。

前回と見た目はそんなに変わらないのですが、祝日が赤色で表示できるようになり当月以外の日にちが非表示になった点が改良点です。

そして今回はワークシートには計算式など使わず、ワークシートには祝日リストのみ作成していますが、これも過去・未来の祝日を自動的に取得することは現状のVBA知識レベルではムリなのでリスト化しました。

また、VBAコードをまとめるだけの知識レベルではないので、作業手順ごとにコードを書いていたら長くなってしまいましたが、作業ブロックごとに分かれているのでそんなに難しくないと思います。

祝日対応カレンダーフォーム

Private Sub UserForm_Initialize()
Dim i As Long, j As Long
For i = -1 To 121
UserForm5.ComboBox1.AddItem Year(Date) – i
Next i
ComboBox1.ListIndex = 1
For j = 1 To 12
UserForm5.ComboBox2.AddItem (j)
Next j
ComboBox2.ListIndex = Month(Date) – 1
y = ComboBox1.Value
m = ComboBox2.Value
w = DateSerial(y, m, 1)
c = w – Weekday(w) + 1
ds = Weekday(w)
de = DateSerial(y, m + 1, 1) – 1
dc = de – w
For i = 1 To 37
With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
.ForeColor = RGB(0, 0, 0)
.Caption = Weekday(c + i – 1)
End With
Select Case UserForm5.Controls(“CommandButton” & i).Caption
Case Is = 1
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(255, 0, 0)
Case Is = 7
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(0, 0, 255)
End Select
With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
.Caption = CDbl(c + i – 1)
End With
Dim syuku As Range
Set syuku = Sheets(“CSV”).Range(“A:A”).Find(what:=UserForm5.Controls(“CommandButton” & i).Caption)
If Not syuku Is Nothing Then
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(255, 0, 0)
End If
With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
End With
Next i
For i = ds To ds + dc
With UserForm5.Controls(“CommandButton” & i)
.Caption = Day(c + i – 1)
End With
Next i
End Sub

ユーザーフォームを表示した時にカレンダーを表示するVBAコードで、長くて見るのもイヤになるかもしれませんが、ざっくりとした流れは以下の通りです。

  1. UserForm_Initializeで表示した時にVBAコードが働きます
  2. コンボボックスに年・月をリスト化
  3. これから使用する変数を準備
  4. 各コマンドボタンの文字を空欄➡黒色➡当月日付を曜日表示
  5. 日曜は赤文字、土曜は青文字
  6. 各コマンドボタンの文字を空欄➡シリアル値表示
  7. 各コマンドボタンの文字(シリアル値の日付)が祝日リストにあったら赤文字
  8. 再度各コマンドボタンの文字を空欄
  9. 今月だけ各コマンドボタンの文字を日で表示する

そしてこのVBAコードの1番の部分以外を【日付表示】ボタンにコピーして書いておくと、コンボボックスで別の年・月を選ぶと表示も変わります。

それでは、順に祝日対応カレンダーフォーム作成VBAコードの説明をします。

スポンサーリンク

現在の日付から西暦と月をリスト化

ユーザーフォームが開いた瞬間にパソコンの時計機能を利用して、コンボボックスに西暦と月をリスト化して既定値にします。

コンボボックスに西暦と月をリスト化

Private Sub UserForm_Initialize()
Dim i As Long, j As Long
For i = -1 To 121
UserForm5.ComboBox1.AddItem Year(Date) – i
Next i
ComboBox1.ListIndex = 1
For j = 1 To 12
UserForm5.ComboBox2.AddItem (j)
Next j
ComboBox2.ListIndex = Month(Date) – 1

詳しくは「コンボボックスに現在の日付から西暦だけを新しい順に表示する」ページに記載しているので、内容が重複するのでここでは省略します。

祝日判定付き自作カレンダーフォーム
祝日判定付き自作カレンダーフォーム(2021年2月4日作成)

画像のように2つのコンボボックスに、上記VBAコードでユーザーフォームが開いたら、西暦と月が表示されます。

通常はこのように西暦と月が表示されたら同時に画像のように各コマンドボタンに日付と色が表示されるように続けてVBAコードを書きたいものですが、そうすると祝日設定や不要な1月31日・3月1日~8日までも表示されてしまうので、次のブロックでは計算式を変数にして、繰り返し処理で使えるように準備しています。

必要項目を変数に置き換える

日付表示をさせるコマンドボタンは当初は42個用意しましたが、実際には最大37個で足りるので今回は37個に減らしています。

そしてこのコマンドボタンに最初から日だけを取り出して表示させてしまうと、ただの数字になってしまい、日付で曜日・祝日・先月・当月・翌月の判断をすることが難しくなります。

そこで、以下の7つの変数を用意してコマンドボタンのキャプションを利用して、曜日・祝日・先月・当月・翌月を判断できるようにします。

必要項目を変数に置き換える

y = ComboBox1.Value
m = ComboBox2.Value
w = DateSerial(y, m, 1)
c = w – Weekday(w) + 1
ds = Weekday(w)
de = DateSerial(y, m + 1, 1) – 1
dc = de – w

ちなみに変数のアルファベットは何でもいいので自分で分かるようにすればいいですよ。

変数「y」

コンボボックス1で選択された西暦(年)を「y」と指定する

変数「m」

コンボボックス2で選択された月を「m」と指定する

変数「w」

DateSerial関数を使用することにより、コンボボックスで選択された西暦(年)、月、1とすることにより、当月1日を取得できる

変数「c」

変数「w」から曜日を表す番号を引いて+1することで、先月末を取得します

変数「ds」

変数「w」の曜日番号を取得して、当月は何曜日が1日なのかを表します。

変数「de」

DateSerial関数を使用することにより、コンボボックスで選択された西暦(年)、月+1、1とすることにより、翌月1日を取得して-1をすることで当月末日を取得できます。

変数「dc」

変数「de」(当月末日)から変数「w」(当月1日)を引くことにより、今月の日数を表します。

曜日を取得する

最初のブロックは、日付表示する37個のコマンドボタンに日曜日は赤色文字、土曜日は青色文字で表示させてそれ以外は黒色文字にする為の準備です。

各ボタンの文字を空欄➡黒色➡当月日付を曜日表示

With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
.ForeColor = RGB(0, 0, 0)
.Caption = Weekday(c + i – 1)
End With

※For i = 1 To 37とNext iで上記コードを挟み込みますが、説明上省略しています。

VBA解説
  1. ユーザーフォーム5のコマンドボタン「i」の
  2. 文字を消去する
  3. 文字色を黒色に指定する
  4. 文字を曜日番号で表す
  5. withステートメント終了

これで全てのコマンドボタンが曜日番号で表示されました。

カレンダー曜日番号
カレンダー曜日番号表示

これで、次に日曜日を表す1番を赤色にして土曜日を表す7番を青色に変えれば祝日以外の色がカレンダーっぽくなりますね。

曜日番号というものがエクセルにはありますので詳細は下記をご覧くださいませ。

日曜は赤文字、土曜は青文字にする

色を変える方法は2つあり、Ifステートメントでもし~だったら、そうでなかったら・・とすることもできますが、今回はSelect Caseを使っています。

日曜は赤文字、土曜は青文字

Select Case UserForm5.Controls(“CommandButton” & i).Caption
Case Is = 1
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(255, 0, 0)
Case Is = 7
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(0, 0, 255)
End Select

VBA解説
  1. Select Caseステートメントを使い、ユーザーフォーム5のコマンドボタン「i」の文字
  2. 1の場合
  3. ユーザーフォーム5のコマンドボタン「i」の文字色を赤色にする
  4. 7の場合
  5. ユーザーフォーム5のコマンドボタン「i」の文字色を青色にする
  6. Selectステートメント終了
カレンダー曜日色

これで、日曜日と土曜日の文字色が変わりましたが、プロパティで設定すれば?と思ったかもしれません。

実は最初はそのようにしたのですが、祝日が土曜日になった時にプロパティが優先されて赤色にならな方ので、この方法に変更しました。

このようにラベルやコマンドボタンなど好みの色に変更することができ、基本となる56色の色見本を用意しました。

各コマンドボタンの文字を空欄にしてシリアル値表示切替

曜日番号で表示されている状態の各コマンドボタンの文字を一旦消去して、祝日を見つけられるように日付をシリアル値にしてボタンに表示させます。

日付をシリアル値で表示

With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
.Caption = CDbl(c + i – 1)
End With

VBA解説
  1. ユーザーフォーム5のコマンドボタン「i」の
  2. 文字を消去する
  3. 文字を先月末から順にシリアル値にして表示
  4. withステートメント終了
カレンダーシリアル値表示
カレンダーシリアル値表示

これで、コマンドボタン1には1月31日のシリアル値からスタートして、順に最後のコマンドボタン37には3月8日となっています。

これで、祝日リストと同じシリアル値があったら赤色文字に変更することで祝日対応ができるようになります。

各ボタンの文字(シリアル値の日付)が祝日リストにあったら赤文字

まず最初にワークシートに祝日のリストを作りますが、カレンダーで調べて祝日をリストアップするのは大変なので、内閣府のホームページにCSVファイルで祝日がリスト化されているので、ダウンロードしてワークシートに貼り付けます。

ダウンロードしたCSVファイルの祝日は1955年~2022年までがリスト化されていますので、ここではセルA列に張り付けて、シリアル値にしています。

カレンダーシリアル値CSV
約70年分976行あります

そして、貼り付けているシート名を「CSV」として、カレンダーフォームのコマンドボタンのシリアル値と一致したら祝日とします。

祝日を探す

Dim syuku As Range
Set syuku = Sheets(“CSV”).Range(“A:A”).Find(what:=UserForm5.Controls(“CommandButton” & i).Caption)
If Not syuku Is Nothing Then
UserForm5.Controls(“CommandButton” & i).ForeColor = RGB(255, 0, 0)
End If

VBA解説
  1. Range型の変数「syuku」を宣言する
  2. シート名「CSV」のセル「A列」からユーザーフォーム5のコマンドボタン「i」の文字と同じ値のセルを検索して、見つけたら変数「syuku」に格納する
  3. 変数「syuku」を見つけたら
  4. ユーザーフォーム5のコマンドボタン「i」の文字色を赤色にする
  5. IFステートメント終了
カレンダーシリアル値祝日色変更
カレンダーシリアル値祝日色変更

シリアル値のままですが、これで祝日の表示となるコマンドボタンの文字色が赤色に変更でき、祝日対応できるようになりました。

シリアル値を日に変えて再表示する

最後にシリアル値で表示されている各コマンドボタンの表示文字を消去して、当月1日~当月末日のみをコマンドボタンに表示させます。

今月1日や月末日を自動取得する方法は下記をご覧くださいませ。

当月のみ日にち表示

With UserForm5.Controls(“CommandButton” & i)
.Caption = “”
End With
Next i
For i = ds To ds + dc
With UserForm5.Controls(“CommandButton” & i)
.Caption = Day(c + i – 1)
End With

VBA解説
  1. ユーザーフォーム5のコマンドボタン「i」の
  2. 表示する文字を消去する
  3. withステートメント終了
  4. 繰り返し処理をする
  5. 変数「i」を宣言
  6. 変数は「ds」~「ds」+「dc」
  7. ユーザーフォーム5のコマンドボタン「i」の
  8. 表示文字を日で表す
  9. withステートメント終了

ここで当月のみ表示させるために、コマンドボタン1~37で変数を使ってしまうと1月と3月まで表示されるので、変数「ds」を1の代わりにして、当月最初の曜日番号から当月最初の曜日番号+月末までとします。

そしてDay(c + i – 1)で日付から日だけ取り出して表示させて完成です。

祝日判定付き自作カレンダーフォーム
祝日判定付き自作カレンダーフォーム

これで祝日対応のカレンダーフォームが出来上がりました。

あとは、各コマンドボタンをクリックしたら、指定したセルに記述するようにVBAコードを書き込めば完成です。

ちなみに日付を取り扱うことがエクセルを使っていれば多くなると思いますので、セルの値から取得する方法など下記にまとめていますので、参考にいかがですか?

また、今回は西暦表示でしたが、和暦表示もちょっと工夫をすれば表示リスト化できますよ。