2021/11/28

工程表の日付をスピンボタンで操作

• １．背景
• ２．システム概要
• 　２－１．数式版の工程表
• 　２－２．マクロ版の工程表
• 　　よりみち（マクロ版を使用する時の注意）
• 　２－３．日付変更のスピンボタン
• ３．プログラムの流れ
• 　３－１．日付変更スピンボタンの流れ
• ４．数式版の工程表
• 　４－１．ワークシート上の数式など
• 　　よりみち（数式の工夫）
• 　４－２．条件付き書式の設定
• ５．マクロ版の工程表
• 　５－１．ワークシートモジュール
• 　　５－１－１．日程バーの関係
• 　　５－１－２．スピンボタン関係
• 　　　よりみち（オブジェクト名の異常）
• 　５－２．標準モジュール
• 　　５－２－１．定数宣言
• 　　５－２－２．日程バーの作成
• 　　５－２－３．スピンボタンの準備
• 　　５－２－４．スピンボタン・コメントの作成
• 　　５－２－５．スピンボタン・コメントの削除
• 　　５－２－６．コメントへセル値を書込み
• 　　５－２－７．スピンボタン操作に伴う処理
• ６．最後に
• サンプルファイル

１．背景

図１－１のような工程表は良く見かけます。開始日（C列）・完了日（D列）に日付を入れると、自動的に工程が引かれるように工夫されたExcel表を使っている方も多いと思います。

図１－１

しかし、最初に作った工程表の通りに作業が進むことは極めて稀で、途中で見直し・修正をしていくのが常です。その時に、前後の日程を見比べながら「日付を手入力で修正」しているかと思いますが、これが結構手間です。
今回は、工程表の日付を「スピンボタンをマウスクリックすることで変更」するものを紹介します。併せて開始・完了日を入力することで表示される工程表の方式として、「数式版」と「マクロ版」を紹介します。

２．システム概要

２－１．数式版の工程表

数式版の工程表は、日程エリア（図２－１では、E4～R7セル）の中に、開始日・完了日、及び日付エリア（図２－１では、E2～R2セル）に反応する数式を記入します。数式そのものについては「数式版の工程表」で説明します。
この数式によって各セルに文字列等が表示されるのですが、その文字列等に反応して背景色を変更するのが条件付き書式です。図２－１には、日程エリアに「何かが表示されていれば、背景色を赤にする」書式が設定されています。

図２－１

この数式版工程表のメリットの第一は、マクロ無し（ファイル拡張子が「.xlsx」）で作成できるために共有時・配布時に「マクロを有効にする・・」などの面倒が無いことです。
また、数式で各セルに文字列等が表示されますので、それを使っての集計も可能です。図２－２は各日付ごとに「■印が何個あるか」を８行目で計算し、そのデータの「スパークライン」を貼り付けることで「日毎の負荷率」を表示することも出来ます。

図２－２

工程ごとで負荷の係数は異なるので、図２－２で表したような単純なグラフでは役に立たないかもしれませんが、セルに表示させる値を■印では無く「工程固有の値にする」などの工夫を数式に盛り込んでやれば、少し精度が上がるかと思います。

ただし、数式で作る工程表は、数式のセル数が多くなると「ファイルサイズが大きくなる」「動作が鈍くなる」というデメリットもあります。それを解消する１つの方法が「マクロ版の工程表」です。

２－２．マクロ版の工程表

マクロ版の工程表は図２－３のように、日程エリアには数式などはありません。開始日・完了日を入力すると、マクロにより日程エリア内に日程バー（＝セル背景色を赤）を引きます。バーを引くマクロについては「マクロ版の工程表」で説明します。


図２－３

開始日・完了日のどちらかが未入力（日付以外が入力されていても、未入力状態と見なします）の場合は、日程バーは引かれません。また、開始日>完了日と日付が逆転している場合も引きません。

一見「マクロ版の工程表の方が良さそう」に見えるかもしれませんが、注意する点もあります。例えば日付部（E2～R2セル）の日付を１週間移動したとします。今回のマクロ版工程表は「開始日・完了日」を変更しない限り何もしませんので、自動的に日程バーは動きません（プロジェクト全体を移動するには、返って便利と思うかもしれませんが、日付と開始日・完了日に矛盾が発生してしまいます）。 一方、数式版工程表は日付部を変更すると一斉にバーが動き、日付と開始日・完了日には矛盾が生じません。工程表を作成した後に全体日程を調整するような場合は、マクロ版だと不具合が発生する可能性があります。

２－３．日付変更のスピンボタン

数式版・マクロ版のどちらの工程表にしても、開始日・完了日の「日付」をもとに日程バーを書き換えています。
なお、日付が入っていない状態（空白セル）から日付を入力する場面は、今回は対象としていません。空白セルにカレンダーを使って日付を入れるものについては、以下の項で紹介していますので参考にして下さい。
　「セルへの日付入力をカレンダー日付クリックで選定」
　「MonthViewコントロールを使ったカレンダー」
　「図形カレンダーをクリックし日付入力」
　「ラベルカレンダーをクリックし日付入力」

