2020/01/27 改訂 2021/10/04

セルへの日付入力をカレンダー日付クリックで選定する




1.背景

Excelのセルに日付を入力する場面は多いと思います。一番簡単なのは、手入力で「2020/12/12」などと日付形式で入力することです。しかしシステムを作る人の期待とは異なり「不明」「未定」のように文字列を入れたり、わざわざ先頭に「'(アポストロフィ)」を付けて「日付っぽく見える文字列」にしたりするユーザもいます。
それを回避するため、サイト等でも良く見かけるのが図1-1の方法です。日付を年・月・日ごとに選択式にしています。

図1-1

しかし、これが意外と使いづらいのです。それは、年月日を別々に選択したり、リストを上下させたりする手間だけでなく、曜日が「見えない」のも原因の1つだと思います。
そこで今回は、カレンダー形式の日付選択方式を紹介します。日付入力が必要なセルに来たらカレンダーが表示され、日付をクリックするとその日付が入力される、というものです。

カレンダー選択方式は昔から存在し、他のサイトでも色々紹介されています。ぜひ色々なサイトも比較・参照してみて下さい。
なお、今回はフォーム上にコマンドボタンを並べてカレンダーにしましたが、ワークシート上にカレンダーを作成し、それを画像としてフォーム上に表示して操作する「図形カレンダーをクリックし日付入力」も参考にして下さい。
また、フォーム上のLabelにカレンダーを表示する「ラベルカレンダーをクリックし日付入力」も参考にして下さい。

本項は2021年10月にフォーム見直し・補筆により改訂しました。オリジナルの項を参照したい方は「こちら」からお願いします。ただし、内容的には不変です。
また、同様の「フォーム上にボタンのカレンダーを作る」システムとして「ボタンを自動生成するフォームカレンダー」も参考にしていただけると幸いです。

2.システム概要

例えば図2-1のように「F6セルに開始予定日」「F8セルに完了予定日」を入力する書類があるとします。ユーザーがその「日付を入力すべきセルを選択」した時に「カレンダーが表示」されます。
最初に表示される年月は「今月」ですが、日付入力セルに「既に日付が入っていた」場合の表示されるカレンダーは「既に入っていた日付の年月」が表示されます。なお、今日の日付の数字は赤字になります。
また、カレンダーダイアログのタイトル(最上部)として、入力目的の指示(図2-1では「完了予定日」)が出来ます。

図2-1

カレンダーは上部のスクロールバーで移動することができ、スクロールバー端の「横三角印」をクリックすれば1ヶ月移動し、レール部をクリックすれば1年移動します。
入力したい日付の年月に移動し「日付をクリック」することで、カレンダーが消えてセルに日付が入力されます。

なお、日付をクリックせずに「カレンダーダイアログの右上×印」をクリックして終了した場合は、入力セル値を空白にします(入力Cancelでは無く、空白を入力)。

この「カレンダーを表示・操作」するためには、下記フォーム(UserForm1)及びクラス(Class1)を「日付を入力するブック上に作成」する必要があります。
例えば「サンプルファイル」のUserForm1とClass1をエキスポートし、操作するExcelブックにインポートする方法でもOKです。その際、もしフォームやクラスのオブジェクト名が(例:既にUserForm1が存在していた)変わってしまった場合には、フォーム名・クラス名の変更をお願いします。

3.プログラムの流れ

日付入力セルをセル選択したことをSelectionChangeイベントで察知すると、そのセル値を調べ日付であればフォーム最下段に書込んだ上でフォームを起動します(空欄等であれば、ゼロを書き込みます)。
フォームでは、シートから渡された日付(ゼロであれば今日の日付)を元にカレンダー計算をし、フォーム上に並べたコマンドボタンの表面文字列を書き込んだり、非表示にしたりして、その年月のカレンダーを作成します。
ユーザーがスクロールバー移動で年月を変更させた際にも、毎回カレンダー計算をし、更新します。


図3-1

ユーザーが選択した年月カレンダーの指定日相当のボタンをクリックすると、表示年月とクリックしたボタンの表面文字列(=指定日)を組合せて、フォーム最下段に日付を書込み、フォームを閉じます(Hideメソッド)。
フォームが閉じられると制御はワークシートモジュール側に戻りますので、フォームの最下段に書き込まれた日付をセルに書き込みます。

4.フォームでカレンダーを作る

4-1.ユーザーフォーム上にコントロールを配置

