2020/06/29

年月をスクロールバーで選択する予定表ひな型




1.背景

システム内で「年月日」を入力するには、デジタル値を入力したり、カレンダーを表示させ日付を選択したりします。この手法は様々なサイトで紹介されています。
しかし、例えばカレンダーを表示する時の「年月までの入力」などについての効率化・直感化の手法については、あまり見掛ける事はありません。
「年月ならカレンダーの中で移動できるから」とか「月は1~12と選択範囲が狭いから」という理由から少ないのかもしれませんが、「省力化」「入力ミスを防ぐ」という面から少し工夫をしてみました。
この年月選択をカレンダー表示切り替えに使ったものを今回紹介します。

2.概要

見掛けは「カレンダー」と同じです。日付行の下に予定等が記入できる枠(日付とはセルを分けています)があります。

図2-1

F1セルに配置した「年月変更」ボタンを押すと「年月切り替えボックス」が表示され、スクロールバーを操作することで前後1年の年月が選択できる、というものです。
スクロールバーの指している年月は図2-1で言うと2020年2月になります。上段に年の「2020」、下段に月の「2」が表示されており、その両脇に前月・前々月、次月・次々月が表示されています。これは図2-2のように円筒の外周に文字が書いてあるように見せたかったからですが、今一つパッとせず申し訳ありません。

図2-2

年月を変更しダイアログボックスのOKボタンを押すことで、指定した年月のカレンダーに切り替わります。
また、C1セル・G1セルに配置した「前月」「次月」ボタンを押す事でもカレンダーを移動させることができます。

3.プログラム

プログラムは、フォームおよび標準モジュールに分かれています。主にダイアログボックス(=年月切り替えボックス)関連はフォーム、ワークシートのカレンダー関連は標準モジュールに記述しています。

3-1.ワークシートとフォーム間の値の受け渡し方法

今回のアプリでは、「フォーム側で決めた表示年月」を使って「ワークシート側でカレンダーを表示」するのと同時に、「ワークシート側のカレンダーの表示年月」を「フォーム側の選択年月の初期値」にするという仕様にしています。
つまり、ワークシート側(=標準モジュール)とフォーム側(=フォームモジュール)で年月の「値を授受」する必要があります。

ワークシート側とフォーム側で値を受け渡す方法としては、以下の4つが考えられます。
(1) Book内で
Public変数を使用
(2) ワークシートのセルを使用 (3) フォーム上の
コントロールの値を使用
(4) フォーム上のSubや
Functionを呼び出す
呼出側
(標準モジュール)
Public変数に値を代入 セルに値を貼り付ける フォームのLabel等に値を書き
込み後フォームを立ち上げる
フォーム上のSubやFunctionの引数
として値を渡す
フォーム側 受取 Public変数から値を受取る セルから値を受取る フォーム起動時にLabel等から
値を受取る
引数として値を受取った後、
フォームを起動する
受渡 Public変数に値を代入 セルに値を貼り付ける フォームを閉じる前にLabel等に
値を書き込み、Hideで閉じる
フォームが閉じた後,SubやFunction
から引数・戻り値として値を返す
受取側
(標準モジュール)
Public変数から値を受取る セルから値を受取る HideしたフォームのLabel等から
値を受取る
引数・戻り値として値を受取る
図3-1

今回は図3-1の内、一番右の(4)の方法で値の受け渡しをしています。

まず、通常ならば「フォームの起動」は「標準モジュール側から実行」します。通常は図3-2の様なコードです。
  1.  UserForm1.Show      '←通常のフォームの起動方法
図3-2

図3-1の(1)・(2)・(3)の方法を使う場合でしたら、事前処理(Public変数やセルやフォームのコントロールに値を代入・記入しておく)をしておけば図3-2の方法でフォームを呼び出してもOKです。
しかし(4)の方法の場合では、以下の「Subプロシージャでの呼出し」や「Function関数での呼び出し」を使用します。

3-1-1.値の受け渡し方法1(Sub プロシージャを使用)

図3-3は標準モジュール側のコードで、「他のプロシージャを引数付きで呼び出す」方法です。
  1.  TD = Sheet1.Range(YM)       '←日付セル(YM)の値を変数TDに代入
  2.  Call UserForm1.Start1(TD)      '←日付値を引数にしてSubプロシージャを呼び出し
図3-3

まず図3-3の2行目で日付セルの値を変数TDに代入し、呼び出しプロシージャの引数TDを準備します。3行目で、そのTDを引数に指定してプロシージャ「Start1」を呼び出します。
呼び出すプロシージャ「Start1」はフォームモジュールにあるので、「UserForm1.」をプロシージャ名の前に追加する必要があります。

呼び出されるSheet1プロシージャは、図3-4です。
  1. Public Sub Start1(TD As Date)
  2.  If TD = 0 Then TD = Now
  3.  Tdate = TD
  4.  Me.Show
  5.  TD = Tdate
  6. End Sub
図3-4

詳細は図5-2で説明しますが、フォームモジュールのStart1プロシージャは「現在表示されているカレンダーの年月」であるTDを引数として受け取ります。
その引数を6行目でフォームモジュールの共通変数Tdateに代入した後、7行目でフォームを起動します。フォーム上のコードですので、「Me.Show」の「Me」は自身(=フォーム)の事になります。
このフォームは「年月を選択する機能を持ち、新たに選択した年月を変数Tdateに代入する」役割を持っていますので、制御が8行目に移る時には「新たな年月が変数Tdateに入っている」ことになります。

少し戻りますが、呼び出し先のStart1プロシージャの引数TDと、呼び出し元の「Call UserForm1.Start1(TD)」の引数TDは、実は図3-5の様に「同じメモリーアドレスを割り当てられている」のです。

図3-5

