Sub MakeCal()
Range("A4").Resize(14, 7).Clear '入力をクリア
Dim A
A = Array("日", "月", "火", "水", "木", "金", "土")
Dim B, C, D
ReDim B(1 To 12, 1 To 7)
k = 1
C = DateSerial(Range("A2"), Range("A3"), 1) '指定月の1日
D = DateSerial(Range("A2"), Range("A3") + 1, 0) '指定月の月末
For i = C To D
'日曜日でカウントアップ、1日はカウントアップしない
If Weekday(i) = 1 And Day(i) <> 1 Then k = k + 2
B(k, Weekday(i)) = Format(i, "yyyy/m/d") '日付を入力
Next
Range("A4").Resize(1, 7) = A
Range("A5").Resize(12, 7) = B
End Sub
年と月をセルに入力しておきます。
年と月を入力しておく
では、VBAコードを実行してみます。
VBAコードを実行
実行すると、日曜から土曜の日付が入力されます。
次に、1か月分の日付が入力されます。
まずは、曜日と1か月分の日付を入力できました。
書式を入力
次は、カレンダーの書式を入力していきます。
書式を入力するVBAコードになります。
Sub SetFormat()
'日曜日の背景色
Range("A4").Resize(13).Interior.Color = RGB(252, 228, 214)
'土曜日の背景色
Range("G4").Resize(13).Interior.Color = RGB(221, 235, 247)
'罫線
Range("A4").Resize(13, 7).Borders.LineStyle = xlContinuous
For i = 0 To 5
'表示形式
Range("A5:G5").Offset(i * 2, 0).NumberFormatLocal = "d"
Next
End Sub
では、VBAコードを実行してみます。
日曜と土曜の塗りつぶし
日曜日の塗りつぶしをします。
土曜日の塗りつぶしをします。
次に、罫線を引きます。
罫線を引く
罫線を引きました。
日付の表示形式を「"m/d"」に設定していきます。
日付の表示形式を設定
1週目~6週目をループして、日付の表示形式を設定していきます。
まずは、1週目を「"m/d"」形式に変更します。
2週目の日付の表示形式も、「"m/d"」に設定します。
3週目の日付の表示形式も、「"m/d"」に設定します。
4週目の日付の表示形式も、「"m/d"」に設定します。
5週目の日付の表示形式も、「"m/d"」に設定します。
最後に、6週目の日付の表示形式を、「"m/d"」に設定します。
これで、2022年5月の月間カレンダーが完成です。
月間のカレンダーができた
まずは、祝日以外を反映して、月間のカレンダーを作成することができました。
祝日の反映
では、祝日を月間のカレンダーに反映していきます。
祝日のデータを入力
別シートに祝日を入力しておきます。
祝日を入力しておく
国民の祝日は、内閣府のサイトに公開されていますので、そちらからコピーして取得するといいです。
祝日をカレンダーに反映
シートに入力した祝日をカレンダーに反映する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("A5:G16").Find(Sheets("祝日").Cells(i, "G")) '日付の位置を取得
.NumberFormatLocal = "d(" & Sheets("祝日").Cells(i, "F") & ")" '表示形式を設定
.Resize(2).Interior.Color = RGB(252, 228, 214) '背景色を設定
End With
Next
Sheets("祝日").Range("E1").CurrentRegion.ClearContents 'フィルタ結果をクリア
End Sub
では、VBAコードを実行してみます。
「5月」でフィルタして、別セルにコピー
祝日のシートで、日付を「5月」でフィルタします。
フィルタ結果を、別セルにコピーします。
フィルタを解除します。
まずは、2022年の5月分の祝日を取得しました。
このコピーした日付をループしていきます。
日付をループしていく
カレンダーのシートで、日付を探して、「表示形式の設定」と「塗りつぶし」をしていきます。
日付を探して「表示形式の設定」と「塗りつぶし」をする
日付を探したら、表示形式で祝日を表示します。
祝日を塗りつぶしします。
同じように、日付を探したら、表示形式で祝日を表示します。
祝日を塗りつぶしします。
次の日付を探したら、表示形式で祝日を表示します。
そして、祝日を塗りつぶしします。
祝日を塗りつぶししました。
これで、祝日をカレンダーに反映することができます。
コピーした値は、不要なのでクリアしておきます。
コピーした値をクリア
これで、祝日を反映したカレンダーを作成できます。
祝日を反映してカレンダーを作成できた
祝日を反映してカレンダーを作成できました。
年と月の更新で実行するイベントを作成
年と月の更新がしやすいように、年と月の更新で実行するイベントを作成します。
年と月を更新でカレンダーを作成
年と月を更新でカレンダーを作成するイベントのVBAコードを作成します。
A2もしくは、A3のセルが変更されたら実行するイベントプロシージャになります。
「月」のシートの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 '祝日を取得
End Sub
Sub NextMonth()
Dim A
'1か月進める
A = DateAdd("m", 1, DateSerial(Range("A2"), Range("A3"), 1))
Range("A2") = Year(A) '年を取得
Range("A3") = Month(A) '月を取得
End Sub
↓「先月」に更新するVBAコードです。
Sub PreMonth()
Dim A
'1か月戻す
A = DateAdd("m", -1, DateSerial(Range("A2"), Range("A3"), 1))
Range("A2") = Year(A) '年を取得
Range("A3") = Month(A) '月を取得
End Sub
↓「今月」に更新するVBAコードです。
Sub ThisMonth()
Range("A2") = Year(Now()) '今年を取得
Range("A3") = Month(Now()) '今月を取得
End Sub
A2やA3のセルに値を入力して、先ほどのChangeイベントで、値を更新するという感じです。
ボタンでカレンダーを更新
では、ボタンでカレンダーを更新してみます。
「翌月」のボタンをクリックしてみます。
「翌月」をクリック
「翌月」ボタンで、翌月のカレンダーを作成できました。
「先月」のボタンをクリックしてみます。
「先月」をクリック
「先月」ボタンで、先月のカレンダーを作成できました。
「今月」のボタンをクリックしてみます。
「今月」をクリック
「今月」ボタンで、今月のカレンダーを作成できました。
という感じで、ボタンを使って月を移動できると便利です。
データを取得する機能を追加
カレンダーに、データを取得する機能を追加してみます。
データを取得する機能
データベースから値を取得して、カレンダーに反映するという感じの機能になります。
データベースの値をカレンダーに反映したい
別シートに入力した値を、カレンダーに入力するという感じになります。
実行すると、データベースの値をカレンダーに入力することができます。
カレンダーからデータベースの値を取得することができます。
データを取得する
では、カレンダーからデータベースの値を取得する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("A5:G16").Find(Sheets("DB").Cells(i, "D")) '日付の位置を取得
.Offset(1, 0) = Sheets("DB").Cells(i, "E") '予定を入力
End With
Next
Sheets("DB").Range("D1").CurrentRegion.ClearContents 'フィルタ結果をクリア
End Sub
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 A
For Each A In Sheets("月").Range("A5:G16")
If IsDate(A) Then '日付の場合
If Month(A) = Sheets("月").Range("A3") Then '当月の場合
Sheets("DB").Range("A1").AutoFilter 1, Format(A, "yyyy/m/d") '日付でフィルタ'データが登録済み
If Application.Subtotal(103, Sheets("DB").Range("A:A")) > 1 Then
Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = A.Offset(1, 0)
Else 'データが未登録
If A.Offset(1, 0) <> "" Then '値が入力されている
Sheets("DB").Range("A1").AutoFilter 1 'フィルタ解除
With Sheets("DB").Cells(Rows.Count, "A").End(xlUp) '最終行
.Offset(1, 0) = A '日付を入力
.Offset(1, 1) = A.Offset(1, 0) '値を入力
End With
End If
End If
End If
End If
Next
Sheets("DB").Range("A1").AutoFilter 'フィルタを解除
End Sub
カレンダーの値を変更しておきます。
カレンダーの値を変更しておく
では、VBAコードを実行してみます。
実行すると、カレンダーをループしていきます。
カレンダーをループする
カレンダーに入力されている値が、「日付」の場合にデータベースをフィルタします。
探した日付でフィルタする
登録データがなしで、カレンダーにデータが入力されていない場合は、次のセルに移動します。
未登録でデータ入力なしは何もしない
入力なしの場合は、書き込みはしません。
登録されている場合はデータを更新
カレンダーの日付でデータベースをフィルタして、登録データがある場合は、データを登録します。
カレンダーの値を、データベースに登録します。
カレンダーの値を、データベースに登録しました。
次は、新規で書き込みの場合をみてみます。
カレンダーに値を入力しておく
カレンダーに新規で値を入力しておきます。
では、VBAコードを実行してみます。
未登録の場合は新規でデータを登録
カレンダーの日付でデータベースをフィルタします。
カレンダーにデータが入力されていたら、データベースのフィルタを解除します。
カレンダーの日付を、データベースに登録します。
同じように、カレンダーの値をデータベースに登録します。
これで、カレンダーの日付と値を、新規でデータベースに登録できます。
書き込みのボタンを追加
データベースへの書き込みは、ボタンを追加すると便利です。
書き込みボタンを追加します。
書き込みボタンを追加
登録するVBAコードは、先ほどの書き込みのVBAコードになります。
登録するVBAコード
Sub WriteData()
Dim A
For Each A In Sheets("月").Range("A5:G16")
If IsDate(A) Then '日付の場合
If Month(A) = Sheets("月").Range("A3") Then '当月の場合
Sheets("DB").Range("A1").AutoFilter 1, Format(A, "yyyy/m/d") '日付でフィルタ'データが登録済み
If Application.Subtotal(103, Sheets("DB").Range("A:A")) > 1 Then
Sheets("DB").Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = A.Offset(1, 0)
Else 'データが未登録
If A.Offset(1, 0) <> "" Then '値が入力されている
Sheets("DB").Range("A1").AutoFilter 1 'フィルタ解除
With Sheets("DB").Cells(Rows.Count, "A").End(xlUp) '最終行
.Offset(1, 0) = A '日付を入力
.Offset(1, 1) = A.Offset(1, 0) '値を入力
End With
End If
End If
End If
End If
Next
Sheets("DB").Range("A1").AutoFilter 'フィルタを解除
End Sub