まず、カレンダーをフォーム上で組み立てます。上の方から以下のコントロールを図4-1のように並べていきます。
1)スクロールバー(1個)・・・・・・・・年月変更用
2)ラベル(1個)・・・・・・・・・・・・年月表示用
3)ラベル(7個)・・・・・・・・・・・・曜日表示用
4)コマンドボタン(7x6=42個)・・・日付用
5)ラベル(1個)・・・・・・・・・・・・ワークシートとの値受け渡し用


図4-1

それぞれのコントロールには「Caption」という表示名のプロパティがあります。図4-1ではコマンドボタンに01、02、03・・・と表示に変更していますが、コントロールを作ったそのまま(例えばCommandButton1のCaptionは「CommandButton1」のまま)の状態で何も問題ありません。月によって曜日毎の日付は変わりますので、マクロでCaptionを書き換えるからです。
但し、CommandButton1は図4-1の「01の場所」に、CommandButton2は図4-1の「02の場所」に・・・と、整然と並べておいて下さい。
尚、固定されている曜日のラベルは、月・火・水・・・とCaptionを変更してあります。

4-2.フォームモジュール

では、カレンダー用にコントロールを配置したフォーム(ここでは、UserForm1の上にカレンダーを作成したとしています)に、以下のコードを記述します。

4-2-1.フォーム起動時の初期設定

  1. ’------ ⇩(1)変数宣言部 ---------------
  2. Dim Event_Flag As Boolean     '←スクロールバー値をゼロに戻した時に再計算するのを防止
  3. Dim Current_day As Date      '←Label9に記載された受け渡し値の日付
図4-2

2~3行目はフォーム内で使用する変数の宣言部です。
Event_Flag変数は、スクロールバーで年月を変更した後、スクロールバーを元に戻す(=次のスクロールバーの操作に備える)ために値(Value値)をゼロに設定しなおします(図4-8の93行目)が、値を変えた瞬間にまたScrollBar1_Changeのイベントが発生してしまい、カレンダーの計算・表示をもう一度繰り返してしまいます。そのまま放っておいても外観的には問題ないのですが、無駄な再計算・再表示を防止する(85行目)ためにフラグを立てる様にしています。
Current_day変数は、ワークシート~フォーム間で受け渡される日付です。ワークシートのセルに既に日付が入力されている場合には、その日付をフォームのLabel9に貼り付けて(図6-2の3~7行目、20~24行目)からフォームを表示しています。フォーム表示時にその日付をCurrent_day変数に取り込み(図4-5の41行目)、カレンダーの対応する日付の文字色を変更(図4-6の76行目)させています。

  1. ’------ ⇩(2)Formの初期化 ---------------
  2. Private Sub UserForm_Initialize()    '←Formの初期化プロシージャ(初めて実行した時に動作する)
  3.  Static mybtnArray() As Class1    '←Class1の設計図に従って、ボタン配列を宣言
  4.  For Each btn In Controls          '←UserForm1の全てのコントロールを調べる
  5.   If TypeOf btn Is MSForms.CommandButton Then   '←コマンドボタン(日付部分)だったら
  6.    n = n + 1
  7.    ReDim Preserve mybtnArray(1 To n)    '←ボタン配列のサイズをボタン数の大きさに変更
  8.    Set mybtnArray(n) = New Class1     '←ボタン配列(1個分)をオブジェクトにする
  9.    mybtnArray(n).setbtn btn        '←ボタン配列(1個分)をイベント取得できるオブジェクトに登録
  10.   End If
  11.  Next
  12.  Call Calender_ini           '←各コントロールのプロパティを設定
  13. End Sub
図4-3

5~18行目は、UserForm1を初めて起動する際のイベント(Initialize)プロシージャです。
前半(6~15行目)の部分は「多くのコマンドボタンのイベントを1つのイベントに集約する」部分です。
今回のカレンダーでは多くのコマンドボタンの内「どれかのボタンを押すとその日付に反応する」という様なものを作りたいのですが、普通に作ると7x6=42個のボタンのイベント処理を記載しないといけなくなります。それは、1つ1つのボタンのイベントは別々のイベントとして発生するからです。数個であれば力ずくでイベント処理を一生懸命書いても良いのですが、数十個となると後のマクロ修正も大変になりますので、いわゆる配列イベントの仕組みを作る方が楽になります。
(似ている言語VBでは配列イベントが簡単な手順で作成可能なのですが、VBAではその仕組みが無いのでクラスを使って作る必要があります。)
クラスとのやり取り部分があるので理解し難いと思いますが、クラスの内容を見比べながら見て下さい。
6行目の「Static mybtnArray() As Class1」は、『「Class1の設計図」に従って「mybtnArrayという配列」を「このマクロ全体で使える様に」宣言します』ということを示しています。Class1は自分で作るオブジェクト設計図になりますが、ここでは「イベントを発生するコマンドボタン」となります。