図3-5のByRefのRefは「Reference(参照)」の略で、引数をByRef(参照渡し)とすると引数用のメモリー場所を確保し、呼び出し側と呼び出され側で共通のメモリー場所を使用しているのです。
(何も指定しなければByRefになります。逆に「ByVal(値渡し)」を指定すると、別々のメモリー場所になるため、フォームモジュール側と標準モジュール側での引数は独立している事になります。)

ですので、8行目でTdateの値を引数TDに代入してから9行目でStart1プロシージャを終了し図3-3の3行目の次に制御が移った時には、「標準モジュール側の変数TDには、新たに選択した年月値が入っている」ことになります。

3-1-2.値の受け渡し方法2(Function プロシージャを使用)

図3-6は標準モジュール側のコードで、Subプロシージャではなく「Functionプロシージャを呼び出し、その戻り値を使う」方法です。
  1.  TD = Sheet1.Range(YM)       '←日付セル(YM)の値を変数TDに代入
  2.  TD = UserForm1.Start2(TD)      '←日付値を引数にしてFunctionプロシージャを呼び出し
図3-6

10行目は図3-3の2行目と同じで、日付セル値を変数TDに代入し、11行目でそのTDを引数にして「関数Start2」を呼び出します。呼び出されるStart2はフォームモジュールにありますので「UserForm1.」を頭につけます。

呼び出されるStart2プロシージャは、図3-7です。
  1. Public Function Start2(TD As Date) As Date
  2.  If TD = 0 Then TD = Now
  3.  Tdate = TD
  4.  Me.Show
  5.  Start2 = Tdate
  6. End Function
図3-7

フォームモジュールのStart2プロシージャは、TDを引数として受取ります。その後の処理は図3-4の「Sub Start1 」とほぼ同じですが、Start2は「Sub」であるStart1とは異なり、戻り値を持つ関数(Function)です。
ですので「共通のメモリー場所を持つ引数」を使って値をやり取りする必要は無く、16行目で「Start2 = Tdate」と「Functionの戻り値として、新たな年月Tdateを指定」すれば良いことになります。

Start2の戻り値となった「新たな年月」は、図3-6の11行目で「TD = UserForm1.Start2(TD) 」の左辺の値として受取ります。

戻り値のTDと引数のTDが同じスペルなので混乱しそうですが、今回は「左辺から新たな年月」が返ってきています。
Subを使うのかFunctionを使うのかは分かり易さで決めるべきですが、私なら「フォームで選んだ値を明示的に返してくれるFunctionを選ぶ」かもしれません。

尚、今回は「値を受取るために、フォームを起動するプロシージャを標準モジュールから呼び出す」という側面でこの手法を紹介しましたが、通常のプロシージャ呼び出しの場面でも「複数の引数を添付してプロシージャを呼び出し」すれば、「複数の戻り値が同時に得られる」ことにもなります。
「Functionだと1つしか戻り値が得られない」とか「戻り値を配列にしてから返すのは面倒」と思われている方は、試してみると良いかと思います。

4.フォームへのコントロールの配置

各コントロールをフォーム上に図4-1のように配置します。

図4-1

ボタン表面の文字、左端Label「年」「月」の文字は、配置時に変更しています。その他のLabel1~10、スクロールバーのプロパティは初期状態のままで、フォームのコード(Initializeイベントプロシージャ)からプロパティ変更をしています。

5.フォームコード

次は、呼び出される側(=フォームモジュール側)のコードです。

5-1.変数の宣言

まず図5-1はフォーム内で使用する変数の宣言です。フォーム上で指し示している年月(Date型)をTdate変数として保管しています。
  1. '========== ⇩① 変数の宣言(フォームモジュール) ===========
  2. Dim Tdate As Date
図5-1

5-2.フォームの起動

次に、標準モジュールから呼び出されるプロシージャが図5-2になります。
内容は、図3-4、図3-7で説明したコードと同じで、21~26行目がSubプロシージャで受け渡しするコード、29~34行目がFunctionプロシージャで受け渡しするコードです。
(サンプルファイルは、Subプロシージャの方を生かしてあります。)
  1. '========== ⇩② フォームの起動(Subプロシージャでデータ受け渡し) ==================
  2. Public Sub Start1(TD As Date)
  3.  If TD = 0 Then TD = Now
  4.  Tdate = TD
  5.  Me.Show
  6.  TD = Tdate
  7. End Sub
  8. '========== ⇩③ フォームの起動(Functionプロシージャでデータ受け渡し) ==================
  9. Public Function Start2(TD As Date) As Date
  10.  If TD = 0 Then TD = Now
  11.  Tdate = TD
  12.  Me.Show
  13.  Start2 = Tdate
  14. End Function
図5-2

まず21~26行目の「Subプロシージャでデータ受け渡し」をする方法について説明します。

21行目の「Sub Start1」が受け取る引数TDは「現在表示しているカレンダーの年月」です。
22行目で、まず引数TDの値を調べますが、もしカレンダーが未表示(全くの初期状態を想定)の時は「年月のセルが空」という事になります。
文字列として引数を受取るのであれば「""(空文字)」になりますが、今回引数はDate型ですので「年月が空の時、引数TDの値はゼロ」となるため、22行目で変数TDに今日の日付を代入(=引数として今日の日付が来た事にする)しています。
23行目では、そのTDをフォーム内変数であるTdateに代入してから、24行目で自分(=フォーム)を起動します。

フォーム起動後、ダイアログボックス内で年月を選択しますが、ダイアログボックスを閉じる際に最終的に選択した年月がフォーム内変数Tdateに入り、制御が25行目に戻ってきます。
25行目では「TD = Tdate 」で引数TDに代入した後「Sub Start1」を終了することで、処理は標準モジュール(図6-3の166行目の次の行)に移りますが、166行目の引数である「TD 」には「ダイアログボックスで選択した新しい年月」が入っていますので、標準モジュール側ではその新しい年月「TD」を使って処理していきます。