今回は、開始日列（図２－４のC列）・完了日列（D列）の内、「日付が入力されているセルを選択」した時に「セル値を変更するスピンボタン」が起動するように設定しています。（添付ファイルでは、数式版・マクロ版の両方に設定してあります。）

図２－４

セル選択すると図２－５のように、そのセルに「スピンボタンが表示」されます。また、そのセルの「日付がコメントとして表示」されます（セルの右上）。

図２－５

表示されたスピンボタンをマウスでクリック操作すれば日付が変更されます。「１クリック＝１日」で、スピンボタンの右側をクリックすれば「＋１日」、左側をクリックすれば「－１日」変更します。変更されたセルの日付はスピンボタンに覆われていて見えませんが、右上に表示されているコメントて確認できますし、日程エリアの日程バーの長さでも確認できます。

図２－６

セルをクリック選択してスピンボタンを表示させた直後（図２－５）は、ワークシートのセルが選択されている状態ですので、キーボードの矢印キー（←、→、↑、↓）を操作すると、操作したキー側のセルが「開始日列・完了日列」であれば、スピンボタンが移動（したように見える）します。
一度でもスピンボタンを操作（図２－６）するとスピンボタンを選択している状態になりますので、それ以降はキーボードの「→、↓」キーで「＋１日」、「←、↑」キーで「－１日」となります。また、異なるセルに移動するにはマウスでセル選択する必要があります。

３．プログラムの流れ

マクロ版工程表のマクロは、言わば「数式版工程表の数式」をマクロに置き換えたようなものなので、プログラムの流れについては割愛します。内容は、図５－８のところで詳細説明します。

３－１．日付変更スピンボタンの流れ

「開始日・完了日エリア」は、マクロ内に事前登録（図５－６の２３～２５行目）しておきます。工程表のワークシート上をセルクリックするとSelectionChangeイベントが発生します。

図３－１

SelectionChangeイベント後、まずは既存のスピンボタン・コメントを削除します。もし既存のスピンボタン・コメントが無い場合はエラーが発生しますので、その場合はエラーをスルーさせます。
その後、選択したセル位置が「開始日・完了日エリア」である場合には、新たにスピンボタン・コメントを作成します。
最後に、作成したコメントにセル値（日付）を書き込みます。

スピンボタンを操作した場合には、スピンボタンのChangeイベントが発生しますので、変更された値（ 0 → +1 又は 0 → -1 ）を現在のセル値（日付値）に加えます。（-1を加えると、１日前に移動することになります）
最後にセルのコメントに、変更されたセル値を書き込み（上書き）ます。

４．数式版の工程表（サンプルファイルではSheet1）

４－１．ワークシート上の数式など

図４－１を工程表とすれば、項目（B列）、開始日（C列）、完了日（D列）を縦軸に配置し、日付を横軸（2行目）に配置します。その縦軸・横軸に囲まれたエリアが日程エリアになり、この範囲に数式の記入、および条件付き書式の設定をします。

図４－１

数式は「対応する縦軸の開始日・完了日のセル」と「対応する横軸の日付のセル」で組み立てます。
図４－１のようにE4セルの数式であれば「=IF(AND(E$2>=$C4,E$2<=$D4),"■","")」と、「開始日と日付」「完了日と日付」の両方が成立する時に「■印」を表示するようにします。成立しない場合は「""（長さゼロの文字列）」にします。

表示するものは「■印」でなくてもOKです。項目ごとに異なる重みが必要な場合には、その値のあるセルを参照する数式を作成することで、情報が１つ増えることになります。
左上セルで数式を組み立てた後は、それをコピーし、日程エリア全体に「数式貼り付け」します。

なお、数式として「=IF(E$2>=$C4,IF(E$2<=$D4,"■",""),"")」のようなものも考えられます。但し実際にやってみると、「再計算に時間が掛かる」現象が見られます。 原因は「IF文を入れ子」にしている為のようです（それにしても遅いので、他にも原因がある気がしますが・・）。 もし「再計算が遅い」と感じた時は、入れ子を減らせないか、他の関数で出来ないか　等も検討した方が良いと思います。

４－２．条件付き書式の設定

上記の数式により「開始日・完了日に挟まれた日付のセル」に値（今回は■印）が表示され、図４－２のように設定した条件付き書式により、表示された値に反応して書式を変更しています。

図４－２

条件付き書式の数式は、適用先エリア（＝日程エリア）の左上セルに対して組み立てます。今回の数式は「=E4<>""」とし、「空欄では無い時」に「背景色を赤色」にしています。

５．マクロ版の工程表（サンプルファイルではSheet2）

ワークシート上の工程表フォーマットは、数式版と同じです。但し日程バーはマクロで作成しますので「数式 及び 条件付き書式」は不要です。