8~15行目はFor Each ~ Next で囲まれています。このフォーム(ここではUserForm1)に配置されたコントロールについて1つ1つ調べ(8行目)、それがコマンドボタンだったら(9行目)、そのコマンドボタンを配列の1つとしてオブジェクトを作り(12行目)、イベントを発生するコマンドボタンに関連付けます(13行目)。
なお、10~11行目は、「コマンドボタンが何個でてくるか分からない」事を前提に配列サイズを都度大きくしていますが、今回の場合42個と決まっていますので固定しておく事も可能です(ここより下のコードでは「ボタンは42個」としてFor~Nextでうっかり42回固定でまわしたりしちゃっています。ボタンの個数が都度変わるのであれば、Ubound(mybtnArray,1)を使うべきですね)。もしカレンダー以外に「OKとかCancelなどのコマンドボタン」を配列したい場合には「ボタンのオブジェクト名での選別」等が必要ですので注意・工夫して下さい。

この6~15行目の手続きにより、どの日付のコマンドボタン(42個)を操作しても、1つのイベント処理で対応できることになります。

17行目は図4-4の各コントロールのプロパティ設定プロシージャを呼び出して実行しています。コントロールの定数や固定色などの設定です。

  1. ’------- ⇩(3)各コントロールのプロパティ設定 ----------------------
  2. Sub Calender_ini()     '←各コントロールのプロパティを設定するプロシージャ
  3.  Me.Height = 200     '←受け渡し値を表示する時は225に変更。200は受け渡し値を隠す寸法
  4.  Me.Width = 150      '←フォームの表示する幅
  5.  Me.Controls("ScrollBar1").Value = 0   '←スクロールバーの値
  6.  Me.Controls("ScrollBar1").Min = -12   '←スクロールバーの最小値(12か月前、という意味)
  7.  Me.Controls("ScrollBar1").Max = 12   '←スクロールバーの最大値(12か月後、という意味)
  8.  Me.Controls("ScrollBar1").SmallChange = 1   '←スクロールバーの移動値(1か月、という意味)
  9.  Me.Controls("ScrollBar1").LargeChange = 12   '←スクロールバーの移動値(12か月、という意味)
  10.  For i = 1 To 42 Step 7   '←日曜日の部分を選択
  11.   Me.Controls("CommandButton" & CStr(i)).BackColor = RGB(255, 192, 203)   '←背景をピンク色に
  12.  Next i
  13.  For i = 7 To 42 Step 7   '←土曜日の部分を選択
  14.   Me.Controls("CommandButton" & CStr(i)).BackColor = RGB(173, 216, 230)   '←背景を水色に
  15.  Next i
  16. End Sub
図4-4

20~37行目は、フォーム上に配置したコントロールのプロパティを設定しています。
フォームのコントロールの配置と一緒にコントロールのプロパティを手動で変更してしまえば、このプロシージャは不要です。しかしプロパティを手動でいじってしまうと、後になって「どのプロパティをどの値に変更したのか分からなく」なり、修正時に非常に時間がかかる経験をしているため、あえて「プロパティを変更したコントロールの記録簿」という意味で別プロシージャにしています。尚、曜日の色付けのように多くのコントロールの同じプロパティを同時に変更するにはFor~Nextなどを使って変更した方が楽なのは言うまでもありません。

21~22行目は、フォームの可視範囲のサイズです。コントロールの大きさ・配置・数量によってサイズを決めて下さい。尚、フォームの下部にLabel9(受け渡す日付データ)を配置していますが、本来は「直接見えない方が良い」と思いますので、運用時には21行目のフォームの高さを200くらいに設定し、Lavel9を隠すようなサイズにして下さい。
24~28行目は、年月を変更するスクロールバーの設定値です。スクロールバーを左右に大きく動かすと「年」が変わり、小さく動かすと「月」が変わるようにしています。
30~32行目は、日曜日のボタンの背景に色をつけています。コマンドボタンは横方向に順にならんでいますので、縦方向を選択するためにFor文のStepを7にしています。また各コマンドボタンのオブジェクト名は「CommandButton〇〇」という名前なので、〇〇の部分を変数にして文字結合しています。
34~36行目は、土曜日のボタンの背景色付けで、コマンド選択のFor~Nextのスタート位置と背景色だけが日曜日色変更コード(30~32行目)と異なっています。