一方、29~34行目の「Functionプロシージャの引数で渡し、戻り値で返す方法」ですが、フォームの起動までは「Subプロシージャ」と同じです。
違いは33行目で、フォームで選択した年月が代入されている変数Tdateの値をFunctionプロシージャStart2の戻り値にしています。
その後、制御が標準モジュール(図6-3の167行目)に戻り、関数の戻り値として左辺のTDに新たな年月値が返ってきます。 それ以降、標準モジュール側ではその新しい年月TDを使って処理していきます。


5-3.フォームの初期化(Initialize)

フォームを起動するときに、まず実行されるのが図5-3のInitializeイベントプロシージャです。
今回のInitializeに記載した内容には、フォームコントロールのプロパティ設定も含まれています。フォームにコントロールを配置する時に各プロパティを変更・設定しても良いのですが、「何のプロパティをどのように変更したか」が後から分からなくなり易いので、出来るだけInitializeイベント等に記載するようにしています。
  1. '========== ⇩④ フォーム初期化(フォームモジュール) ===========
  2. Private Sub UserForm_Initialize()
  3.  ScrollBar1.Min = -12
  4.  ScrollBar1.Max = 12
  5.  ScrollBar1.Value = 0
  6.  Me.Label1.TextAlign = fmTextAlignCenter
  7.  Me.Label2.TextAlign = fmTextAlignCenter
  8.  Me.Label3.TextAlign = fmTextAlignCenter
  9.  Me.Label4.TextAlign = fmTextAlignCenter
  10.  Me.Label5.TextAlign = fmTextAlignCenter
  11.  Me.Label6.TextAlign = fmTextAlignCenter
  12.  Me.Label7.TextAlign = fmTextAlignCenter
  13.  Me.Label8.TextAlign = fmTextAlignCenter
  14.  Me.Label9.TextAlign = fmTextAlignCenter
  15.  Me.Label10.TextAlign = fmTextAlignCenter
  16.  Me.Label1.ForeColor = RGB(128, 128, 128)
  17.  Me.Label2.ForeColor = RGB(64, 64, 64)
  18.  Me.Label3.ForeColor = RGB(0, 0, 0)
  19.  Me.Label4.ForeColor = RGB(64, 64, 64)
  20.  Me.Label5.ForeColor = RGB(128, 128, 128)
  21.  Me.Label6.ForeColor = RGB(128, 128, 128)
  22.  Me.Label7.ForeColor = RGB(64, 64, 64)
  23.  Me.Label8.ForeColor = RGB(0, 0, 0)
  24.  Me.Label9.ForeColor = RGB(64, 64, 64)
  25.  Me.Label10.ForeColor = RGB(128, 128, 128)
  26.  Me.Label1.Font.Size = 9
  27.  Me.Label2.Font.Size = 10
  28.  Me.Label3.Font.Size = 12
  29.  Me.Label4.Font.Size = 10
  30.  Me.Label5.Font.Size = 9
  31.  Me.Label6.Font.Size = 9
  32.  Me.Label7.Font.Size = 10
  33.  Me.Label8.Font.Size = 12
  34.  Me.Label9.Font.Size = 10
  35.  Me.Label10.Font.Size = 9
  36.  Me.Label3.Font.Bold = True
  37.  Me.Label8.Font.Bold = True
  38.  Me.Caption = "予定表の年月を選択して下さい"
  39. End Sub
図5-3

まず、37~39行目はスクロールバーの設定値です。バーの中央値(Value = 0)を基準に、±1年を選択できるような値に設定しています。

41~50行目は、年月の文字(Label1~10)を左右方向で中央揃えにしています。
52~75行目は、文字の色・サイズ・太文字化のプロパティを調整し文字に遠近感を持たせ、図5-4のように立体的に見える様な工夫をしています。

図5-4

52~61行目は、その文字の色を設定しています。一番手前側(Label3、Label8)は濃い黒(RGB(0,0,0))にし、横に行くに従って薄い色にしています。
63~72行目は、文字サイズの設定です。手前側は大きな文字、横に行くに従って小さな文字にしています。
74~75行目は、最も近いはずの文字を太文字化(Bold)しています。

77行目は、ダイアログボックスのタイトル部に、ユーザーへの指示を表示しています。

5-4.フォーム表示時の初期化(Activate)

フォームが初めて起動する時にはInitializeイベントが発生します。しかし、起動後一旦フォームを隠した(=Hide)後で、再度表示(=Show)した際にはInitializeイベントは発生しません。
ちなみに、フォームが完全に表示するまでに発生するイベントは、以下の順序(図5-5)で発生します。
 .Show で最初の起動した時  Initialize → Layout → Activate
.Hide で閉じる 何も発生せず(「閉じるボタン」のイベントのみ)
.Show で再表示 Activate のみ
Unload で閉じる QueryClose → Terminate
図5-5

今回の場合、フォームは「.Hide」で閉じます。フォームが閉じている間は「フォーム終了時の状態のまま保存」されます。しかし、閉じている間にユーザーが「前月」「次月」ボタンを使ってカレンダー表示を変更する事は可能です。
つまり、フォームを次に再表示する際「フォームの年月の表示」と「ワークシート上のカレンダーの年月」が異なっている可能性があるのです。
フォームを再表示した時に発生するイベントは「Activateイベント」のみです。そのため図5-6の様にActivateイベントで「フォームの表示を現在表示してあるカレンダーの年月に合わせる」ことをしています。

尚、YMout(Year Month OutPut の略のつもり)は、フォーム内に表示されている年月を「フォーム内共有変数Tdate(カレンダーの表示年月)」中心に書き換えるプロシージャ(図5-7)です。
  1. '========== ⇩⑤ フォームアクティブ時(フォームモジュール) =============
  2. Private Sub UserForm_Activate()
  3.  Call YMout
  4. End Sub
図5-6

5-5.ダイアログの年月表示を書き換える