５－１．ワークシートモジュール

５－１－１．日程バーの関係

日程バーは、工程表のあるシートの「開始日・完了日」の日付を変更する際に作られます。セルの値を変更した時は、図５－１のChangeイベントが発生します。

1. '==========　⇩(1)　セル値変更によるイベント　============
2. Private Sub Worksheet_Change(ByVal Target As Range)
3. 　Call W_change(Target)
4. End Sub

図５－１

３行目「Call W_change(Target)」で、標準モジュールのW_changeプロシージャ（図５－８）を呼び出します。引数としては、Changeイベントが受け取った「Target（変更したセル範囲）」を渡します。「W_change」プロシージャでは、日程エリアに日程バーを作ります。

５－１－２．スピンボタン関係

図５－２、図５－３、図５－５の３つは、スピンボタン関係のイベントプロシージャです。
まず、ユーザーがワークシート上のセルを選択したときに呼び出されるのが図５－２のSelectionChangeイベントです。

6. '==========　⇩(2)　セル選択によるイベント　============
7. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
8. 　Call W_SelectionChange(Target)
9. End Sub

図５－２

今回システムでは、スピンボタンが表示されるのは「開始日・完了日エリア」のみです。ですのでスピンボタンの表示・非表示のためには、選択したセルが「開始日・完了日のエリア」か否かを判断する必要がありますが、それは８行目「Call W_SelectionChange(Target)」で標準モジュールのW_SelectionChangeプロシージャ（図５－１２）を呼び出した先で分岐させます。引数としては、SelectionChangeイベントが受け取った「Target（選択したセル範囲）」をそのまま渡します。

図５－２の先の「W_SelectionChange」プロシージャによって「日付を変更するスピンボタン」は作られます。そのスピンボタンには「DateSpin」というオブジェクト名が付けられます。
ですので、スピンボタンを操作する（＝値を変更する）と、図５－３の「スピンボタンのChangeイベント」が発生します。

11. '==========　⇩(3)　スピンボタン操作時　============
12. Private Sub DateSpin_Change()
13. 　Call Spin_Change(ActiveSheet)
14. End Sub

図５－３

１３行目「Call Spin_Change(ActiveSheet)」では、標準モジュールのSpin_Changeプロシージャ（図５－１８）を呼出し、セルの日付を変更させます。引数としては「ActiveSheet（現在操作しているシートオブジェクト）」を渡します。

このシステムで使うスピンボタンには、作成する時に「DateSpin」という名前を付けて、その後の制御を行っています。ですので、そのスピンボタンを操作した時に発生するイベントは「DateSpin_Change」になるはずです。 しかし、試行している時に「オブジェクト名は変更されているのに、オブジェクト名は変更されない」状況が発生したのです。図５－４はその時の状況です。名前ボックス①やオブジェクト表示②では、ちゃんと「DateSpin」に変更されているのに、マウス右クリックで表示されるプロパティ一覧③でのオブジェクト名は標準の「SpinButton1」となっているのです。 図５－４ なお、スピンボタンの名前を「SpinButton1」に設定した時には「SpinButton2」になります。つまり、スピンボタンのオブジェクトを作成する時に「既にその名前のオブジェクトは存在するので、名前の付け方の順序に従って自動的に異なる名前を付ける」ようなのです。それも中途半端に・・・ もちろん頻繁に発生する訳ではありません。見つけた条件としては以下ですが、再現しない場合も多々あり、また違う条件も存在するようです。 　「スピンボタンが表示されている状態でDELキーを押す」→「セル値は空白セルになり、同時にスピンボタンも削除」→「そのセルに日付を入力」→「矢印キーで日付の有るセルに移動」→「スピンボタンが表示」→「更に、異なる日付セルへ移動」→「スピンボタンが移動」→「オブジェクト名の異常を発見」 既に記入されている日付を削除する場面は、あまり多く無いとは思いますが、この中途半端なオブジェクト名は非常に迷惑です。この現象が発生した場合、スピンボタンの操作に対するClickイベントは、自動設定のオブジェクト名「SpinButton1」として反応してしまいますので、図５－３の「DateSpin_Change」では拾えなくなります。逆にOLEObjectsの名前は「DateSpin」になっているので、処理自体はDateSpinで行う必要があります。 私の2台のノートPCでは両方ともこの不具合が発生しますので、もしかしたらExcelのバグなのかもしれません。常に発生する訳では無いので、プログラムミスでは無いとは思っているのですが、このままでは「スピンボタンをクリックしても日付が変わらない」という機能不良になります。 ですので、今回は「オブジェクト名が異常になっても、日付を変える動作をさせる」ことを考えました。それが図５－５の「異常なオブジェクト名でのClickイベント」です。 バージョンの異なるExcel（私のは2016 32ビット版）での発生有無は不明です。もしかしたら全く使われないイベントプロシージャになってしまう事をご了承下さい。

