コピペするだけ！ExcelVBAでカレンダーコントロールを自作する

URLをコピーしました！

カレンダーコントロールはExcel 2010から無くなった

カレンダーコントロールとは以下のようなものです（実際はAccessの日付選択コントロール）

Excel 2007まではカレンダーコントロールは標準機能として搭載されていましたが、Excel 2010からは廃止されてしまいました。

カレンダーコントロール (mscal.ocx) は、Access ワークシートで使用できる Microsoft Access の機能でした。Access 2010 ではカレンダーコントロールが削除されており、Excel 2013 で使用できません。代わりに、日付の選択を使用するか、独自のカスタムカレンダーコントロールを使用します。
Excel 2010での変更点 | Microsoft Docsより

Accessだとテキストボックスの書式を「日付」に設定しておくことで、入力する際にカレンダーから日付を選択することが出来ますが、Excelでは独自のカスタムカレンダーコントロールを自作するしかありません。

自作のカレンダーコントロールはインターネット上で幾つも紹介されていますが、社内規定でファイルをダウンロード出来ないところも多いと思います。

というわけで、コードをコピペするだけでフォームも自作できるようなサンプルを作成しましたので公開します。

⇧ 目次へ戻る

完成イメージ

完成イメージはこちら。

選択されたセル（アクティブセル）にカレンダーで選択した日付をセットします。

⇧ 目次へ戻る

ソースコード

Exccelを起動してAlt + F11でVBEを立ち上げます。

「Sheet1」を右クリックし、メニューから「挿入」＞「標準モジュール」を選択（クリック）して下さい。

右側にパネルが現れますので、そこに以下のソースコードをコピペします。

Private Sub CreateCalendarForm()
    Dim myForm As Object
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(ComponentType:=3)  '' vbext_ct_MSForm
    With myForm
        .Name = "CalendarForm"
        .Properties("Height") = 310
        .Properties("Width") = 310
        .Properties("Caption") = "日付を選択してセルに入力"
    End With
    Dim myFormDesign As Object
    Set myFormDesign = myForm.Designer
    With myFormDesign.Controls.Add("Forms.TextBox.1")
        .Name = "TXT日付"
        .Width = 144
        .Height = 24
        .Top = 6
        .Left = 78
        .BackColor = 16777215
        .BackStyle = 1
        .ForeColor = 0
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 0
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .MaxLength = 10
        .IMEMode = 3
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD先月"
        .Width = 30
        .Height = 24
        .Top = 6
        .Left = 42
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 1
        .Caption = "<"
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD翌月"
        .Width = 30
        .Height = 24
        .Top = 6
        .Left = 228
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 2
        .Caption = ">"
    End With
    With myFormDesign.Controls.Add("Forms.CommandButton.1")
        .Name = "CMD今日"
        .Width = 48
        .Height = 24
        .Top = 252
        .Left = 126
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 10.2
        .Font.Name = "MS UI Gothic"
        .TabIndex = 3
        .Caption = "今日"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label43"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 4
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "日"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label44"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 5
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "月"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label45"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 6
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "火"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label46"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 7
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "水"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label47"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 8
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "木"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label48"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 9
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "金"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label49"
        .Width = 36
        .Height = 20
        .Top = 40
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 0
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 10
        .BorderColor = -2147483633
        .BorderStyle = 1
        .SpecialEffect = 0
        .TextAlign = 2
        .Caption = "土"
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label1"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 13
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label2"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 14
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label3"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 15
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label4"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 16
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label5"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 17
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label6"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 18
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label7"
        .Width = 36
        .Height = 30
        .Top = 66
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 19
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label8"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 20
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label9"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 21
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label10"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 22
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label11"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 23
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label12"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 24
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label13"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 25
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label14"
        .Width = 36
        .Height = 30
        .Top = 96
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 26
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label15"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 27
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label16"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 28
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label17"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 29
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label18"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 30
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label19"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 31
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label20"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 32
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label21"
        .Width = 36
        .Height = 30
        .Top = 126
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 33
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label22"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 34
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label23"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 35
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label24"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 36
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label25"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 37
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label26"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 38
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label27"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 39
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label28"
        .Width = 36
        .Height = 30
        .Top = 156
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 40
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label29"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 41
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label30"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 42
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label31"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 43
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label32"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 44
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label33"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 45
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label34"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 46
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label35"
        .Width = 36
        .Height = 30
        .Top = 186
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 47
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label36"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 24
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 255
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 48
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label37"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 60
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 49
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label38"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 96
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 50
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label39"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 132
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 51
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label40"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 168
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 52
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label41"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 204
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = -2147483630
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 53
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
    With myFormDesign.Controls.Add("Forms.Label.1")
        .Name = "Label42"
        .Width = 36
        .Height = 30
        .Top = 216
        .Left = 240
        .BackColor = -2147483633
        .BackStyle = 1
        .ForeColor = 16711680
        .Font.Size = 15.75
        .Font.Name = "Times New Roman"
        .TabIndex = 54
        .BorderColor = -2147483642
        .BorderStyle = 0
        .SpecialEffect = 3
        .TextAlign = 2
        .Caption = ""
    End With