図5-7が、「フォーム中の年月表示をTdate中心に書き換える」プロシージャです。
  1. '========== ⇩⑥ 年月の表示(フォームモジュール) ===========
  2. Private Sub YMout()
  3.  Dim Ldate As Date, Fdate As Date, LLdate As Date, FFdate As Date
  4.  Ldate = DateAdd("m", -1, Tdate)   '前月
  5.  LLdate = DateAdd("m", -2, Tdate)  '前々月
  6.  Fdate = DateAdd("m", 1, Tdate)   '次月
  7.  FFdate = DateAdd("m", 2, Tdate)   '次々月
  8.  Me.Label3 = Year(Tdate)
  9.  If Not Year(Tdate) = Year(Ldate) Then
  10.   Me.Label2 = Year(Ldate)
  11.  Else
  12.   Me.Label2 = ""
  13.  End If
  14.  If Not Year(Ldate) = Year(LLdate) Then
  15.   Me.Label1 = Year(LLdate)
  16.  Else
  17.   Me.Label1 = ""
  18.  End If
  19.  If Not Year(Tdate) = Year(Fdate) Then
  20.   Me.Label4 = Year(Fdate)
  21.  Else
  22.   Me.Label4 = ""
  23.  End If
  24.  If Not Year(Fdate) = Year(FFdate) Then
  25.   Me.Label5 = Year(FFdate)
  26.  Else
  27.   Me.Label5 = ""
  28.  End If
  29.  Me.Label6 = Month(LLdate)
  30.  Me.Label7 = Month(Ldate)
  31.  Me.Label8 = Month(Tdate)
  32.  Me.Label9 = Month(Fdate)
  33.  Me.Label10 = Month(FFdate)
  34. End Sub
図5-7

フォーム内には共有変数「Tdate」があり、フォームの表示年月の中央値が代入されています。
しかしフォーム上の表示年月は「年月中央値 ± 2か月」は不明ですので、その計算を87~90行目で行い変数に代入します。
変数名として、Ldate = 前月、LLdate = 前々月、Fdate = 次月、FFdate = 次々月 と名前を付けました。

92~115行目は、表示年月の上段部分の「年」の表示を行っている部分です。
表示の「年月の中央値 ± 2か月」の全てに年を表示しても良いのですが、ダブリを省いて見易くなるように「中央値の年は必ず表示」+「年が切り替わるところだけは年を表示」することにしました。

まず年月を表示する為のLabelの位置を再確認します。

図5-8(図5-4と同一)

中央値の年はLabel3です。「中央値の年は必ず表示」するので、まず92行目で「中央値の年」を記入しています。

次に、前月の年(Label2)をどうするか93行目で判断します。つまり「中央値と同じ年か否か」で判断し、異なれば(=中央値は1月で前月値は12月)94行目で年を記入します。
また同じであれば(=12月では無い)96行目で空文字("")を記入します。

前々月(Label1)の場合は、比較は中央値ではなく前月の値と比較します。
つまり「前月と同じ年か否か」で判断し、異なれば(=前月が1月で、前々月が12月)100行目で年を記入、同じであれば102行目で空文字("")を記入します。

次月(Label4)の場合は、中央値と異なれば(=中央値が12月で次月が1月)106行目で年を記入、同じであれば108行目で空文字("")を記入。
次々月(Label5)の場合は、次月と異なれば(=次月が12月で次々月が1月)112行目で年を記入、同じであれば114行目で空文字("")を記入します。

今回、Labelを配列にしなかったために、各Labelごとに条件式で判別せざるを得ず、ダラダラしたコードになってしまいました。また「年を比較」するのではなく「月が1か、月が12か」で条件式を組み立てる方法もあると思います。ご自分の理解し易い方法でコードにして下さい。
尚、年を記入するLabel1~5の5つの内、年が記入されるのは多くても2つなので、まず最初にLabel1~5の5つ全てに空文字("")を代入してしまう方法も、コードが短くなるため良い方法と考えられます。


117~121行目の「Label6~Label10」は「月の表示」です。月は年と違って前後で同じになる事はありませんので、前々月~中央年月~次々月までの「月の表示」を記入します。

5-6.スクロールバー操作時の動作

スクロールバーを操作した時のイベントプロシージャが図5-11になります。
「操作」といっても、どこを操作するかで発生するイベントも異なってきます。
図5-9はスクロールバーの部位を表したもので、発生するイベント等との関連は図5-10になります。

スクロールバーの部位名
図5-9

クリックする場所操作動く量・示す値発生イベント
スクロール矢印クリックSmallChangeプロパティの量Changeイベント
スクロールボックスと
スクロール矢印の間の領域(レール)
クリックLargeChangeプロパティの量Changeイベント
スクロールボックスクリックし移動中スクロールボックスの位置のValue値
(動く量はValue=1ずつ)
Scrollイベント
移動後クリックを離すクリックを離した場所のValue値Changeイベント
図5-10

通常スクロールバーと言うとChangeイベントを真っ先に思いつくと思いますが、スクロールボックスをつまんで動かしている間はChangeイベントは発生しません。「スクロールボックスを動かすと同時に年月の表示を変更」しようとするならば、Changeイベントと併せてScrollイベントも使用する必要が生じます。
  1. '========== ⇩⑦ スクロールバー操作時の動作(フォームモジュール) ============
  2. Private Sub ScrollBar1_Change()
  3.  Tdate = DateAdd("m", ScrollBar1.Value, Tdate)
  4.  ScrollBar1.Value = 0
  5.  Call YMout
  6. End Sub
  7. Private Sub ScrollBar1_Scroll()
  8.  Static LastValue As Integer
  9.  Tdate = DateAdd("m", ScrollBar1.Value - LastValue, Tdate)
  10.  LastValue = ScrollBar1.Value
  11.  Call YMout
  12. End Sub
図5-11

5-6ー1.スクロール矢印・レール部をクリック操作した場合