4-2-2.ワークシート側からの情報を受け取りフォームに反映

  1. ’------- ⇩(4)フォームを表示時に実行 --------------------
  2. Private Sub UserForm_Activate()         '←UserFormを表示した時に実行されるプロシージャ
  3.  Event_Flag = True              '←カレンダー表示を可にする
  4.  Current_day = Me.Controls("label9").Caption   '←シート側のプロシージャから渡された値をForm内の変数に置換
  5.  If Current_day = 0 Then            '←渡された値がゼロの時(セルに値が無い時)
  6.   Call Calendar(Year(Now()), Month(Now()))   '←今日の日を含むカレンダーを表示
  7.  Else                    '←渡された値が日付の時(セルに日付がある時)
  8.   yyyy = Year(Current_day)          '←渡された日付の年を取得
  9.   mm = Month(Current_day)          '←渡された日付の月を取得
  10.   Call Calendar(yyyy, mm)           '←渡された日付の日を含むカレンダーを表示
  11.  End If
  12. End Sub
図4-5

39~50行目は、フォームが表示される直前に実行されるイベントプロシージャです。
39行目で変数Event_FlagをTrueにしています。
カレンダーの表示年月を変更する時にはスクロールバーを操作しますが、新しいカレンダーが表示された後、次の年月変更操作に備えてスクロールバーを元の値(ゼロ)に戻す処理をします(93行目)。その「ゼロに戻した」処理を「人間がスクロールバーを動かした」と勘違いして、無駄にカレンダーを再計算してしまうのです。これを防止するのがEvent_Flag変数(85行目)の役目です。
尚この処理をしなくても、見かけ上はカレンダーの年月が変わるわけでもなく問題ありませんが、無駄な計算は避けるべきと考えて設定しました。