End Sub

次に、ソースコードを実行します。
再生マークをクリックして実行するか、F5を押しても実行して下さい。

実行時エラーが発生する場合

環境によっては以下のようなエラーが発生する場合があります。

これは、デフォルトでマクロの設定のVBAプロジェクトへのアクセスが無効化されているためです。

こちらを有効化するには、Excelのリボンの「ファイル」タブを開きます。

続いて「オプション」に進んで下さい。

Excelのオプションが開いたら、「トラストセンター」（旧バージョンではセキュリティセンターといった表記になっている可能性があります。）の「トラストセンターの設定」をクリックします。

トラストセンターの「マクロの設定」から「VBA プロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れて下さい。

再度、再生マークをクリックしてソースコードの実行が出来るか確認して下さい。実行出来たら次の手順に進みます。

⇧ 目次へ戻る

ソースコード（続き）

先程の手順のコードを実行すると以下のようにフォームが表示されます。

フォームの「CalendarForm」を右クリックして「コードを表示」を選択します。

先程と同様に右側のパネルに以下のソースコードをコピペします。

Private CalendarParts(1 To 42)  As CalendarControl
Private CurrentDate             As Date
 
Private Const GRAY      As Long = -2147483633
Private Const LIGHTBLUE As Long = 16763070
 
Private Sub UserForm_Initialize()
 
    Dim i As Long
    For i = LBound(CalendarParts) To UBound(CalendarParts)
        Set CalendarParts(i) = New CalendarControl
        Call CalendarParts(i).Bind(Me.Controls("Label" & i))
    Next i
 
    CurrentDate = Date
    Call CreateDays
 
End Sub
 
Private Sub UserForm_Terminate()
 
    Erase CalendarParts
 
End Sub
 
Private Sub TXT日付_Change()
 
    If IsDate(Me.TXT日付.Value) Then
        CurrentDate = Me.TXT日付.Value
        Call CreateDays
    End If
 
End Sub
 
Private Sub TXT日付_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 
    If Not IsDate(Me.TXT日付.Value) Then
        Me.TXT日付.Value = CurrentDate
    End If
 
End Sub
 