まず「スクロール矢印をクリック」又は「レール部をクリック」で年月の操作をした場合は、124~128行目のChangeイベントで処理します。

スクロールバーの初期値はゼロ(図5-3の39行目で設定済み)で、動かした後にスクロールバーが指し示している値はValue値で得られます。その値を「〇か月のズレ」と置き換えて、125行目の「DateAdd("m", ScrollBar1.Value, Tdate)」で、年月の中央値(Tdate)をズラしています。
中央値をズラした後で、126行目でスクロールバーのValue値をゼロに戻し、スクロールボックスを中央に戻します。次に操作する時の初期値をゼロにするためです。
最後に、YMsoutプロシージャを呼び出し、年月の表示を更新します。

実は126行目で「Value値をゼロ」にすると、Changeイベントがまた発生(再帰呼び出し)するのです。実行順序としては、
 124行目 → 125 → 126 → 124 → 125 → 126 → 127 → 128 → 127 → 128行目  という順番です。
つまり、127行目の「年月Labelの再表示」は2回実行されています。しかし、再表示時のTdate値は2回とも「ズラした後の値」ですので、パラパラと文字が動くような現象にはならないのと、Labelを10箇所書き換えているだけで処理時間も短いと判断し、再表示はそのまま実行させています。
ただ、同じ処理の繰り返しは無駄であることに変わりありません。これを避ける方法としては、126行目の「Value値をゼロにする処理」を実行する際に「スクロールバーの初期化をしているだけなので、Changeイベントはスルーして下さい」という意味のフラグを立てる方法があります。(図5-12)
  • Private Sub ScrollBar1_Change()
  •  If Flag0=True Then Exit Sub   '←フラグがTrue(スクロールバーのValue値をゼロにしている)の時は終了
  •  Tdate = DateAdd("m", ScrollBar1.Value, Tdate)
  •  Flag0=True           '←フラグを立てる(True)
  •   ScrollBar1.Value = 0
  •  Flag0=False          '←フラグを寝かす(False)
  •  Call YMout
  • End Sub
図5-12

尚、図5-12の方法を行う時は、フラグ変数「Dim Flag0 As Boolean」はフォームモジュール共有の変数としてモジュール先頭で宣言するか、またはプロシージャ内に「Static Flag0 As Boolean」と値保持型として宣言して下さい。
また、イベントを停止させると言うと「Application.EnableEvents = False」を思い出されると思いますが、このEnableEventsはワークシートでは通じますが、フォームでは通じませんので注意して下さい。

5-6ー2.スクロールボックスを移動させる操作をした場合

次に「スクロールボックスを移動させて年月を設定する方法」の処理が、130~135行目になります。

まず、131行目で「Static LastValue As Integer」と、変数「LastValue」を値保持型として宣言します。
これは、スクロールボックスの1つ前の位置を変数「LastValue」に記憶し、次にスクロールボックスが移動した場所との差を移動量とすることで、スクロールボックスの動きと年月表示を合わせるためです。

「スクロールボックス移動」の特徴として、スクロールボックスを「移動させている途中はScrollイベント」のみが発生していますが、ある年月の場所でマウスを止め「左クリックを外すとChangeイベント」が発生することです。
図5-13は、初期に1月を表示していた場所からスクロールバーのスクロールボックスをマウスで移動し、4月で左クリックを外した時のコードの実行順序を示してあります。

ScrollとChangeイベントの往復
図5-13

図5-13で、このイベントプロシージャで宣言している「LastValue」の値と表示年月の関係も説明していきます。

まず、スクロールボックスを動かすとScrollイベントが発生します。スクロールバーのValue値の初期値はゼロですので、そこから右側に1だけ動かした時を考えます。
132行目の右辺は「DateAdd("m", ScrollBar1.Value - LastValue, Tdate)」です。式の中のLastValue値は宣言したままの状態なのでゼロです。またスクロールバーのValue値は1に変化しています。
ですのでこの式は「DateAdd("m", 1 - 0, Tdate)」となり、Tdateつまり1月の1か月後となり「Tdate=2月」になります。 133行目では、Value値を変数LastValueに代入しますので、LastValue=1となります。
134行目ではTdate(=2月)を中心にして年月を表示します。

スクロールボックスをマウスで掴んだまま、更に1だけ右に動かします。
またScrollイベントが発生し、132行目「DateAdd("m", ScrollBar1.Value - LastValue, Tdate)」を計算します。この時点ではTdateは2月、LastValue=1で、スクロールバーのValue値は2になっています。ですから「DateAdd("m", 2 - 1, Tdate)」で、Tdateは3月になります。また133行目でLastValue=2になります。

このようにスクロールボックスを右に動かしていくとTdateが1か月ずつ先に送られ、LastValue値も増えていきます。

スクロールバーのValue値が3になるとTdateは4月となり、LastValueには3が代入され134行目で4月を中心としたLabel表示になります。
ユーザーが「この年月でOK」と、ここでマウスの左クリックを離したとします。

クリックを離すと、Scrollイベントのコードを全て実行した後、Changeイベントに移動します。
Changeイベントに入った時には、スクロールバーのValue値は3であり、またTdateは4月になっています。
125行目にコードが進むと、右辺は「DateAdd("m", ScrollBar1.Value, Tdate)」ですので、4月から3か月進みTdateは「7月」になります。

そして126行目ではValue値にゼロを代入しますのでValue値=0ですが、Value値が変化すると「スクロールボックスが動いた」と判断するようでScrollイベントが割り込んで発生します。

Scrollイベントプロシージャの中では、既にValue値はゼロになっていますが、スクロールボックスを離した位置はLastValue=3としてまだ記憶されています。
したがって「DateAdd("m", ScrollBar1.Value - LastValue, Tdate)」の式は「DateAdd("m", 0 - 3, Tdate)」となり、7月の3か月前、つまりTdateは4月に戻され、134行目で4月を中心に表示が行われます。