上記「よりみち」で説明したように、スピンボタンのオブジェクト名に異常が発生した場合、イベントはオリジナルの「SpinButton1」というオブジェクト名で発生するようです。ですので図５－５は「オブジェクト名が異常時のスピンボタンの操作」に反応するイベントプロシージャとなります。
図５－３と同様、１８行目「Call Spin_Change(ActiveSheet)」で、標準モジュールのSpin_Changeプロシージャ（図５－１８）を呼出し、セルの日付を変更させます。

16. '==========　⇩(4)　オブジェクト名異常時のスピンボタン操作時　============
17. Private Sub SpinButton1_Change()
18. 　Call Spin_Change(ActiveSheet)
19. End Sub

図５－５

なお上記マクロは、マクロ版工程表（Sheet2）のシートモジュールに存在するものとして説明しましたが、図５－２、図５－３、図５－５の３つは「スピンボタン」に関するマクロのため、「数式版工程表としても使用可」です。
「サンプルファイル」では、数式版工程表（Sheet1）のシートモジュールにもマクロを記述していますので、「スピンボタンで日付を変更し、数式で日程バーが動く工程表」となっています。

但し、複数シートで同じマクロを使用する時の注意点として、今回システムでは「開始日・完了日エリアの位置の設定を標準モジュールの宣言部で行っている」ため、工程表のフォーム（開始日・完了日などのセル位置）が同一である必要があります。

５－２．標準モジュール（Module1）

５－２－１．定数宣言

宣言部（図５－６）では、工程表の位置関係などの定数宣言をしています。

21. '==========　⇩(5)　定数宣言　============
22. Private Const itemCol As Long = 2 　　　　 '項目列番号
23. Private Const StartCol As Long = 3 　　　　 '開始日列番号
24. Private Const EndCol As Long = 4 　　　　 '完了日列番号
25. Private Const DataRow As Long = 4 　　　 'データ開始行
26. Private Const DateS As String = "E2" 　　　 '日付開始日
27. Private Const DateE As String = "R2" 　　　 '日付終了日
28. Private Const ColorNum As Long = 255 + 0 * 2 ^ 8 + 0 * 2 ^ 16 　　 '日程バーの色:RGB(255,0,0)
29. Private Const SBname = "DateSpin" 　　　 '日付変更用スピンボタンの名前

図５－６

各定数は、図５－７の各部を指しています。

図５－７

２６～２７行目の定数「DateS」「DateE」は、セル位置を文字列として定数宣言してます。
また、２８行目の「ColorNum」は日程バーの色（＝セル背景色）です。本当なら「RGB(255,0,0)」の様に設定したいのですが、宣言部では関数が使えないため、Long型の数値に直して設定しています。

５－２－２．日程バーの作成

Worksheet_Changeイベント（図５－１の３行目）から呼び出され、「日程バーの作成」をするのが図５－８です。引数として変更されたセル範囲（Target）を受取ります。

31. '==========　⇩(6)　日程バーの作成　============
32. Public Sub W_change(Target As Range)
33. 　Dim RDay As Range 　　 '横日付エリア（Referenceの略）
34. 　Dim SDay As Date 　　 '横日付エリアの初日
35. 　Dim EDay As Date 　　 '横日付エリアの最終日
36. 　Dim T As Range 　　　 '引数Targetで得られたセル範囲の1つ1つ
37. 　Dim S As Integer 　　 '日程バーの始まり位置(横日付エリアとのズレ量)
38. 　Dim L As Long 　　　 '日程バーの長さ
39. 　Dim C As Integer 　　 '日程バーの行位置(横日付エリアとのズレ量)
41. 　For Each T In Target
43. 　　If Not ((T.Column = StartCol Or T.Column = EndCol) And T.Row >= DataRow) Then GoTo NX
45. 　　Set RDay = Range(DateS & ":" & DateE)
46. 　　C = T.Row - RDay.Row
47. 　　RDay.Offset(C, 0).Interior.Pattern = xlNone
49. 　　If T.Value = "" Then Call SpinOff
51. 　　On Error Resume Next
52. 　　　SDay = Cells(T.Row, StartCol)
53. 　　　EDay = Cells(T.Row, EndCol)
54. 　　On Error GoTo 0
56. 　　If SDay * EDay = 0 Then Exit Sub
58. 　　If SDay <= RDay(1).Value Then SDay = RDay(1).Value
59. 　　If EDay >= RDay(RDay.Count).Value Then EDay = RDay(RDay.Count).Value
61. 　　If SDay > EDay Then Exit Sub
63. 　　S = SDay - RDay(1).Value
64. 　　L = EDay - SDay + 1
66. 　　RDay.Offset(C, S).Resize(1, L).Interior.Color = ColorNum
68. NX:
70. 　Next T
72. End Sub

図５－８