Private Sub CMD先月_Click()
 
    CurrentDate = DateAdd("m", -1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
 
End Sub
 
Private Sub CMD翌月_Click()
 
    CurrentDate = DateAdd("m", 1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
 
End Sub
 
Private Sub CMD今日_Click()
 
    ActiveCell.Value = Date
 
    Call CalendarForm.Hide
 
End Sub
 
Private Sub CreateDays()
 
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
 
    Dim TargetDate As Date
        TargetDate = Format(CurrentDate, "yyyy/mm") & "/1"
 
    Dim WeekDayCode As Long
        WeekDayCode = 1
 
    Dim Ctrl As Control
    Dim i As Long
    For i = 1 To 42
        Set Ctrl = Me.Controls("Label" & i)
        Ctrl.Caption = ""
        Ctrl.BackColor = GRAY
        If Month(TargetDate) = Month(CurrentDate) _
        And WeekDayCode >= Weekday(TargetDate) Then
            Ctrl.Caption = Day(TargetDate)
            If TargetDate = Date Then
                Ctrl.BackColor = LIGHTBLUE
            End If
            TargetDate = DateAdd("d", 1, TargetDate)
        End If
        WeekDayCode = WeekDayCode + 1
    Next i
 
End Sub
 
Public Sub CopyToActiveCell(ByVal xDate As String)
 
    If xDate = "" Then Exit Sub
 
    ActiveCell.Value = Format(CurrentDate, "yyyy/mm/") & xDate
 
    Call CalendarForm.Hide
 
End Sub

次に、「標準モジュール」を右クリックし、「挿入」＞「クラスモジュール」を選択します。

こちらも同様の以下のソースコードをコピペします。

Private WithEvents DateLabel As MSForms.Label
 
Public Sub Bind(ByVal Ctrl As MSForms.Control)
    Select Case TypeName(Ctrl)
        Case "Label"
            Set DateLabel = Ctrl
        Case Else
    End Select
End Sub
 
Private Sub DateLabel_Click()
    Call CalendarForm.CopyToActiveCell(DateLabel.Caption)
End Sub

ソースコードをコピペしたら、プロパティウィンドウのオブジェクト名（Class1と書かれているところ）をダブルクリックして変更します。

クラス名は「CalendarControl」とします。

次に、「標準モジュール」を右クリックし「挿入」＞「標準モジュール」を選択します。

以下ソースコードをコピペします。

Public Sub カレンダーから入力_Click()
    Call CalendarForm.Show
End Sub

続いて、リボンに「開発」タブを表示させます。既に「開発」タブが表示されている人は読み飛ばして下さい。
Ctrl + Gを押してイミディエイトウィンドウを開いて下さい。

イミディエイトウィンドウに「Application.ShowDevTools = True」と入力して「Enter」を押します。

すると、下図のようにExcelのリボンに「開発」タブが表示されるはずです。
そうしたら、「開発」タブの「挿入」からフォームコントロールのボタンをクリックして下さい。

シートの任意の場所をクリックすると、下図の「マクロの登録」ウィンドウが起動します。

「カレンダーから入力_Click」をクリックしてから、「OK」ボタンを押して閉じて下さい。

ボタンのキャプション（標題）や幅や高さなどは適宜調整します。

以上で完成です！

⇧ 目次へ戻る

完成

⇧ 目次へ戻る

連続して日付を入力したい場合　2021/07/20追記

日付を選択した後に引き続きカレンダーを表示して他のセルにも日付入力したい場合、
以下のように修正すれば対応出来ます。

①All + F11でVBEを起動し、上記の手順の中で最後に作成した標準モジュールにある、「カレンダーから入力_Click」の処理を以下のように修正
Call CalendarForm.Show(vbModeless)

②CalendarFormを右クリックしコードを表示

③下記に置き換え（変更点は、Call CalendarForm.Hideを削除しただけです）

Private CalendarParts(1 To 42)  As CalendarControl
Private CurrentDate             As Date
 
Private Const GRAY      As Long = -2147483633
Private Const LIGHTBLUE As Long = 16763070
 
Private Sub UserForm_Initialize()
 
    Dim i As Long
    For i = LBound(CalendarParts) To UBound(CalendarParts)
        Set CalendarParts(i) = New CalendarControl
        Call CalendarParts(i).Bind(Me.Controls("Label" & i))
    Next i
 
    CurrentDate = Date
    Call CreateDays
 
End Sub
 
Private Sub UserForm_Terminate()
 
    Erase CalendarParts
 
End Sub
 
Private Sub TXT日付_Change()
 
    If IsDate(Me.TXT日付.Value) Then
        CurrentDate = Me.TXT日付.Value
        Call CreateDays
    End If
 
End Sub
 
Private Sub TXT日付_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 
    If Not IsDate(Me.TXT日付.Value) Then
        Me.TXT日付.Value = CurrentDate
    End If
 
End Sub
 
Private Sub CMD先月_Click()
 
    CurrentDate = DateAdd("m", -1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
 
End Sub
 
Private Sub CMD翌月_Click()
 
    CurrentDate = DateAdd("m", 1, CurrentDate)
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
    Call CreateDays
 
End Sub
 
Private Sub CMD今日_Click()
 
    ActiveCell.Value = Date
 
End Sub
 
Private Sub CreateDays()
 
    Me.TXT日付.Value = Format(CurrentDate, "yyyy/mm")
 
    Dim TargetDate As Date
        TargetDate = Format(CurrentDate, "yyyy/mm") & "/1"
 
    Dim WeekDayCode As Long
        WeekDayCode = 1
 
    Dim Ctrl As Control
    Dim i As Long
    For i = 1 To 42
        Set Ctrl = Me.Controls("Label" & i)
        Ctrl.Caption = ""
        Ctrl.BackColor = GRAY
        If Month(TargetDate) = Month(CurrentDate) _
        And WeekDayCode >= Weekday(TargetDate) Then
            Ctrl.Caption = Day(TargetDate)
            If TargetDate = Date Then
                Ctrl.BackColor = LIGHTBLUE
            End If
            TargetDate = DateAdd("d", 1, TargetDate)
        End If
        WeekDayCode = WeekDayCode + 1
    Next i
 
End Sub
 
Public Sub CopyToActiveCell(ByVal xDate As String)
 
    If xDate = "" Then Exit Sub
 
    ActiveCell.Value = Format(CurrentDate, "yyyy/mm/") & xDate
 
End Sub

⇧ 目次へ戻る

補足

シートにボタンを挿入するときに、ActiveXコントロールよりもフォームコントロールを使うことをおすすめします。

ActiveXコントロールでは色付け出来たりデザインモードでクリックするだけでイベントプロシージャのひな形が作成されたりとよさそうな感じもするのですが、上記をExcel 2016で試したところボタンが効かない不具合が発生しました。

他にも表示が崩れるなどの問題が起きたというケースがあったので、ActiveXコントロールは避けたほうがよいでしょう。どうしても色付けしたい！ということであれば、図形（シェイプ）にマクロを登録すればいいと思います。（クリックイベントぐらいしかないですが）

⇧ 目次へ戻る

よかったらシェアしてね！

URLをコピーしました！

コピペするだけ！ExcelVBAでカレンダーコントロールを自作する

カレンダーコントロールはExcel 2010から無くなった

完成イメージ

ソースコード

実行時エラーが発生する場合

ソースコード（続き）

完成

連続して日付を入力したい場合 2021/07/20追記

補足

関連記事

連続して日付を入力したい場合　2021/07/20追記