表示が完了したらScrollイベントプロシージャを抜け、元のChangeイベントプロシージャの126行目の次の127行目に移り、Tdate=4月を中心に表示が行われます。

ここで「Scrollイベントの後にChangeイベントが発生するはずなので、表示は3回では?」という疑問が出ますが、色々テストしてみると「表示は2回」の様です。
つまり「スクロールボックスを操作した後に発生するChangeイベント内でValue値を変更しても、Scrollイベントは発生するがChangイベントは再帰呼び出しされない」ようなのです。
この理屈は良く分かりません。

また「スクロールボックスでの操作」と「スクロール矢印等での操作」を混ぜた時に表示がおかしくならないか心配になりますが、「スクロールボックスでの操作」の工程の最後のScrollイベントプロシージャ内の133行目で「LastValue値=ゼロ」と初期化しており、操作ごとに完結させているため誤表示はありません。


5-7.「今月」ボタンをクリックした時の動作

次に「今月」ボタンを押した時のClickイベントプロシージャが図5-14になります。
  1. '========== ⇩⑧ 「今月ボタン」のクリック時(フォームモジュール) ==================
  2. Private Sub CommandButton1_Click()  '//「今月」ボタン
  3.  Tdate = Now
  4.  Call YMout
  5. End Sub
図5-14

138行目で、年月表示の中央値として現在の日付を代入した後、139行目で年月Labelの再表示を行うことで、「今月」を中央に持ってきてます。

5-8.「OK」「Cancel」のボタンをクリックした時の動作

「OK」ボタンをクリックした時が142~144行目、「Cancel」ボタンをクリックした時が146~149行目になります。
また、ダイアログの右上の×印で強制的に終了した時には151~155行目が働きます。
  1. '========== ⇩⑨ フォーム終了時の処理(フォームモジュール) =================
  2. Private Sub CommandButton2_Click()  '//「OK」ボタン
  3.  Me.Hide
  4. End Sub
  5. Private Sub CommandButton3_Click()  '//「Cancel」ボタン
  6.  Tdate = 0
  7.  Me.Hide
  8. End Sub
  9. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) '//右上×印で終了した時
  10.  Cancel = True
  11.  Tdate = 0
  12.  Me.Hide
  13. End Sub
図5-15

まず「OK」ボタンをクリックした時は、「ユーザーが選んだ年月(Labelの中央値)を呼び出した標準モジュールに返す」必要があります。また、その他(「Cancel」ボタン、ダイアログの×印 )の場合は、「変更をやめる」という意味にとらえて「標準モジュールにはゼロを返す」ことにしました。

「OK」ボタンの場合は、143行目でダイアログを閉じ(Hide:隠す)ます。ダイアログを閉じると、ダイアログを起動したコードの次に移動しますので、図5-2の25行目、または図5-2の33行目を実行します。
図5-2の「Sub Start1」は、25行目で引数であるTDにPublic変数Tdate(選択した年月)を代入した後、26行目で閉じますので、標準モジュール側に引数TDを通じて年月を渡すことになります。
また「Function Start2」の場合は、33行目でFunctionの戻り値としてTdateを指定し34行目で閉じますので、呼び出したFunctionの戻り値として選択した日付を受取ります。

「Cancel」ボタンの場合は147行目で選択した年月としてTdateにゼロを代入し、ゼロ値を標準モジュール側に返すことで、標準モジュール側で「戻り値がゼロか否かを判定」し、ゼロだったら「何もしない」ことにしています。

「ダイアログ右上×で閉じる」の場合は、まず152行目で「Cancell = True」の設定を行い「制御して閉じる」という状態にしておき、153行目でTdateにゼロを代入し、正常にダイアログを閉じています。

6.標準モジュールのコード

6-1.定数の宣言

標準モジュールの先頭に、カレンダー表示位置の定数を宣言しています。(図6-1)
  1. '========== ⇩⑩ 変数の宣言(標準モジュール) =============
  2. Const CalTitleRow As Integer = 2  'カレンダーのタイトル行の行位置
  3. Const CalStartRow As Integer = 3  'カレンダーの日付範囲の開始行
  4. Const CalStartCol As Integer = 2  'カレンダーの日付範囲の開始列
  5. Const YM As String = "D1"     '年月表示してあるセル位置
図6-1

カレンダーは通常7列x6行ですが、各日付の下に予定を記入できるセルを追加したため、今回は7列x12行とします。
表示位置は「カレンダータイトル(曜日の書いてある行)の行」「カレンダー本体の開始行」「カレンダー本体の開始列」「表示年月を記入するセル」の4つを図6-2のように設定します。
カレンダー表示位置の定数化
図6-2

6-2.「年月変更」ボタンによる起動

F1セルの上に配置した「年月変更」ボタンを押すと実行されるのが図6-3です。
166行目、または167行目がフォームを呼び出すコードですが、サンプルファイルでは166行目を生かし、167行目はコメントアウトにしてあります。どちらでも動きますので、どちらかをコメントアウトして下さい。
  1. '========== ⇩⑪ 「年月変更」ボタンで作動(標準モジュール) ================
  2. Sub Ymake()  '「年月変更」ボタン
  3.  Dim TD As Date
  4.  TD = Sheet1.Range(YM)
  5.  Call UserForm1.Start1(TD)
  6.  ' TD = UserForm1.Start2(TD) 
  7.  If TD = 0 Then Exit Sub
  8.  Call DateMake(TD)
  9. End Sub
図6-3

163行目は変数TD(ToDayの略)の宣言で、表示している・表示しようとするカレンダーの年月です。Public変数としてTdateを宣言する方法もあるとは思いますが、Publicにすると「フォームを「Cancel」ボタンで閉じる時、元の表示年月に戻るために別の変数にデータを保存しなければならない」等から、プロシージャ内変数としました。
なお、変数TDは「年月」と言ってもDate型ですので「年月日」と日付までのデータが入っています。しかし日付部分は無視し、年と月のデータだけを使って処理していきます。