４１行目「For Each T In Target」は、引数として受け取った「変更されたセル範囲（Target）」を１つ１つのセルに分解し作業を行っていきます。通常セルの書き換えは１セルずつですが、別な場所で作った複数セルのデータ（項目・開始完了日のまとまったデータなど）を一気に貼り付ける場合が考えられます。
その場合は１セルずつ処理しますが、逆に同じ行の開始日データと完了日データも別々に対応するため、同じ日程バーを２回引くことにもなり、多少効率は悪くなります。

４３行目「If Not ((T.Column = StartCol Or T.Column = EndCol) And T.Row >= DataRow) Then GoTo NX」では、変更されたセルが「開始日・完了日の列で、データ開始行より大きい」以外のときに、ラベルNX（６８行目）にジャンプさせます。つまり、開始日・完了日エリア以外では、４５～６６行目の「日程バーを消したり・作ったりする処理」を行わずにスルーさせます。

４５行目「Set RDay = Range(DateS & ":" & DateE)」は、宣言部（図５－６）で定数設定した日付エリアの開始日・終了日のセル位置を使って、日付エリアをRange設定しています。図５－９だと、RDayが日付エリアになります。

図５－９

４６行目「C = T.Row - RDay.Row」は、値を変更したセル位置と日付エリアの行位置の差を計算しています。
４７行目「RDay.Offset(C, 0).Interior.Pattern = xlNone」の前半の「RDay.Offset(C, 0)」は、図５－９のように「日付エリアを、４６行目で求めた行位置の差だけ下に移動したRange位置」になります。その位置の背景色をxlNoneでクリアすることで、日程バーを削除しています。

４９行目「If T.Value = "" Then Call SpinOff」は、値変更したセルが空白セルだったらSpinOffプロシージャ（図５－１６）を呼び出して「スピンボタンを削除」する処理をします。

しかし、スピンボタンを作るW_SelectionChangeプロシージャ内では「セルが日付で無ければスピンボタンは作成しない」「複数セル選択の場合はスピンボタンを作成しない」コードになっていますので、一見４９行目は不要に見えます。
この４９行目のコードは「日付が入っているセルを、DELキー等で空白セルにする」時に「空白セルの上に置かれたスピンボタンを削除」するために設けています。
本当なら「マクロ版工程表の、開始日・完了日で日程バーを引くプロシージャ（W_Change）」の独立性を高めるために、プロシージャ内にはスピンボタン関連のコードを入れたく無かったのですが、「（セル位置を動かさずに）値を削除した」ことを検知するイベントがChangeしか無いために、仕方なく「日程バー作成の間にスピンボタン関係のコードを入れている」次第です。

５１～５４行目では、開始日・完了日を変数SDay・EDayに代入しています。
５２行目「SDay = Cells(T.Row, StartCol)」では、変更セルと同一行の開始日列のデータを変数SDayに代入します。
５３行目「EDay = Cells(T.Row, EndCol)」では、変更セルと同一行の完了日列のデータを変数EDayに代入します。
SDayかEDayのどちらかが、変更したセル自体なのですが、どちらか分からないのでこのような書き方にしています。
SDay・EDayはDate型として宣言していますので、セル値が日付相当なら日付として代入されますが、空白セルの場合には「ゼロ（1900年1月1日の前日）」が代入されます。
なお、セル値が文字列だった場合にはエラーが発生しますので、５１行目「On Error Resume Next」でエラーをスルーさせることで、初期値である「ゼロ」となります。

５６行目「If SDay * EDay = 0 Then Exit Sub」では、SDay（開始日） または EDay（完了日）が、空白または文字列の時にはプロシージャを抜けます。４７行目で日程バーを削除してますので、結果として日程バーが表示されない事になります。
なお４９行目の「If T.Value = "" Then Call SpinOff」と機能が似ているため、まとめて「Call SpinOff」と「Exit Sub」を実行しても良さそうに見えますが、このプロシージャが「スピンボタンを操作した結果として呼び出された場合」は、呼出し元のスピンボタンを削除してしまうことになり、エラーが発生してしまいますので、４９行目は独立して必要です。

今回システムでは、日程バーを「日程エリア左端からの距離」と「日程バーの長さ」で表現しています。一方で開始日・完了日が「日程エリア内の日付」では無い可能性もあるため、「日程エリア内での開始日・完了日」にしておく必要があります。
考え方としては、図５－１０のように、日程エリアの先頭日・最終日とデータの開始日・完了日を見比べて、エリア内に入るように修正しています。なお、開始日・完了日の両方がエリア外のデータに対して修正を行うと「開始日と完了日が逆転」するため、除外の対象となります。

図５－１０

５８行目「If SDay <= RDay(1).Value Then SDay = RDay(1).Value」では、日付エリアの先頭日（RDay(1)）よりもデータの開始日（SDay）が小さいときに、開始日を先頭日に修正します（図５－１０の赤字のSDay）。
５９行目「If EDay >= RDay(RDay.Count).Value Then EDay = RDay(RDay.Count).Value」は、日付エリアの最終日（RDay(RDay.Count)）よりもデータの完了日（EDay）が大きいときに、完了日を最終日に修正します（図５－１０の赤字のEDay）。