41行目でワークシート側からLabel9に渡された「受け渡し日付」をマクロ内で使い易いように変数Current_dayに代入しています。尚、ワークシート側からの受け渡し日付は、日付入力セルに「既に日付が入っている時は、その日付」「セルが空欄、又は日付以外の値なら、ゼロ」を受け渡すようにしています。
43~49行目は、受け渡し日変数Current_dayの値に従って処理を分けています。
まずゼロ(=セルが空欄、又は日付以外)の場合は、44行目で今日の日を含む年月を引数として、カレンダー計算・表示プロシージャ(Calendar(年,月)を呼び出します。
その他(=セルに既に日付が入っている)の場合は、渡された日付Current_dayから年月を取り出し(46~47行目)て、それを引数にカレンダー計算・表示プロシージャを呼び出します。

4-2-3.カレンダーの計算・表示

  1. ’-------- ⇩(5)カレンダーの計算・表示 ------------------------
  2. Sub Calendar(yyyy, mm)           '←カレンダーを計算・表示させるプロシージャ
  3.  Dim Cal_First_Day As Date         '←表示カレンダーの初日
  4.  Dim First_Week, Last_Day As Integer     '←初日の曜日、最終日
  5.  Dim i, j As Integer             '←カウンター変数
  6.  Cal_First_Day = DateSerial(yyyy, mm, 1)    '←表示カレンダーの初日を計算
  7.  yyyy = Year(Cal_First_Day)
  8.  mm = Month(Cal_First_Day)
  9.  First_Week = Weekday(DateSerial(yyyy, mm, 1), 1)   '←初日の曜日を計算
  10.  Last_Day = Day(DateSerial(yyyy, mm + 1, 1) - 1)    '←最終日を計算
  11.  For i = 1 To 42          '←全ボタンの初期化
  12.   Me.Controls("CommandButton" & CStr(i)).Visible = False    '←全ボタンを非表示に
  13.   Me.Controls("CommandButton" & CStr(i)).ForeColor = RGB(0, 0, 0)   '←全ボタンの文字色を黒に
  14.  Next i
  15.  j = First_Week           '←初日の曜日からボタンを表示する
  16.  For i = 1 To Last_Day              '←その月の1~最終日までを表示する
  17.   Me.Controls("CommandButton" & CStr(j)).Caption = i     '←ボタンに日付を記入
  18.   Me.Controls("CommandButton" & CStr(j)).Visible = True    '←ボタンを表示する
  19.   If (yyyy = Year(Now())) And (mm = Month(Now())) And (i = Day(Now())) Then   '←本日の場合
  20.    Me.Controls("CommandButton" & CStr(j)).ForeColor = RGB(255, 0, 0)      '←文字を赤色に
  21.   ElseIf (yyyy = Year(Current_day)) And (mm = Month(Current_day)) And (i = Day(Current_day)) Then   '←受渡日
  22.    Me.Controls("CommandButton" & CStr(j)).ForeColor = RGB(0, 128, 0)     '←文字を緑色に
  23.   End If
  24.   j = j + 1
  25.  Next i
  26.  Me.Controls("Label8").Caption = Year(Cal_First_Day) & "/" & Month(Cal_First_Day)    '←「年/月」をLabel8に記入
  27. End Sub
図4-6

52~82行目は、年月を引数として受け取って、実際にカレンダーを計算・表示するプロシージャです。 この計算プロセスについては、別項目の「ワークシート上で永久カレンダーを作る」も参考にして下さい。

57行目では、引数として受け取った「年=yyyy」「月=mm」を使って、表示させるカレンダーの初日を「日付型」として変数に代入します。「年月を引数として渡してくれたんだから、再度日付を計算する事は無いのでは?」と思われるかもしれませんが、図4-8のスクロールバーを変更した時には、月を指定する第2引数には「表示されている月 + 〇か月」という値が渡されてきます。例えば「2020年13月 を表示しなさい」という指示をされている様なものなので、一度ちゃんとした年月日に計算しておく必要があるのです。
そのちゃんとした年月日を使って年月を計算(58~59行目)し、初日の曜日を60行目で計算、最終日(=その月の日数)を61行目で計算します。

63~66行目では、全てのボタンを非表示にし、ボタンの文字色を黒にしています。今回表示するカレンダーでは、ボタンには「日」以外の情報を持たせておらず、年月についてはLabel8のデータを使っています。ですので、その月以外の日付は表示しないこととしました。また、73~77行目で本日や受け渡し日には文字に色を付けていますので、文字色をリセットしています。

69~71行目で日付コマンドボタンを「非表示→表示」に切り替え、「日付を記入」していきます。
どのようにして、その年月に該当する日付を「非表示→表示」+「日付記入」にするか を説明します。図4-7も見ながら動きを追って下さい。
69~80行目の For~Next でコマンドボタンに処理をしていきますが、処理するコマンドボタンの番号には変数 j を当てており、その j は68行目で初日の曜日が初期値として与えられ、For~Next を回すたびに79行目で1つずつ増やしています。つまり、初日の曜日(日曜日を1として土曜日が7まで)のコマンドボタン以降が表示される事になります。
そして For~Next での繰り返し処理数としては、69行目の Last_Day(その月の日数)までになりますので、日数を超えたコマンドボタンは非表示のままとなります。

図4-7

後は趣味的な部分で、73~77行目で本日を示す日付には赤色、受け渡し日には緑色を付けています。
そして81行目で、表示したカレンダーの「年/月」をLabel8 に書き込みます。今回はスラッシュ「/」で年・月を切っていますが、年と月を別のLabelに書き込む方法も良いかと思います。(年と月を別々のLabelにする場合は、スクロールバーで年月を変更する図4-8の87~88行目の修正、及びクラス内のボタンが押されたイベントを受け取るプロシージャ図5-3の9行目の修正が必要です)

4-2-4.カレンダーの年月移動

  1. ’--------- ⇩(6)スクロールバーの変更(=年月の変更) ------------------------
  2. Private Sub ScrollBar1_Change()       '←スクロールバーの値を変更した時に実行されるプロシージャ
  3.  If Event_Flag = False Then Exit Sub     '←スクロールバーの値をゼロにした時の再表示を防止
  4.  yyyy = Split(Me.Controls("Label8").Caption, "/")(0)  '←表示されている年を取得
  5.  mm = Split(Me.Controls("Label8").Caption, "/")(1)   '←表示されている月を取得
  6.  Call Calendar(Val(yyyy), Val(mm) + Me.ScrollBar1.Value)   '←月数を変更してカレンダーを表示
  7.  Event_Flag = False          '←スクロールバーの値を変更する前に再表示防止のフラグを立てる
  8.   Me.ScrollBar1.Value = 0       '←連続的に年月を変更できるようにスクロールバーの値を戻す
  9.  Event_Flag = True           '←スクロールバーの値を変更した後、再表示防止のフラグを戻す
  10. End Sub
図4-8

84~95行目は、スクロールバーの値を変更した(=年月を変更した)場合のイベントプロシージャです。
スクロールバーは、変更前の値(Value)はゼロ、移動値小(SmallChange)が1、移動値大(LargeChange)が12です。ですので、スクロールバーを変更した時には-12~+12までの変更後の値(ScrolBar1.Value)として取得できます。(尚、初期値はゼロなので、バーを動かしてもゼロの位置でマウスを離すと「変更しなかった」ことになりますのでゼロは値としては出てきません)

図4-9

85行目のEvent_Flag変数がFalseの場合は、何もせずにプロシージャを抜けます。これは40行目のところでも説明しましたが、初期値はTrueですので通常このプロシージャは実行されます。
87~88行目は、現在の年月を示しているLabel8の文字を、仕切り文字「/」で文字分割し、年月を取得しています。
90行目では、現在の年月に対し、月の値にスクロールバーの変更後の値(-12~+12)を加えたものを引数にしてカレンダー計算・作成を呼び出しています。単純に月の値に数を足し引きしている事になりますので、例えば「2020年13月(2020年1月 + 12か月)を表示しなさい」のような指示を与えていることになります。

新たな年月のカレンダーが表示されましたが、スクロールバーの値は変更されたままです。それを中立の位置(ゼロ)に戻すために93行目でスクロールバーのValue値にゼロを代入しています。

図4-10

そのValue値をゼロに戻した際に、「スクロールバーの値を変更した」と判断されてイベントが発生します。発生すると84行目から順にマクロを実行し始め、カレンダーを再表示する動きになります。それを防止するためにValue値をゼロに戻す前に92行目でEvent_FlagをFalseに設定し、85行目まで行ったところでプロシージャを抜け出させ、再表示を防いでいます。カレンダーの再表示を防止したら、94行目でEvent_Flag値を初期値(True)に戻しておきます。
なお、このEvent_Flag処理をしなくても、見かけのカレンダーには影響ありません。というのは、幸いなことに再表示をしようとする時点ではLabel8は新たな年月に書き換えられており、その年月に対して「±0か月のカレンダーを表示せよ」との指示になるためです。

またValue値をゼロに戻さない方法として「スクロールバーのMAX値を大きく取り、Value値はそのままにしておく」事も考えられるでしょう。その場合には、
①表示されているカレンダーの年月をLabel8から取得するのでは無く、ある基準年月を内部的にもっておく
②スクロールバーの変更前値を確保しておき、変更後との差分でカレンダーの表示年月を指示する
などの処理が必要になります。

4-2-5.フォームの強制終了

  1. ’----------- ⇩(7)右上の×で閉じた時の処置 -----------------------
  2. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)    '←右上の×で閉じた時
  3.  Me.Controls("label9").Caption = "0"      '←Label9にゼロを記入
  4.  Cancel = True                '←×で閉じる(UnLoadする)のをやめる
  5.  Me.Hide                   '←フォームを隠す
  6. End Sub