165行目では表示カレンダーの年月を変数TDに代入したあと、フォームのStart1サブプロシージャ、又はStart2関数プロシージャを呼び出します。
もしワークシートにカレンダーが無い(=カレンダー年月が無い)場合は、変数TDにはゼロとして代入されますので、図5-2の 22行目・30行目で「フォーム側で、ゼロの場合は今日の日付にする」処理をしています。

フォームを閉じて、その日付が変数TDに返されて来たら、まずは168行目でゼロか否かを判断します。ゼロが返されてきたということは、図5-15で説明した通り「Cancelボタンがクリックされた」または「ダイアログ右上の×印がクリックされた」ことを示しているので、カレンダー年月を変更する必要は無くなるため「Exit Sub」で終了させます。

選択した年月がゼロで無かった場合だけ、169行目のDateMakeプロシージャを実行しカレンダーの日付を記入します。

6-3.カレンダーの作成

ダイアログで年月を選択しOKボタンをクリックした時、および図6-9の「前月」「次月」ボタンをクリックした時に実行されるのが図6-4です。引数として「表示するカレンダーの年月(変数TD)」を渡します。
  1. '========== ⇩⑫ カレンダーの日付を記入 =====================
  2. Sub DateMake(TD As Date)    'カレンダーの数値記入
  3.  Dim FirstDay As Date
  4.  Dim LastDay As Integer
  5.  Dim FirstWeek As Integer
  6.  Dim CalArray(1 To 12, 1 To 7) As Integer
  7.  Dim i As Integer, j As Integer, k As Integer
  8.  FirstDay = DateSerial(Year(TD), Month(TD), 1)
  9.  LastDay = Day(DateSerial(Year(TD), Month(TD) + 1, 0))
  10.  FirstWeek = Weekday(FirstDay)
  11.  For i = FirstWeek To LastDay + FirstWeek - 1
  12.   k = Int((i - 1) / 7) + 1
  13.   j = i - (k - 1) * 7
  14.   CalArray(k * 2 - 1, j) = i - FirstWeek + 1
  15.  Next i
  16.  Sheet1.Cells(CalStartRow, CalStartCol).Resize(12, 7) = CalArray
  17.  Sheet1.Range(YM).Value = FirstDay
  18. End Sub
図6-4

173~177行目はプロシージャ内で使用する変数の宣言です。
173行目の「FirstDay」は、表示年月の初日(各月の1日目)の日付です。
174行目の「LastDay」は、表示年月の最終日です。例えば1月でしたら「31」という数値が入ってきます。
175行目の「FirstWeek」は「FirstDay」の曜日で、日曜なら1、月曜なら2という数値が入ってきます。
176行目の「CalArray」はカレンダー全体の配列です。図6-2で説明しましたが、カレンダーの各日付の下に予定を書き込むセルがありますので、7列x12行のサイズにしています。
177行目の「i」「j」「k」はカウンター変数です。

まず179行目では、引数TDの日付を含む年月の初日の日付を取得し、変数FirstDayに代入しています。これは181行目の「初日の曜日」を求めるための準備として実行しています。

初日を求める他の方法としては「Application.WorksheetFunction.EoMonth(Now, -1) + 1」という式も使えます。EoMonthはワークシート関数にもある月の末日を計算する関数です。第二引数にズラす月数を指定するのですが、「-1」を指定することで「前月の最終日」を得ることができます。その最終日に「+1」をすることで当月の初日になります。
但しこの関数はExcel2007以降なので、179行目の式の方が安全です。

180行目は最終日の計算です。式の右辺は「次月の0日目」という意味になり、つまりは今月の最終日が得られます。
先程の例を使えば「Application.WorksheetFunction.EoMonth(Now, 0)」でも求めることができます。

181行目は、初日の曜日値を計算しています。Weekday関数の第二引数を指定していませんので、既定の「日曜日=1」から始まる数値になります。

183から187行目はカレンダーの日付を配列に収めていくコードです。

まず、183行目の開始値「FirstWeek」と終了値「LastDay + FirstWeek + 1」です。
図6-5の様に「月の初日はカレンダーの1行目」に必ずなりますので、カレンダー先頭からの番号(図6-5の赤字の数値)と曜日値(曜日の上の数値)が一致します。ですので開始値は「FirstWeek」となります。
終了値は、その最終日の日付(LastDay)と、初日のズレ分(FirstWeek)+1を足すことで、カレンダー先頭からの番号が得られます。
カレンダー日付を入力する位置
図6-5

次に184行目の「k = Int((i - 1) / 7) + 1」ですが、カレンダー配列の行位置を計算しています。
カレンダー先頭からの番号を「i-1」に置き換える(=番号を1ずつ引く)と、図6-6のようになります。ここで、例えば1行目は「全て7未満なので、7で割った商はゼロ」になります。
つまり「Int((i - 1) / 7)+1」が行位置になります。

カレンダーの行位置の計算
図6-6

また、上で得られた行位置(k)を使って「(k - 1) * 7」を各行で計算してみると図6-7の中央部分になります。「カレンダーの先頭からの番号」からその値を引いたのが図6-7の右図になり、列位置を示すことになります。
つまり185行目の「j = i - (k - 1) * 7」が列位置になります。
カレンダーの列位置の計算
図6-7

日付を入れる行位置(184行目)、列位置(185行目)を求める事ができましたので、186行目で日付を実際のカレンダー配列に入れていきます。但し今回は通常のカレンダーでは無く、1行置きに予定を書き込む行が入っています。
図で表現すると図6-8のようになりますので、代入する行位置は「K*2-1」と置き換えて代入していきます。
コメント行のあるカレンダーの日付入力位置
図6-8