６１行目「If SDay > EDay Then Exit Sub」では、開始日と完了日が逆転している時にはプロシージャを抜け出し、日程バーを表示しません。逆転のデータには「元々のセルの値が逆転」しているデータもありますし、図５－１０のように日程エリア外のために５８～５９行目で「修正されて逆転」になったデータも含まれます。

今回システムは「日程エリア左端からの距離」と「日程バーの長さ」で日程バーを表現します。その計算を６３～６４行目で行います。計算の仕組みは図５－１１のようになります。

図５－１１

６３行目「S = SDay - RDay(1).Value」は、日程バーの左端が日程エリアの左端からいくつ離れているかを計算します。
６４行目「L = EDay - SDay + 1」は、日程バーの長さを計算します。
このSとLの値を使った日程バーの範囲は「RDay.Offset(C, S).Resize(1, L)」となります。

６６行目「RDay.Offset(C, S).Resize(1, L).Interior.Color = ColorNum」は、その日程バーのRange範囲の背景色を定数ColorNum（図５－６の２８行目で定数設定した色）の色に変更します。

６８行目「NX:」は、変更されたセル位置が開始日・完了日のエリア外の場合にジャンプしてくるラベルです。この先の７０行目「Next T」により、次の変更セル範囲の処理に移ります。

５－２－３．スピンボタンの準備

ユーザーがセル選択した時（SelectionChangeイベント：図５－２の８行目）に呼び出されるのが図５－１２です。引数として選択したセル範囲（Target）を受取ります。

74. '==========　⇩(7)　スピンボタンの準備　============
75. Public Sub W_SelectionChange(Target As Range)
77. 　Call SpinOff
79. 　If Target.CountLarge > 1 Then Exit Sub
81. 　If (Target.Column = StartCol Or Target.Column = EndCol) _
82. 　　　And Target.Row >= DataRow And IsDate(Target.Value) Then
84. 　　Call SpinOn(Target)
86. 　End If
88. End Sub

図５－１２

７７行目「Call SpinOff」では図５－１６を呼び出し、スピンボタンが存在する場合はまず削除します。
７９行目「If Target.CountLarge > 1 Then Exit Sub」は、セル選択した範囲が複数セルの場合は、スピンボタン作成処理を中止させています。これは、選択した範囲のどこにスピンボタンを置いたら良いのか判断できないためです。

ここで使っている「CountLarge」はExcel2007以降でしか使えないプロパティです。ワークシートの左上角をクリックし「ワークシート全体」を選択してしまうと、Countプロパティ（Long型）の上限を超えてしまうためエラーが発生してしまいます。そのため今回はCountLargeプロパティを使用しました。
なお「On Error Resume Next」でエラースルーをさせた後「If Target.Count > 1 Then Exit Sub」を使うと、うまくExit Subしてくれます。エラーはTrue扱いにしてくれるのは少し面白いです。

８１～８２行目「If (Target.Column = StartCol Or Target.Column = EndCol) And Target.Row >= DataRow And IsDate(Target.Value) Then」のIF文は「開始日・完了日エリア内であり、且つセル値が日付の場合」に８４行目のコードを実行するという意味になります。
前半「Target.Column = StartCol Or Target.Column = EndCol」が「開始日・完了日列」であることを表し、中盤「Target.Row >= DataRow」が、データ範囲であることを表しますので、合わせて「開始日・完了日エリア内」という意味になります。そして後半の「IsDate(Target.Value)」で、セル値が日付データか否かを確かめています。

８４行目「Call SpinOn(Target)」で、図５－１３を呼び出し、スピンボタンの作成をします。引数として、受け取ったTarget（選択したセル範囲）をそのまま渡します。

５－２－４．スピンボタン・コメントの作成

図５－１２の８４行目から呼び出される「スピンボタン・コメントを作成」するプロシージャが図５－１３です。引数として、選択した単一セル範囲（図５－１２の７９行目で絞り込まれているため）を受け取ります。

90. '==========　⇩(8)　スピンボタン・コメントの作成　============
91. Private Sub SpinOn(Target As Range)
93. 　With ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _
94. 　　　　Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)
96. 　　.Name = SBname
97. 　　.Object.Max = 1
98. 　　.Object.Min = -1
99. 　　.Object.Value = 0
100. 　End With
102. 　With Target
103. 　　.AddComment
104. 　　.Comment.Visible = True
105. 　　.Comment.Shape.DrawingObject.Font.Size = 9
106. 　　.Comment.Shape.Width = 55
107. 　　.Comment.Shape.Height = 15
108. 　　.Comment.Shape.Top = Target.Top - .Comment.Shape.Height
109. 　End With
111. 　Call CommentUpdate(Target)
113. End Sub

図５－１３

