Sub SetFormat()
'日曜日の背景色
Range("B5").Resize(3).Interior.Color = RGB(252, 228, 214)
'土曜日の背景色
Range("H5").Resize(3).Interior.Color = RGB(221, 235, 247)
'平日の背景色
Range("C5").Resize(3, 5).Interior.Color = xlNone
'罫線
Range("A5").Resize(3, 8).Borders.LineStyle = xlContinuous
'表示形式
Range("B6:H6").NumberFormatLocal = "m/d"
End Sub
では、VBAコードを実行してみます。
書式を設定していく
日曜日を塗りつぶしします。
土曜日を塗りつぶしします。
平日は塗りつぶしなしにします。
罫線を引きます。
日付の表示形式を「"m/d"」に設定します。
これで、書式を設定できます。
カレンダーの書式を設定できた
書式を設定できました。
祝日の反映
次は、祝日の反映をしていきます。
祝日のデータを入力
祝日のデータはあらかじめ、シートに入力しておきます。
祝日のデータを入力しておく
内閣府のサイトにデータがありましたので、コピーして使うといいです。
祝日をカレンダーに反映
祝日をカレンダーに反映させる手順は、次のようになります。
指定した週の祝日を抽出
抽出した祝日をループしてカレンダーに反映
という感じになります。
祝日をカレンダーに反映させるVBAコードは、次のようになります。
Sub GetHoliday()
Dim A, B
With Sheets("週")
A = .Range("B6") '指定週の日曜日
B = .Range("H6") '指定週の土曜日
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("A6").Offset(0, Weekday(Sheets("祝日").Cells(i, "G"))) '日付の位置を取得
.NumberFormatLocal = "m/d(" & Sheets("祝日").Cells(i, "F") & ")" '表示形式を設定
.Offset(-1, 0).Resize(3).Interior.Color = RGB(252, 228, 214) '背景色を設定
End With
Next
Sheets("祝日").Range("E1").CurrentRegion.ClearContents 'フィルタ結果をクリア
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A4"), Target) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を設定
Application.ScreenUpdating = True
End Sub
年を変更して、カレンダーを更新できます。
年を変更して、カレンダーを更新
年を変更して、カレンダーを更新できます。
月を変更して、カレンダーを更新
週を変更して、カレンダーを変更できます。
週を変更して、カレンダーを更新
こんな感じで、年や月や週の変更で、カレンダーを更新することができます。
「翌週」「先週」「今週」のボタンで更新
「翌週」「先週」「今週」のボタンで更新できるようにすると、さらに便利になります。
「翌週」「先週」「今週」のボタンを作成
「翌週」「先週」「今週」のボタンを作成します。
ボタンを作成
登録するVBAコードは、次のようになります。
登録するVBAコード
↓「翌週」に更新するVBAコードです。
Sub NextWeek()
Range("A4") = Range("A4") + 1 '1週進める
Dim A
A = Range("H6") '土曜日の日付を取得
B = DateSerial(Year(A), Month(A), 1) '「1日」の日付を取得
Range("A2") = Year(A) '年
Range("A3") = Month(A) '月
Range("A4") = DateDiff("ww", B, A) + 1 '週
End Sub
Sub PreWeek()
Range("A4") = Range("A4") - 1
Dim A
A = Range("H6") '土曜日の日付を取得
B = DateSerial(Year(A), Month(A), 1) '「1日」の日付を取得
Range("A2") = Year(A) '年
Range("A3") = Month(A) '月
Range("A4") = DateDiff("ww", B, A) + 1 '週
End Sub
↓「今週」に更新するVBAコードです。
Sub ThisWeek()
Range("A2") = Year(Date) '今年を取得
Range("A3") = Month(Date) '今月を取得'週を取得
Range("A4") = DateDiff("ww", DateSerial(Year(Date), Month(Date), 1), Date) + 1
End Sub
上記のVBAコードを、ボタンに登録します。
ボタンでカレンダーを更新
では、ボタンをクリックして、カレンダーを更新してみます。
ボタンをクリックで、「翌週」を表示できます。
ボタンクリックで、「翌週」を表示
ボタンをクリックで、「先週」を表示できます。
ボタンクリックで、「先週」を表示
ボタンをクリックで、「今週」を表示できます。
ボタンクリックで、「今週」を表示
という感じで、ボタンをクリックして、カレンダーを更新することができます。
データを取得する機能を追加
次は、カレンダーにデータを取得する機能を追加してみます。
データを取得する機能
データベースからデータを取得して、カレンダーに反映させる、という感じです。
データベースから値を取得する機能を追加
データベースから値を取得します。
次のように、カレンダーに反映させることができます。
こんな感じで、データベースから値を取得する機能を追加してみます。
データを取得する
データベースからデータを取得する手順は、次のようになります。
指定した週でデータベースのデータを抽出
抽出したデータをループして、カレンダーに反映
という感じです。
データベースから値を取得して、カレンダーに反映させるVBAコードになります。
Sub GetData()
Dim A, B
With Sheets("週")
A = .Range("B6") '週初めの日付
B = .Range("H6") '週末の日付
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("A7")
.Offset(0, Weekday(Sheets("DB").Cells(i, "D"))) = Sheets("DB").Cells(i, "E")
End With
Next
Sheets("DB").Range("D1").CurrentRegion.ClearContents 'フィルタ結果をクリア
End Sub
データベースのシートは、あらかじめ作成しておきます。
データベースのシートを作成しておく
データベースにデータを入力しておきます。
では、VBAコードを実行してみます。
指定した週のデータを抽出
指定した週でデータベースをフィルタします。
フィルタ結果をコピーします。
フィルタを解除します。
オートフィルタを使って、指定した週のデータを抽出することができます。
次は、抽出した値をループしてカレンダーに反映していきます。
抽出した値をループしてカレンダーに反映
抽出した値をループしていきます。
カレンダーに値を反映させます。
カレンダーへ反映が終了したら、抽出した値をクリアします。
これで、データベースからデータを取得できます。
データベースから値を取得できた
データベースからデータを取得できました。
年と月と週を変更で実行するイベントに追加
年と月と週を変更で実行するイベントに追加すると、自動で値を取得できるようになります。
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A2:A4"), Target) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Call MakeCal 'カレンダーを作成
Call SetFormat '書式を設定
Call GetHoliday '祝日を設定
Call GetData 'データを取得
Application.ScreenUpdating = True
End Sub
では、カレンダーを更新してみます。
カレンダー更新で、データベースから自動で値を取得
カレンダー更新のタイミングで、データベースから自動で値を取得できました。
データを書き込みする機能を追加
次は、データを書き込みする機能を追加してみます。
データの取得と書き込みができれば、カレンダーとデータベースを連携することができます。
追加する書き込み機能
追加する書き込み機能をみてみます。
カレンダーのデータを変更してみます。
カレンダーを変更してみます。
書き込みボタンで、データベースに書き込みすることができます。
書き込みボタンで、データベースに書き込み
書き込みボタンをクリックします。
データベースに書き込みができます。
データベースに書き込みができました。
では、書き込みする手順をみていきます。
書き込みする手順は、次のようになります。
カレンダーの日付をループ
登録済みはデータベースに書き込み
未登録で入力なしは、次の日付に移動
未登録で入力ありは、新規で書き込み
という感じです。
書き込みする手順
データベースに書き込みをするVBAコードは、次のようになります。
まずは、赤色の部分をみていきます。
Sub WriteData()
Dim A
For Each A In Sheets("週").Range("B6:H6")
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
Next
Sheets("DB").Range("A1").AutoFilter 'フィルタを解除
End Sub
では、カレンダーのデータを変更しておきます。
値を変更しておく
では、VBAコードを実行してみます。
日付をループしていきます。
日付をループしていく
登録済みの場合は、値を書き込みします。
登録済みは値を書き込み
日付でデータベースをフィルタします。
登録済みなので、値を書き込みします。
データベースに書き込みできました。
未登録で入力なしは、次の日付に移動します。
未登録で入力なしは次の日付
次の日付でデータベースをフィルタします。
未登録で、入力なしなので、次の日付に移動します。
未登録で、入力なしの場合は、書き込みはしません。
同じ流れを、最後の日付までループしていきます。
最後の日付までループ
最後の日付までループして、終了です。
最後に、フィルタを解除します。
これで、値を書き込みできます。
登録済みの日付に書き込みできた
値を書き込みできました。
新規で書き込みする手順
新規で書き込みする手順についてもみていきます。
新規で書き込みをする場合は、赤色の部分が実行されます。
Sub WriteData()
Dim A
For Each A In Sheets("週").Range("B6:H6")
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
Next
Sheets("DB").Range("A1").AutoFilter 'フィルタを解除
End Sub
では、カレンダーに値を入力しておきます。
カレンダーに値を入力
では、VBAコードを実行してみます。
日付をループしていきます。
日付をループしていく
未登録で入力ありは、新規で書き込みされます。
未登録で入力ありは、新規で書き込み
データベースを日付でフィルタします。
未登録で、入力ありなので、新規で書き込みをしていきます。
データベースのフィルタを解除します。
日付をデータベースに書き込みします。
値をデータベースに書き込みします。
データベースのフィルタを解除します。
これで、データベースに新規で書き込みができます。
新規で書き込みできた
データベースに新規で書き込みができました。
書き込みのボタンを追加
書き込みをする際は、ボタンを使うと便利になります。
書き込みのボタンを追加してみます。
書き込みボタンを追加
登録するVBAコードは、先ほどのVBAコードを登録します。
Sub WriteData()
Dim A
For Each A In Sheets("週").Range("B6:H6")
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
Next
Sheets("DB").Range("A1").AutoFilter 'フィルタを解除
End Sub