図4-11

97~101行目は、表示されているフォームの右上の✖印でフォームを閉じた時の処理です。
この QueryClose イベントはフォームが閉じられた時(UnLoad)に発生するイベントですが、今回のフォームではどのコントロールにも UnLoad ステートメントを組み込んでいませんので、右上の✖印で閉じようとしたことを表しています。
98行目で、ワークシート側との受け渡し値(Label9)にゼロを書き込んで、✖が押された(=日付を選択しなかった)ことを伝えようとしています。
99行目では、QueryClose の引数である Cancel に True を設定することで「フォームを閉じることをキャンセル」させます。フォームを閉じてしまうと、せっかく Label9 に受け渡し値を記入しても無駄になるためです。
そして100行目で、フォームを非表示(Hide = 隠す)にします。

なお、この QueryClose イベントプロシージャが無い場合、✖印で閉じるとフォームが UnLoad されてしまいます。しかし、Label9 の初期値をゼロに設定しておくと、見かけ上は正常に動きます。これはワークシート側のマクロで Label9 を見に行こうとすると「フォームは閉じられていて Label9 の値が分からないため、わざわざフォームを立ち上げ直して(Loadして)、Label9 の値を見る」からです。見に行ってみると「(初期値が)ゼロだから、カレンダーの日付をクリックしなかったんだな」と判断するのです。