９３～９４行目は、選択セル上にスピンボタンを作成しています。
９３行目「With ActiveSheet.OLEObjects.Add(ClassType:="Forms.SpinButton.1", _」は、ActiveXコントロールのスピンボタンを作成しています。作成するコントロールの種類は、図５－１４のようにClassType引数に文字列として設定します。

ActiveXｺﾝﾄﾛｰﾙ	ClassType
コマントボタン	Forms.CommandButton.1
コンボボックス	Forms.ComboBox.1
チェックボックス	Forms.CheckBox.1
リストボックス	Forms.ListBox.1
テキストボックス	Forms.TextBox.1
スクロールバー	Forms.ScrollBar.1
スピンボタン	Forms.SpinButton.1
オプションボタン	Forms.OptionButton.1
ラベル	Forms.Label.1
イメージ	Forms.Image.1
トグルボタン	Forms.ToggleButton.1

図５－１４

９４行目「Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)」は、作成するスピンボタンの位置・サイズを決めています。今回は選択セル一杯にスピンボタンを表示する設定にしています。

９６～９９行目は、作成したスピンボタンのプロパティ設定をしています。
９６行目「.Name = SBname」では、スピンボタンのオブジェクト名として、図５－６の２９行目で定数宣言済みの「SBname値（今回は "DateSpin" ）」を設定します。

９７行目「.Object.Max = 1」はスピンボタンの最大値を１に、９８行目「.Object.Min = -1」は最小値を－１に、９９行目「.Object.Value = 0」は初期のスピンボタンの値をゼロに設定しています。
この設定により、スピンボタンをクリックした時の移動量は「右側クリック＝＋１」「左側クリック＝－１」となり、且つ操作後の処理が完了したらスピンボタンのValue値をゼロに戻します（図５－１８の１４２行目）ので、「クリックするたびに１日ずつ移動」するスピンボタンとなります。
（なおMax・Min値に、例えば「+100・-100」を設定しても同じ動作になります。ボタンを連打しても動作ミスが起こる事は無さそうですが、誤解を与えないためにも「+1」「-1」の設定にすべきと思います。）

１０２～１０９行目は、スピンボタンを作成したセル（Target）にコメントを作成します。これは、セル全体がスピンボタンに覆われてしまうため値が確認できず（数式バーでは確認できますが）、その代わりとしてコメントにセル値（日付）を表示することにしました。
１０３行目「.AddComment」で、コメントを作成し、１０４行目「.Comment.Visible = True」でコメントを常時表示にします。（１０４行目を実行しない場合は、マウスをセルの上に合わせた時だけコメントが表示されるようになります。）

ここまででコメントは表示されるのですが、標準状態だと図５－１５の左側のようになります。今回システムでは、コメントに表示する内容は「スピンボタンで隠されているセルの日付値」ですので、これほど広い面積のコメントは必要ありません。
と言って、日付値に合わせて「コメントの高さと幅を調整」するだけでは、隣のセル（図５－１５で言えば、D5セル）にコメントが被ってしまい、「D5セルをクリックしようとすると、コメントをクリック」してしまう可能性があります。

図５－１５

そこで、コメント高さ・幅だけでなく、上下方向の位置も含めて調整するのが１０５～１０８行目のプロパティ設定です。
１０５行目「.Comment.Shape.DrawingObject.Font.Size = 9」は、まずコメントのフォントサイズを設定しています。私のPCだとサイズの既定値は9ポイントの様ですが、バージョンなどによって変わる可能性もあるので、念の為設定しています。
なお１０５行目のコードを手入力していく際に「DrawingObject」は候補として表示されません。DrawingObjectは古いオブジェクトのためだと思われますが、もしかしたら近い将来使えなくなる可能性も考えられますので注意が必要と思います。
（DrawingObject以外を使ったフォント設定が分からなかったため、仕方なくDrawingObjectを使いました。）

１０６行目「.Comment.Shape.Width = 55」ではコメントの幅を、１０７行目「.Comment.Shape.Height = 15」ではコメントの高さを設定しています。このサイズは、Try＆Errorで決めました。
１０８行目「.Comment.Shape.Top = Target.Top - .Comment.Shape.Height」は、図５－１５の右側のように「セルTop＝コメントの底辺」になるようにしています。

コメントの設定が完了したら、１１１行目「Call CommentUpdate(Target)」で図５－１７を呼び出し、セルの値をコメントの文字として書き出します。

５－２－５．スピンボタン・コメントの削除

図５－８の４９行目、図５－１２の７７行目から呼び出される「スピンボタン・コメントを削除」するプロシージャが図５－１６です。

115. '==========　⇩(9)　スピンボタン・コメントの削除　============
116. Private Sub SpinOff()
118. 　On Error Resume Next
119. 　　With ActiveSheet.OLEObjects(SBname)
120. 　　　.TopLeftCell.ClearComments
121. 　　　.Delete
122. 　　End With
123. 　On Error GoTo 0
125. End Sub

