－ちょっと使えるネタを紹介－
Excel VBAを使って、祝日も反映した「縦型の月間カレンダー」を作成する方法について、解説していきます。さらにデータベースと連携することで、予定表の管理をすることができます。縦型の月間カレンダーの作成と、データベースとの連携について、マスターしていきましょう。
この記事では、縦型の月間カレンダーを作成する方法について、ご紹介します。
1か月分の日付を入力して、表示形式の設定や、罫線、セルの塗りつぶしをすることで、縦型の月間カレンダーを作成することができます。
縦型の月間カレンダーと、データベースを連携させると、作成したカレンダーで予定表の管理ができるようになります。
縦型の月間カレンダーの作成と、データベースとの連携について、マスターしていきましょう。
では、縦型の月間カレンダーを作成する方法について、解説していきます。
この記事を読むメリット
目次から見たい項目へ移動すると便利ですよ。
目次
やりたい内容は、祝日を反映した「縦型の月間カレンダー」を作成したい、ということになります。
次のような縦型の月間カレンダーを作成してみます。
縦方向に日付を入力したカレンダーになります。
作成したカレンダーに、祝日も反映させてみます。
こんな感じで、祝日もカレンダーに反映してみます。
祝日のデータは、シートに入力したデータを取得します。
国民の祝日は、内閣府のサイトにありましたので、そちらをコピーして使うといいです。
カレンダーの更新は、「翌月」「先月」「今月」のボタンで、できるようにしてみます。
ボタンのクリックで、カレンダーを更新できます。
ボタンで、翌月に更新できます。
ボタンで、先月に更新できます。
ボタンで、今月に更新できます。
という感じで、カレンダーを更新できるようにしてみます。
では、縦型の月間カレンダーを作成していきます。
1か月分の日付を入力するVBAコードになります。
Sub MakeCal()
Range("4:100").Clear
Range("A4") = "日付"
Range("B4") = "曜日"
Range("C4") = "登録データ"
Dim A, B, C
A = DateSerial(Range("A2"), Range("A3"), 1) '指定月の1日
B = DateSerial(Range("A2"), Range("A3") + 1, 0) '指定月の月末
ReDim C(1 To 31, 1 To 2)
k = 0
For i = A To B
k = k + 1
C(k, 1) = i '日付を入力
C(k, 2) = i '日付を入力
Next
Range("A5").Resize(31, 2) = C 'セルに入力
End Sub
最初に、年と月は、入力しておきます。
では、VBAコードを実行してみます。
実行するとまず、見出しが入力されます。
次に、1か月分の日付が入力されます。
日付と曜日で使う日付を、1か月分入力します。
列幅を大きくして確認してみます。
こんな感じで、1か月分のデータが入力されています。
次は、縦型の月間カレンダーの書式を設定していきます。
表示形式と、罫線、そして土日の塗りつぶしをするVBAコードです。
Sub SetFormat()
Range("A5").Resize(31).NumberFormatLocal = "d" '「日」を表示
Range("B5").Resize(31).NumberFormatLocal = "aaa" '「曜日」を表示
Range("A4").Resize(32, 3).Borders.LineStyle = xlContinuous '罫線
Dim A
For Each A In Range("A5").Resize(31)
If Format(A, "aaa") = "日" Then
'日曜日を塗りつぶし
A.Resize(, 3).Interior.Color = RGB(252, 228, 214)
ElseIf Format(A, "aaa") = "土" Then
'土曜日を塗りつぶし
A.Resize(, 3).Interior.Color = RGB(221, 235, 247)
End If
Next
End Sub
では、VBAコードを実行してみます。
日付のところを、日にちの表示形式に変更します。
そして、曜日の表示形式を設定します。
表示形式の設定ができました。
次は、罫線を引きます。
次は、土日を塗りつぶししていきます。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
日曜日を塗りつぶしします。
これで、縦型の月間カレンダーを作成できます。
縦型の月間カレンダーを作成できました。
次は、作成した縦型の月間カレンダーに、祝日を反映していきます。
祝日のデータは、シートに入力しておきます。
こちらの祝日のデータを、カレンダーに反映させていきます。
では、祝日のデータをカレンダーに反映させていきます。
祝日のデータをカレンダーに反映させる手順は、次のようになります。
という感じです。
祝日のデータをカレンダーに反映させるVBAコードです。
Sub GetHoliday()
Dim A, B
With Sheets("月")
A = DateSerial(.Range("A2"), .Range("A3"), 1) '指定月の1日
B = DateSerial(.Range("A2"), .Range("A3") + 1, 0) '指定月の月末
End With
With Sheets("祝日")
'指定月でフィルタ
.Range("A1").AutoFilter 3, ">=" & A, xlAnd, "<=" & B
.Range("A1").CurrentRegion.Copy .Range("E1") 'フィルタ結果をコピー
.Range("A1").AutoFilter 'フィルタを解除
End With
For i = 2 To Sheets("祝日").Cells(Rows.Count, "G").End(xlUp).Row
With Sheets("月").Range("A4").Offset(Day(Sheets("祝日").Cells(i, "G")), 0)
'祝日を塗りつぶし
.Resize(, 3).Interior.Color = RGB(252, 228, 214)
End With
Next
'コピーした値をクリア
Sheets("祝日").Range("E1").CurrentRegion.ClearContents
End Sub
まずは、指定した年月で祝日のデータを抽出します。
祝日のデータを、2022年5月でフィルタします。
フィルタ結果を別セルにコピーします。
フィルタを解除します。
抽出した祝日をループして、カレンダーを塗りつぶししていきます。
抽出した値をループしていきます。
1つ目の日付に対応するセル範囲を、塗りつぶしします。
2つ目の日付に対応するセル範囲を、塗りつぶしします。
3つ目の日付に対応するセル範囲を、塗りつぶしします。
という感じで祝日をカレンダーに反映させます。
抽出した値は不要なので、クリアします。
これで、縦型の月間カレンダーに、祝日を反映できます。
縦型の月間カレンダーに、祝日を反映できました。
カレンダーを簡単に更新できるように、年と月の更新で実行するイベントを作成します。
年と月を変更でカレンダーを更新できるように、「Change」イベントを作成します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A3"), Target) Is Nothing Then Exit Sub
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を反映
End Sub
では、年や月を変更してみます。
年を変更すると、カレンダーを更新できます。
月を変更すると、カレンダーを更新できます。
というような感じで、年や月を変更したタイミングで、カレンダーを更新することができます。
次に、「翌月」「先月」「今月」のボタンで、カレンダーを更新できるようにしてみます。
ボタンでカレンダーを更新できるようになると、かなり便利です。
「翌月」「先月」「今月」のボタンを作成します。
翌月、先月、今月のボタンに、登録するVBAコードは次のようになります。
↓翌月に更新するVBAコード
Sub NextMonth() Dim A '1か月進める A = DateSerial(Range("A2"), Range("A3") + 1, 1) Range("A2") = Year(A) '年を取得 Range("A3") = Month(A) '月を取得 End Sub
↓先月に更新するVBAコード
Sub PreMonth() Dim A '1か月戻す A = DateSerial(Range("A2"), Range("A3") - 1, 1) Range("A2") = Year(A) '年を取得 Range("A3") = Month(A) '月を取得 End Sub
↓今月に更新するVBAコード
Sub ThisMonth()
Range("A2") = Year(Now()) '今年を取得
Range("A3") = Month(Now()) '今月を取得
End Sub
という感じで登録します。
では、ボタンでカレンダーを更新してみます。
ボタンをクリックで、翌月に更新できます。
ボタンをクリックで、先月に更新できます。
ボタンをクリックで、今月に更新できます。
という感じで、ボタンでカレンダーを更新できました。
次は、データベースからカレンダーに、データを取得する機能を追加してみます。
データベースと連携することで、作成したカレンダーで予定表の管理ができるようになります。
こんな感じで、データベースから、値を取得できる機能を追加してみます。
では、作成する方法について、解説していきます。
まず、適当にデータベースを作成しておきます。
データベースのシートを追加します。
データベースに値を入力しておきます。
データベースからカレンダーに値を取得する手順は、次のようになります。
という感じです。
データベースからカレンダーに値を取得するVBAコードは、次のようになります。
Sub GetData()
Dim A, B
With Sheets("月")
A = DateSerial(.Range("A2"), .Range("A3"), 1) '指定月の1日
B = DateSerial(.Range("A2"), .Range("A3") + 1, 0) '指定月の月末
End With
With Sheets("DB")
'指定月をフィルタ
.Range("A1").AutoFilter 1, ">=" & A, xlAnd, "<=" & B
.Range("A1").CurrentRegion.Copy .Range("D1") 'フィルタ結果をコピー
.Range("A1").AutoFilter 'フィルタ解除
End With
For i = 2 To Sheets("DB").Cells(Rows.Count, "D").End(xlUp).Row
With Sheets("月").Range("A4")
'データを取得
.Offset(Day(Sheets("DB").Cells(i, "D")), 2) = Sheets("DB").Cells(i, "E")
End With
Next
'コピー結果をクリア
Sheets("DB").Range("E1").CurrentRegion.ClearContents
End Sub
では、VBAコードを実行してみます。
2022年5月で、データベースをフィルタします。
フィルタ結果をコピーします。
オートフィルタを解除します。
これで、指定した年月でデータを抽出できます。
抽出したデータをループして、データをカレンダーに転記していきます。
抽出したデータをループしていきます。
1つ目のデータを転記します。
2つ目のデータを転記します。
最後に、抽出データをクリアします。
これで、データベースから、登録データを取得できます。
データベースから、登録データを取得できました。
毎回VBAコードを実行していると面倒なので、カレンダーを更新したタイミングで、データベースから値を取得できるようにします。
年もしくは月を変更したタイミングで、実行するイベントコードに、値を取得するVBAコードを記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A3"), Target) Is Nothing Then Exit Sub
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を反映
Call GetData 'データを取得
End Sub
では、カレンダーを更新して、自動で値を取得してみます。
今月をクリックで、データベースの値を自動で取得できます。
先月をクリックで、データベースの値を自動で取得できます。
翌月をクリックで、データベースの値を自動で取得できます。
こんな感じで、カレンダー更新のタイミングで、自動で値を取得することができます。
さらに、カレンダーからデータベースに、データを書き込みする機能を追加してみます。
これで、カレンダーとデータベースを連携することができます。
先に、追加する書き込み機能をみてみます。
カレンダーに値を入力します。
ボタンをクリックします。
データベースに値を書き込みできます。
こんな感じで、カレンダーからデータベースに書き込みすることができます。
書き込みする手順は、次のようになります。
という感じです。
この流れを、カレンダーのすべての日付でループします。
VBAコードは、次のようになります。
Sub WriteData() Dim Flag, A 'カレンダーをループ For Each A In Sheets("月").Range("A5").Resize(31) Flag = 0 '既存のデータを変更 For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row If A = Sheets("DB").Cells(i, "A") Then Sheets("DB").Cells(i, "B") = A.Offset(0, 2) '登録データ Flag = 1 Exit For End If Next '新規に登録 If Flag = 0 And A.Offset(0, 2) <> "" Then With Sheets("DB").Cells(Rows.Count, "A").End(xlUp) '最終行を取得 .Offset(1, 0) = A '日付 .Offset(1, 1) = A.Offset(0, 2) '登録データ End With End If Next End Sub
データベースに値を入力しておきます。
カレンダーの値を変更してみます。
では、VBAコードを実行してみます。
カレンダーの日付と、データベースをループして登録していきます。
カレンダーの日付をループしていって、データベースに登録していきます。
まずはカレンダーの1つ目の日付で、データベースをループしていきます。
日付が登録されている場合は、登録データを上書きします。
カレンダーからデータベースに書き込みできました。
データベースに登録されていない場合は、新規で書き込みします。
新しくデータを入力してみます。
では、再度VBAコードを実行してみます。
カレンダーに入力があって、データベースに日付が登録されていない場合は、新規で書き込みをします。
カレンダーの日付を、新規で書き込みをします。
カレンダーのデータを、新規で書き込みをします。
これで、カレンダーの値を、データベースに新規で書き込みできます。
カレンダーの値を、データベースに新規で書き込みできました。
最後に、書き込みのボタンを追加して、VBAコードを登録します。
書き込みボタンを追加します。
登録するVBAコードは、先ほどのVBAコードになります。
Sub WriteData() Dim Flag, A 'カレンダーをループ For Each A In Sheets("月").Range("A5").Resize(31) Flag = 0 '既存のデータを変更 For i = 2 To Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Row If A = Sheets("DB").Cells(i, "A") Then Sheets("DB").Cells(i, "B") = A.Offset(0, 2) '登録データ Flag = 1 Exit For End If Next '新規に登録 If Flag = 0 And A.Offset(0, 2) <> "" Then With Sheets("DB").Cells(Rows.Count, "A").End(xlUp) '最終行を取得 .Offset(1, 0) = A '日付 .Offset(1, 1) = A.Offset(0, 2) '登録データ End With End If Next End Sub
では、書き込みボタンで書き込みしてみます。
カレンダーにデータを入力してみます。
書き込みボタンをクリックします。
ボタンクリックで、データベースに値を登録できます。
データベースに値を登録できました。
こんな感じで、データベースから値の取得と、データベースへの書き込みができると、カレンダーとデータベースを連携することができます。
この記事では、縦型の月間カレンダーを作成する方法について、ご紹介しました。
1か月分の日付を入力して、表示形式の設定や、罫線、セルの塗りつぶしをすることで、縦型の月間カレンダーを作成することができます。
縦型の月間カレンダーと、データベースを連携させると、作成したカレンダーで予定表の管理ができるようになります。
縦型の月間カレンダーの作成と、データベースとの連携について、マスターしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する