5.クラスモジュール(Class1)

フォームの Initialize イベントプロシージャ内で記載した、42個のコマンドボタンのイベントを集約して1つのイベントとして受け取る機能を作るため、クラスモジュールに以下のコードを記述します。クラスは「自作オブジェクトの設計図」という位置づけで、通常は分かり易いオブジェクト名(=クラス名)をつけるのですが、今回は作ったままの「Class1」としてあります。

5-1.フォーム上のボタンのイベント共通化

  1. ’---------- ⇩(8)ユーザーイベントの登録 --------------------
  2. Private WithEvents mybtn As MSForms.CommandButton    '←変数mybtnをコマンドボタン型としてイベントを受け取る
図5-1

2行目はWithEventsキーワードをつけてmybtnというオブジェクト変数を宣言しています。型はコマンドボタンです。
こうすることで、オブジェクト変数mybtnが参照するオブジェクトのイベントを受け取ることが可能になります。

  1. ’---------- ⇩(9)ユーザーイベントとオブジェクトの接続---------------------
  2. Public Sub setbtn(ByVal myNewbtn As MSForms.CommandButton)
  3.  Set mybtn = myNewbtn
  4. End Sub
図5-2

4~6行目は、フォーム内マクロ(図4-3)の13行目で、カレンダー日付のコマンドボタン1つ1つをイベント取得可能なオブジェクトに登録するために関連付けます。
    イベントを受け取れるmybtnオブジェクト ~ 配列mybtnarrayの1つ1つ ~  各コマンドボタン 
と関連付けを行い、mybtnでイベントを受け取るのです。

5-2.カレンダーボタンの操作を取得

  1. ’---------- ⇩(10)ユーザーイベントの受け取り ---------------------
  2. Private Sub mybtn_Click()
  3.  UserForm1.Label9.Caption = UserForm1.Label8 & "/" & mybtn.Caption
  4.  UserForm1.Hide
  5. End Sub
図5-3

8~10行目は。どれかのコマンドボタンが押された時にはmybtnが押されたというイベントが発生します。そのmybtnは =1つ1つのコマンドボタン ということになりますので、そのコマンドボタンのプロパティを利用して処理をしていきます。
9行目は、フォーム内のカレンダーの年月が記載されているLabel8と、押されたボタンのCaption(日付の数字が記載されている)を取得して文字結合をします。そしてその値(=選択した日付値)をフォームのLabel9に記入し、ワークシート側で使用できるようにします。
10行目で、カレンダーを非表示(Hide)にします。ここでUnLoadしてしまうと、フォームが閉じられてしまい、せっかく書き込んだ受け渡し日付も消えてしまいます。もしUnLoadする必要があるならば、マクロ内のPublic変数としてデータを受け渡しておく必要があります。

6.ワークシートモジュール

例えば図6-1のように「F6セルを開始予定日」「F8セルを完了予定日」とする書類があった場合、図6-2のマクロをシートモジュールに記載することで「日付を入力すべきセルを選択」した時に「カレンダーが表示」され、そのカレンダーの「日付をクリック」することでセルに日付が入力されます。

図6-1

ワークシート上で、そのセルに入った(=異なるセル範囲を選択した)のを検出するイベントは SelectionChangeイベント です。どこのセルに入ったかは、その引数である Target(選択した範囲)を使用して位置を確認し、処理することになります。
書類画面を作成したワークシートの VBE に以下のコードを記述して下さい。ワークブックでイベントを受け取ることも可能(SheetSelectionChange を使用します)ですが、コードミスの可能性を少なくする為にも、シートのイベントはシートで受け取るのが一般的と思います。
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.  If (Target.Row = 6) And (Target.Column = 6) Then
  3.   If (Target.Value = "") Or (Not IsDate(Target.Value)) Then
  4.    UserForm1.Controls("Label9").Caption = "0"
  5.   Else
  6.    UserForm1.Controls("Label9").Caption = Target.Value
  7.   End If
  8.   UserForm1.Caption = "開始予定日"
  9.   UserForm1.Show
  10.   If UserForm1.Controls("Label9").Caption = "0" Then
  11.    Target.Value = ""
  12.   Else
  13.    Target.Value = UserForm1.Controls("Label9").Caption
  14.   End If
  15.  End If
  16.  If (Target.Row = 8) And (Target.Column = 6) Then
  17.   If (Target.Value = "") Or (Not IsDate(Target.Value)) Then
  18.    UserForm1.Controls("Label9").Caption = "0"
  19.   Else
  20.    UserForm1.Controls("Label9").Caption = Target.Value
  21.   End If
  22.   UserForm1.Caption = "完了予定日"
  23.   UserForm1.Show
  24.   If UserForm1.Controls("Label9").Caption = "0" Then
  25.    Target.Value = ""
  26.   Else
  27.    Target.Value = UserForm1.Controls("Label9").Caption
  28.   End If
  29.  End If
  30. End Sub