186行目の右辺は「i - FirstWeek + 1」となっています。計算すると1から始まり、月末値で終了します。
右辺の式が分かりにくい場合は、1から順に代入していく様な新たな変数を設けても良いと思います。

189行目では、ワークシートのカレンダー範囲にCalArray(カレンダー日付を入れた配列)を貼り付けます。
次に、そのカレンダーの年月を定められた位置(定数で決めたYMセル位置)に書き込みます。
年月を書き込むセルは「年月のみの表示」形式にしてあります(図6-10の205行目)ので、代入値は「FirstDay」でなく引数「TD」でもOKです。

6-4.カレンダーの「前月」・「次月」移動

カレンダーの「前月」ボタン(C1セル部)、「次月」ボタン(G1セル部)をクリックした時のマクロが図6-9になります。
  1. '========== ⇩⑬ 「前月」「次月」ボタンで作動 ================
  2. Sub IncMonth()   '次月カレンダーの表示
  3.  Call DateMake(DateAdd("m", 1, Sheet1.Range(YM)))
  4. End Sub
  5. Sub DecMonth()   '前月カレンダーの表示
  6.  Call DateMake(DateAdd("m", -1, Sheet1.Range(YM)))
  7. End Sub
図6-9

現在表示されている年月は「Sheet1.Range(YM)」ですので、次月は「DateAdd("m", 1, Sheet1.Range(YM))」となり、その値を引数にしてDateMakeプロシージャを呼び出して、カレンダーを再表示します。
また前月の場合は「DateAdd("m", -1, Sheet1.Range(YM))」を引数にしてDateMakeを呼び出します。

6-5.カレンダーの外観作成(現在のところ、手動で起動)

カレンダーの位置を表す定数(図6-1)さえ決まっていれば、どんな外観のカレンダーでもデータを貼り付けられますが、一応サンプルファイルのカレンダーを作るマクロを添付しておきます。
尚、色とか罫線はマクロにするのが面倒だったので記述しておりません。
  1. '========== ⇩⑭ カレンダー外観作成 ==============
  2. Public Sub CalMake()   'カレンダーの外観作成。罫線・彩色は別(1回だけ実施)
  3.  Dim i As Integer
  4.  Windows(ThisWorkbook.Name).DisplayZeros = False  '数値ゼロは非表示
  5.  Sheet1.Range(YM).NumberFormatLocal = "yyyy""年""m""月"""
  6.  With Sheet1.Cells(CalTitleRow, CalStartCol).Resize(1, 7)  '曜日行
  7.   .HorizontalAlignment = xlCenter
  8.   .Font.Size = 11
  9.   .Font.Bold = True
  10.   .EntireRow.RowHeight = 19
  11.  End With
  12.  For i = 0 To 11 Step 2   '日付行
  13.   With Sheet1.Cells(CalStartRow + i, CalStartCol).Resize(1, 7)
  14.    .HorizontalAlignment = xlCenter
  15.    .Font.Size = 16
  16.    .Font.Bold = True
  17.    .EntireRow.RowHeight = 21
  18.   End With
  19.  Next i
  20.  For i = 1 To 11 Step 2   'コメント行
  21.   With Sheet1.Cells(CalStartRow + i, CalStartCol).Resize(1, 7)
  22.    .HorizontalAlignment = xlGeneral
  23.    .Font.Size = 10
  24.    .Font.Bold = False
  25.    .EntireRow.RowHeight = 73
  26.   End With
  27.  Next i
  28.  For i = 0 To 6      '列の調整
  29.   With Sheet1.Cells(CalStartRow, CalStartCol + i)
  30.    .EntireColumn.ColumnWidth = 19
  31.   End With
  32.  Next i
  33. End Sub
図6-10

ワークシートの詳細設定に「ゼロ値のセルにゼロを表示する」という項目がありますが、204行目はその項目の「レ点を外す」指示をしています。
というのも、図6-4の189行目でデータの配列(CalArray)をワークシートに貼っていますが、そのCalArray配列は176行目でInteger型として宣言をしています。つまり、日付数値が入っていない部分には「ゼロが入っている」ことになります。
カレンダーの日付以外のセルにゼロが入っているのはカッコ悪いので、204行目でゼロ表示無しにしています。

205行目は既に説明してしまいましたが、カレンダーの年月表示セルには「年月日データ」が入っています。表示としては年月までにしたいため、そのような表示形式にしています。

207~212行目は、曜日行を「中央揃え」「文字サイズ11」「太文字化」「行高さ19ポイント」にしています。
214~221行目は、日付行を「中央揃え」「文字サイズ16」「太文字化」「行高さ21ポイント」にしています。
223~230行目は、コメント行を「標準揃え」「文字サイズ10」「普通文字」「行高さ73ポイント」にしています。
232~236行目は、列幅を「19ポイント」にしています。

日付行の下のセルにプログラムで予定等を入力する場合、その文字数によっては表示しきれない場合も生じるかもしれません。その場合には細かい調整(プログラムでセルの大きさを調整するか、枠の大きさに合わせて文字を調整するか)が必要になると思います。


7.最後に

予約を記入できるカレンダーの様な形で本件を紹介しましたが、本当は「予定表のひな型」を作りたかった訳では無く、最終的にはデータベースからデータを呼び出し、予定の埋まった月間予定表として表示することを目指していきます。
(日付の下の空きセルに、CSV等から読み込んだ予定データを貼り付けるつもりです)

今回、スクロールバーのScrollイベント・Changeイベントの動きを紹介しましたが、フォームのコントロールには似たような「Slider」というコントロールがあります。(標準では表示されておらず、その他の中の「Microsoft Slider Control」にレ点を付けてから作業します)
このスライダーも移動に伴ってScroll・Changeというイベントが発生し、その動作はスクロールバーのイベントと同じみたいです。見かけはスクロールバーとずいぶん違うので、代替として試してみる価値はあるかもしれません。


年月をスクロールバーで選択する予定表ひな型(it-032.xlsm)

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