図５－１６

スピンボタン・コメントが存在しない状態でも、このプロシージャが呼び出される可能性があるので、１１８行目「On Error Resume Next」でエラーが発生してもスルーするようにしています。
削除する対象は「スピンボタン」であり「スピンボタンの下に隠れたセル」ですので、１１９行目「With ActiveSheet.OLEObjects(SBname)」で「スピンボタン オブジェクト」に対して処理をしていきます。

１２０行目「.TopLeftCell.ClearComments」では、スピンボタンの下にあるセルのコメントを削除しています。
１２１行目「.Delete」では、スピンボタンそのものを削除しています。
なお、この１２０・１２１行目を前後逆にするとエラーが発生します。先にスピンボタンを削除してしまうと、コメントのあるセル位置が特定できなくなってしまうためです。

５－２－６．コメントへセル値を書込み

図５－１３の１１１行目、図５－１８の１４７行目から呼び出される「コメントにセル値を書き込む」のが図５－１７です。
引数として、セル位置（Target）を受け取ります。

127. '==========　⇩(10)　コメントへセル値を書込み　============
128. Private Sub CommentUpdate(Target As Range)
129. 　Target.Comment.Text Text:=CStr(Target.Value)
130. End Sub

図５－１７

１２９行目「Target.Comment.Text Text:=CStr(Target.Value)」で、引数で得たセルのコメントに、そのセルの値を書き込みます。CStr関数でString型への変換が必要で、この変換を行わないとデータ型が合わないためにエラーが発生します。

５－２－７．スピンボタン操作に伴う処理

図５－３の１３行目、図５－５の１８行目から呼び出される「スピンボタン操作により、セル値・コメント値を変更」するプロシージャが図５－１８です。引数として、操作されたスピンボタンがあるワークシートを受け取ります。

132. '==========　⇩(11)　スピンボタン操作に伴う処理　============
133. Public Sub Spin_Change(S As Worksheet)
134. 　Static EventOff As Boolean
136. 　If EventOff = True Then Exit Sub
138. 　With S.OLEObjects(SBname)
139. 　　Selection.Value = Selection.Value + .Object.Value
141. 　　EventOff = True
142. 　　　.Object.Value = 0
143. 　　EventOff = False
145. 　End With
147. 　Call CommentUpdate(Selection)
149. End Sub

図５－１８

１３４行目「Static EventOff As Boolean」の変数EventOffは、再帰呼び出しによる実行をスルーさせるためのフラグ変数です。Staticとして宣言することで、このプロシージャを再帰呼び出しされた時にも値を保持することが出来ます。
そのStatic変数を使い、１３６行目「If EventOff = True Then Exit Sub」で、プロシージャが再帰呼び出しされたときに、重複して以下のコード実行されないようにしています。そのON-OFFは１４１行目・１４３行目で行っています。

１３８～１４５行目では、変更されたスピンボタンの値を使ってセル値の処理をし、またスピンボタンの位置を中立に戻しています。まず１３８行目「With S.OLEObjects(SBname)」で、「スピンボタン オブジェクト」に対して処理を行っていきます。

１３９行目「Selection.Value = Selection.Value + .Object.Value」は、操作したスピンボタンの下にあるセル値（日付）に対し、スピンボタン値（＋１ または －１）を加えています。
その後１４２行目「.Object.Value = 0」でスピンボタン値を中立（＝ゼロ）に戻しています。この１４２行目の実行により、再びスピンボタンのChangeイベントが発生してしまいますので、１４１行目「EventOff = True」でフラグを立て、再帰呼び出し先の１３９行目「If EventOff = True Then Exit Sub」で、コード実行をスルーさせています。

最後に１４７行目「Call CommentUpdate(Selection)」で、図５－１７を呼び出し、変更されたセル値をコメントに書き出しています。

６．最後に

今回のスピンボタンはActiveXコントロールを使用しました。「よりみち」でも説明したオブジェクト名の異常が発生した際「フォームコントロール」を使ったらどうだろう　と思いました。しかしフォームコントロールのスピンボタンをセル上に貼り付けてみると、ボタンの向きが左右方向に向いてくれなかったため、オブジェクト異常解析はそこでストップしてしまいました。
以前の項「データの重みを考慮したComboBox入力補助」で使用したのはフォームコントロールのコンボボックスでしたが、その時には今回の様な異常は無かったと思いますので、ワークシート上に配置するコントロールとしてはフォームコントロールの方が相性が良いのかもしれません。

今回、操作する対象を特定する方法として「Selection」とか「Activesheet」とかを使っていますが、ちょっと指定が曖昧なような気もしています。もう少し適切な指定方法がある様にも思いますが、とりあえず正常に動いているのでこのままとしました。もしおかしな動きをするようでしたら、ここも疑ってみて下さい。


工程表の日付をスピンボタンで操作（it-069.xlsm）

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