図6-2

図6-2のプロシージャは、1~17行目(開始予定日) と 19~35行目(完了予定日)の2つに大きく分けられます。これは、選択されたセル(Target)の位置を検出し、「開始予定日」のセルを選択したのか「完了予定日」のセルを選択したのかをIF文で分けているためです。
2行目と19行目のIF文には、今回はR1C1形式で .Row と .Column を使っています。もちろん Range(F6") などと A1 形式を使っても構いません。
尚、複数のセル範囲を選択した場合は、このコードでは実行時エラーが発生してしまいますので、対策が必要です。

ここから先は、開始予定日である1~17行目で説明します。19~35行目もほとんど同様と考えて下さい。
3行目は、F6セルに何が記入されているかを確認しています。①何も記入されていないか、②日付以外が記入されているか、③その他か(日付か) で処理を分けています。①か②の場合は、「何も記入されていないのと同等」と判断して、これから表示するカレンダー用フォームの Label9 に受け渡し値としてゼロを記入(4行目)します。
③の場合(5行目)は、その日付値を Label9 に記入(6行目)します。

9行目では、フォームのタイトルに「開始予定日」を表示させ、使用するユーザーに「今から開始予定日を選択して下さい」と促すようにしています。
10行目では、以上の準備をした上でカレンダーを表示させています。セルに日付が記載されている場合(③)は、その日付が緑色で表示される(図4-6の76行目)ことになります。

カレンダー上で日付をクリックするとフォーム上の Label9 に日付が入り(図5-3の9行目)、フォーム右上の✖印で閉じれば Label9 にはゼロが入り(図4-11の98行目)ますので、図6-2の12~16行目の IF 文で仕分けて処理をします。
ゼロが入っていた場合(12行目)は、カレンダー日付を選択しなかったのですから開始予定日のセルは空欄にします(13行目)。一方、日付が入っていた場合(14行目)は、日付値を開始予定日のセルに記入します。

尚、カレンダーの日付をクリック、または✖印で閉じた後のセル位置は、開始予定日・完了予定日のセル位置にとどまっています。この状態でセル編集(ダブルクリックやF2キーによる編集モード)をしても、選択している位置の変更が無いためSelectionChangeイベントが発生せず、編集モードが有効となり、日付の修正・日付以外の文字入力・空欄化 が可能になります。
これを防ぐには、「シートの保護」をしたり、図6-3の様にフォームが閉じられた直後に選択しているセル位置を移動する方法が考えられます。
  1.   Target.Offset(1, 0).Select
図6-3

この「セル位置を移動する」の後で、再度「開始予定日・完了予定日」のセルに入った時には SelectionChange イベントが発生しカレンダーが表示されてくれますので、日付以外のものに手修正されることは防げます。
ただし、移動先として何かが記入されているセルを指定(例えば、開始予定日セルのあと完了予定日セルに移動させようとすると)すると、図6-2の20~24行目の処理が動いてしまい、せっかく選んだ日付( Label9 の値)が上書きされてしまう事になりますので、注意が必要です。

7.最後に

本項は「図形カレンダーをクリックし日付入力」の作成を機に2年ぶりに読み直しをしたのですが、コードと説明文が大きく分けれていて「読みにくそうだな」というのが気にかかりました。もちろん元は自分で書いたものなのですが、2年間書き進めていく内に、自分なりのフォームが出来て来た気もします。
コード自体を直したい場所が何箇所かありましたが、間違っている訳では無いので今回改訂は主に書式修正と補足に留めました。

このカレンダーの項は、興味を持たれている方が多いらしくアクセスも多いです。カレンダーの作り方に多くの種類がある訳では無いので、「新方法の紹介!」みたいな訳にはいかないと思いますが、カレンダーにアクセスする方法は無限にあると思いますので、面白いのを思いついたら紹介したいと思います。

日付をカレンダーで選定するマクロ(it-014.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。