2020/11/26

備品の予約・貸出・記録ができる貸出管理表




１．背景

課や係などの小さなグループ内で「USBやSDカードなどの備品をちょっと借りる」ということは多いと思います。備品とは言っても共用のものなので、庶務係などで管理するために貸出ノートなどを使っているかもしれません。
しかし借りに行っても誰かが先に借りていることもあるので、本当に在庫しているか知りたかったり、また備品の最適量を知りたい場合には、現在と過去の貸出記録が必要になります。
そのような状況で、借りたい人にとっては「備品の予約」ができ、管理をしたい人にとっては「チェックしながら貸出」ができ、その借用状況の記録もできる「貸出管理表」を紹介します。

なお、今回は「現時点で空いている備品を予約」するシステムですが、もう少し計画的に予約が可能な「先行予約可能な備品予約・貸出システム」も参考にして下さい。

２．システム概要

備品を借りたい、と考える時点から備品が返ってきて保管するまでの流れを図２－１のように考えました。また、システム（今回はExcelファイル）は、使う人が操作可能な共有フォルダーに存在するものと仮定します。
備品管理の流れ
図２－１

備品を借りたい人はシステムに記入①した後、庶務係のところに行きます。庶務係は記入してある内容をチェック②して備品を貸し出し④⑤ます。借用者は使い終わったら備品を庶務係に返却⑥をしに行きます。庶務係は備品を確認⑦し、棚などに保管⑨します。
この流れの中の①③⑧の時点で、システムへの入力を行います。

システムの外観としては、図２－２のようになります。
システムの貸出管理表外観
図２－２

この表は次のことを表しています。
　・USB（管理番号102）は北村氏が11/16から11/18まで借りる予約をしている。（右側日程表の〇印）
　・SD（管理番号202）は熊谷氏が11/15から11/19まで予約し、11/15に貸出を実施している。（右側日程表の●印）
　・その他の備品は庶務係に保管されており、使うことが可能。（右側日程表では無印）

以下で、備品の予約から借用、返却までの手順を説明します。なお説明文の中の①②等は図２－１の中の①②等を示し、(1)(2)等は、各図中の手順・表示順になります。

まず備品を借りたい人が備品の予約①をするために、図２－３のように「備品名称の部分をダブルクリック(1)」します。すると「入力ダイアログが表示」されるので「必要事項を入力後OKボタンをクリック(2)」します。
備品の借用予約
図２－３

すると図２－４のように備品に対応したセルに情報が記入(3)され、右側日程表に「備品予約中」の意味である〇印(4)が付きます。
借用予約情報のセル上の表示
図２－４

借用希望者は庶務係のところ備品を借りに行きます。庶務係はその予約内容を確認②し、図２－５の「予約行のチェック欄をダブルクリック(5)」します。するとパスワードのダイアログが表示されますので、承認用のパスワードを入力しOKボタンをクリック(6)します。
備品貸出時のチェック
図２－５

すると、図２－６のように貸出日のセルに承認した日付が入力され(7)、庶務係は備品を借用希望者に貸し出します④。
なお右側の日程表は、〇印から●印(8)に変わります（「備品貸出中」であることを意味する）。
貸出中のセル上の表示
図２－６

備品借用者が備品を使い終わり庶務係へ返却に来る⑥と、庶務係は備品をチェックし、図２－７のように再びチェック欄をダブルクリック(9)します。パスワードを求められますので承認用パスワード（サンプルファイルでは「123」）を入力しOKボタンをクリック(10)します。
備品返却時のチェック
図２－７

すると図２－８のように予約データは消去(11)され、また右側日程表の●印も消えます(12)。
備品返却後は空白セルに変更
図２－８

借用情報は、ただ消去されるのではなく、図２－９のようにデータシート（図２－９ではSheet2）に移動(13)されています。
なお、データシートは通常の状態では非表示になっています。
借用情報はデータシートに移動
図２－９

操作するシート（サンプルファイルではSheet1）は保護されているため、日付等の記入はダブルクリックにより表示されるダイアログのみで可能です。ですので「備品が必要無くなった」「予定の日付が変わった」などのデータ修正必要時には、庶務係などの管理者が修正することとしました。（借用者が自分の都合で、勝手にデータ変更しても困るので）
そこで図２－１０のように「ロック設置解除セル（図２－１０ではＩ３セル）」をダブルクリック(14)しパスワード（サンプルファイルでは「1234」）を入力(15)することで、
　・シート保護の解除　・・・データの修正が可能
　・データシート（過去の借用情報の保存先）の再表示 　・・・データ解析等に活用
を行います。なお、再度ダブルクリックをすることで「シート保護＋データシートの非表示」になります。
シートの保護と解除
図２－１０

３．プログラムの流れ

プログラムとしては、シート上をダブルクリックするところから始まります。図３－１のように、ダブルクリックした場所を特定（A、B、C）し、それ以降の処理を分岐しています。
プログラムの流れ
図３－１

Ａ：
備品名、または管理番号のセル範囲をダブルクリックした時は、借用予約情報を入力するダイアログ（UserForm2）を表示し、入力した情報をセルに書き込みます。

Ｂ：
チェックのセル範囲をダブルクリックした時は、パスワード入力ダイアログ（UserForm1）を表示したあと、現在どの工程か（予約中の状態か、貸出中の状態か）を貸出日に値が入っているか否かで判断します。
予約中の状態ならば「備品の貸出工程（図２－１の③）」と判断し、貸出日に今日の日付を入力します。
貸出中の状態ならば「備品の返却工程（図２－１の⑧」と判断し、返却日に今日の日付を入力します。その後、借用情報をデータシートにコピーし、貸出表のデータを消去します。（データをデータシートに移動したことになる）

Ｃ：
シートロック（シート保護）のセル範囲をダブルクリックした時は、パスワード入力ダイアログ（UserForm1）を表示したあと、現時点でシート保護が掛かっているかを調べます。
シート保護が掛かっている状態ならば、シート保護を解除し、非表示のデータシート（Sheet2）を表示させます。
シート保護が掛かっていない状態ならば、シート保護を設定し、データシート（Sheet2）を非表示にします。

４．管理用ワークシート（今回はSheet1）

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

４－１－１．レイアウト

以下の説明は「サンプルファイル」を元に行います。
ワークシート上には、Ｂ列・Ｃ列に備品名称と管理番号を記載します。備品の増減で行を足したり削除したりしてＯＫです。
ただし行を増減した場合は、シートモジュール宣言部の定数変更が必要です。
（空白行を入れる場合は、その行だけはダブルクリックに反応しないような工夫が必要ですので注意して下さい。）
Ｄ列からＨ列までは借用情報が記載されますので空白のままにしておきます。
Ｉ列は備品管理者がダブルクリックする列です。Ｉ列に書かれた値は、どこにも使われませんので何か書いても問題ありません。
Ｂ列・Ｃ列・Ｉ列が薄緑色なのは「クリックするセル」を強調するためです。
数式をセルへ埋め込み
図４－１

４－１－２．日付の数式数式

Ｊ列より右側のセルに貼り付けた数式について説明します。

まず１行目・２行目は日付系統です。
Ｊ１セルは「=TODAY( )」で、今日の日付となります。
Ｋ１セルは「=J1 - WEEKDAY(J1) + 1」で、「今週の日曜日の日付」を計算しています。
「WEEKDAY 関数」は「引数の日付の曜日を数値として返す」もので、第二引数を省略していますので「日曜=1、月曜=2，･･･、土曜=7」という値が得られます。
ですので、例えば「今日が水曜日」であれば「今日の日付 ー　４ ＋ １」で、「水曜より３日前の日曜」という計算になります。
１行目は説明のため文字を薄く表示させていますが、ユーザーに見える必要は無いので「文字を白く」したり「１行目を非表示」にすると良いと思います。

２行目に移ります。Ｊ２セルは「=K1」ですので、Ｋ１セルで計算した「今週の日曜日の日付」が入ります。
その隣のＫ２セルは「=j2 + 1」ですので、「次の日」となります。それ以降は、Ｋ２セルが「=K2 + 1」・・・・となり、連日の日付が入っていきます。
Ｊ１セルとＫ１セルの式をまとめて、Ｊ２セルに「=TODAY( ) - WEEKDAY(TODAY( )) + 1」としてももちろん良いです。しかし、「Ｊ１セルに様々な曜日の日付を入れてみる」ことで数式のチェック（今日が日曜の場合に、表示範囲に本日が含まれるのか等）が出来ますし、また「スクロールバーで日付を１週間単位で動かす」等を考えているのであれば楽に改修できます。
仕様が不確定、または数式が苦手であるのであれば、分割して数式を作ることをお勧めします。
３行目の「月・火・水・・・」は手書きで記入しています。

４－１－３．日程表の記号の数式

まず、記号をどのように表すかを図４－２で考えます。
日程表の記号の意味
図４－２

今回のシステムは、備品の「予約」→「貸出」→「返却」という順番を繰り返します。その工程ごとに、記入される値を整理したのが図４－２です。工程は上から下へと進み、また一番上に戻ります。

日程表に表す記号ですが、備品を貸し出していない状態は「マーク無し」とします。
その後の「予約＝〇印」「貸出＝●印」は、工程が進むたびに「マークの色を濃く」しているつもりでマーク種類を選択しています。
その「色をだんだん濃く」している理由は、日程表を見た人が「予約または貸出中の備品を自分の方で使いたい」と思ったとき、予約の時点（〇印）では「丸の色が薄い」ため、予約者と交渉すれば譲ってもらえる可能性がありますが、実際に貸出を行ってしまった後では譲ってもらえる可能性が低くなる（借用者の所有度合いが高まる＝丸の色が濃くなる）ことを表しているつもりです。
今回は無印も含めて３種類でしたが、もっと多種類の場合にはマークを選ぶのが大変になりますが、行き当たりばったりのマークを選ぶよりは意味のあるマーク（例：△→□→☆→〇→◎→●　段々と角が多くなり最後は丸くなる）にした方が、使う側としては分かり易いと思います。

さて、今回は図４－２の各状態を「無印・〇印・●印」で表すことにしますが、それを実現するには「マクロでセルに書き込む」方法と「各セルに数式を記述」する方法の２通りが考えられます。
どちらの方法も可能ですが、今回の表示条件は単純で充分数式で表現できるため、数式を使うことにしました。

図４－１のＪ４～Ｗ９セル範囲には、図４－３の①の様な数式が記載されています。（代表としてＪ４セルの数式で表しています）

マークの数式を分割
図４－３

この数式はIF文が重なっており、分解すると②③のようになります。
つまり図４－４のように１つ目の条件式②が成立（True）したら「●印」。成立しなかった（False）ら２つ目の条件式③で判断し、２つ目の条件式が成立（True）したら「〇印」、成立しなかった（False）ら「" " (無印)」、という意味になります。
マークの分岐
図４－４

まず、今回は図４－４のように「●印　→　〇印　→　無印」の順にマークを出すようにIF文を組み立てています。実はこの順番には意味があります。
図４－２の表には「借用者・借用予定日・返却予定日・貸出日・返却日」の５つの要素があり、その組合せで「マーク無し・〇印・●印」が一意で決まりますので、もちろん「どの順番でもIF文は作れる」ことになります。
「●印」「〇印」「無印」の順番は全部で６通りありますので、それを並べてみたのが図４－５です。
どの順序でIF文を組み立てるか
図４－５

図４－５の(1)(2)が最初に「マーク無し」を処理するパターン、(3))(4)が最初に「〇印」を処理するパターン、(5))(6)が最初に「●印」を処理するパターンです。
IF文を考える時には、まず各行を見分ける「存在の確認」が必要です（存在の確認は、私が勝手に名付けたものです）。たとえば(1)で言えば「無印・〇印・●印の３行」の中から、まず「無印」の行を見極めるには「①借用者セルが空白」という条件が必要です（①でなくても②でも③でもOKです）。
「無印」の処理が終わったら、残りの中から「〇印」の行を見極め、最後に残ったのが「●印」という訳です。

簡単なIF文ですとここまでなのですが、今回は日程表の日付も考慮しなくてはいけませんので「期間の確認」という条件も必要です。つまりマークがあるもの（「〇印」および「●印」）については、図４－６のように対象日が「開始日・完了日の中に入っているか」を式で表す必要があります。
期間の確認
図４－６

これらを整理したのが図４－５で、各順番の表の下に「その順番で立てた数式」を記述しました。なお「J$2」が「対象日」に相当します。
この図４－５だとゴチャゴチャして分かり難いので、まとめたのが図４－７です。
IF文の順序による式の複雑さの比較
図４－７

図４－７から(6)の「●印→〇印→無印」の順番が、IF文の数も少なく条件式も少ないことが分かります。
IF文が少ないのは「最後が無印の処理」であり、その理由は「最後のIF文のFalse条件（最後尾）に指定する文字列が無印」であり無駄が無いためと分かります。また条件式が少なくなる条件としては、最初のIF文の条件（最初に選ぶ行）が出来るだけ「簡潔な条件で選択できる処理」が有利となります。
ですので、公式ではありませんが「IF文を作る時のコツ」としては、以下の様になります。
「最後のFalseで空白になるようにする」　「最初はできるだけ少ない条件式で選べる項目にする」

これに合致するのが、(6)である今回の式
「=IF(AND($G4<>"",$G4<=J$2,$F4>=J$2),"●" ,IF(AND($E4<=J$2,$F4>=J$2),"〇",“ "))」
になります。数式は単純で短い方が、バグも少なくなりますし、タイプする手間も少なくなります。
難しそうに思えるかもしれませんが、今回の例で言えば「無印→〇印→●印」と工程が進むにつれて「記入項目が増えていく」ので、「最後に付け加えられた項目（貸出日）に値があるのが、最後の項目の特徴」と言えます。

この式を日程表のセル範囲（Ｊ４セル～Ｗ９セル）の左上角のセル（Ｊ４セル）に記入後、コピーし日程表セル範囲全体に数式貼付けをします。なお、貼付け後に数式を修正しないために、各参照セル位置（E4、F4、G4、J2）の動かしたくない行・列側に「$」を付けます。

４－２．ワークシートモジュール

４－２－１．定数設定

図４－８のように、シート内で使用する定数を宣言部で宣言します。
  1. '==========　⇩(1)　シートレベル定数設定　====================
  2. Const PW1 As String = "123"
  3. Const PW2 As String = "1234"
  5. Const copySCol As Long = 2
  6. Const copyECol As Long = 8
  7. Const listSCol As Long = 2
  8. Const listECol As Long = 3
  9. Const reserveSCol As Long = 4
  10. Const reserveECol As Long = 6
  11. Const rentCol As Long = 7
  12. Const returnCol As Long = 8
  13. Const checkCol As Long = 9
  15. Const listSRow As Long = 4
  16. Const listERow As Long = 9
  17. Const lockArea As String = "i3"
  19. Const dataSheet As String = "sheet2"
図４－８

２～３行目はパスワード値の設定です。
「PW1」は「備品貸出・返却」用のパスワード、「PW2」は「シート保護・解除」用のパスワードです。「シート保護・解除」が出来るという事は「データをどうにでも出来てしまう」ため、権限を分けました。 今回はパスワードを入力するダイアログ（UserForm1）のTextboxを「Me.TextBox1.IMEMode = fmIMEModeDisable」（図６－２の１３２行目）と設定しましたので、「半角英数字」のみが可能です。

５～１７行目は、入力セル範囲の行・列を特定する定数です。
５～１３行目は列位置、１５～１６行目は行位置、１７行目はセル範囲を示すString型です。その位置を図４－９に表しました。なお定数のワード中の「S」や「E」は「Start」「End」のつもりで、列の開始・終了のような意味に使っています。
また、定数「lockArea」は、シート保護・解除を行うダブルクリック位置です。
入力セル範囲の行・列を示す定数
図４－９

この定数は、後述のコードを短くする目的もありますが、「項目の入れ替えや増減をしても、定数値だけ変更すればＯＫになるように」と考えたのですが、完全とはいきませんでした。
たとえば項目は「借用予定・返却予定・貸出日・返却日」と並んでいますが、「借用予定・貸出日・返却予定・返却日」と並べてしまうと、それだけで「ユーザー入力値」が崩れてしまいます。
あれこれ考えましたが、残念ながら良い手が思いつかず、中途半端な定数となってしまいました。申し訳ありませんが、改造する場合は全コードの見直しが必要と思います。
なお、少なくとも備品の数は大量にあるでしょうから、行挿入などをした際には「listERow」だけは正しい値に設定をして下さい。

１９行目は、データシート（過去の借用記録）の「オブジェクト名（.CodeName）」をString型で指定します。
「シート名（.Name）」では無いことに注意が必要です。シート名はユーザー側（とは言っても、シート保護・解除が出来るユーザーですが）からExcel操作の１つとして変更出来てしまうので、今回は「シート削除やVBE側から変更」でもしない限り変わらないオブジェクト名を使用しました。

４－２－２．ダブルクリックイベント

ワークシート上をダブルクリックすると、図４－１０のイベントプロシージャが起動します。
  1. '==========　⇩(2)　ダブルクリックイベント　====================
  2. Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
  3. 　Dim listRange As Range　　　　 'ユーザーが備品予約をする際の「備品・管理番号範囲」のエリア
  4. 　Dim checkRange As Range　　　 '庶務係などがチェックする「チェック範囲」のエリア
  5. 　Dim lockRange As Range　　　　 'シート保護・解除をする為のエリア
  6. 　Dim Sh As Worksheet　　　　　　 'データシート
  8. 　Set listRange = Range(Cells(listSRow, listSCol), Cells(listERow, listECol))
  9. 　Set checkRange = Range(Cells(listSRow, checkCol), Cells(listERow, checkCol))
  10. 　Set lockRange = Range(lockArea)
  11. 　Set Sh = Evaluate(dataSheet & "!a1").Parent
  13. 　If Not Intersect(target, listRange) Is Nothing Then 　　　　　　 '備品・管理番号範囲の場合
  14. 　　Cancel = True
  15. 　　Call userInput(target)
  16. 　ElseIf Not Intersect(target, checkRange) Is Nothing Then 　　　　 'チェック範囲の場合
  17. 　　Cancel = True
  18. 　　Call adminCheck(target, Sh)
  19. 　ElseIf Not Intersect(target, lockRange) Is Nothing Then 　　　　　 'シート保護・解除範囲の場合
  20. 　　Cancel = True
  21. 　　Call adminLock(Sh)
  22. 　End If
  24. 　Set listRange = Nothing
  25. 　Set checkRange = Nothing
  26. 　Set lockRange = Nothing
  27. 　Set Sh = Nothing
  28. End Sub
図４－１０

２７～３０行目は、「予約用のダブルクリック範囲」「チェック用のダブルクリック範囲」「シート保護・解除用のダブルクリック範囲」「データシートオブジェクト」の設定です。図４－８の定数を使って範囲設定します。

２７～２９行目のRangeオブジェクトは、定数を使ってセル範囲を決めているだけなので理解できると思いますので、３０行目の「データシートの設定」について詳しく説明をします。
データシートのオブジェクト名は図４－８の１９行目で定数設定していますが、これは「String型」でありオブジェクトではありません。ですのでオブジェクトを得るために、まずEvaluateメソッドを使っています。Evaluateメソッドの引数に「A1形式の参照」を表す文字列を指定すると、その参照が得られます。ここでは「データシートのＡ１セル」の文字列を渡しています。
こうすることで「データシートのＡ１セル」のオブジェクトが得られ、それに対して「.Parent」と「親のオブジェクト」を指していますので「データシート」そのもののオブジェクトが得られる　というものです。

３２～４１行目は、ダブルクリックしたエリアを判別して動作を分岐しています。
If文内で使っている「Intersect」メソッドは、「Intersect（Ａ,Ｂ）」とすることで「ＡとＢの重なったセル範囲を返す」ことを利用し、「Not Intersect（Ａ,Ｂ） Is Nothing」で「「ＡとＢが重なっている範囲は無い」の反対」→「ＡとＢが重なっている場合」という意味になります。

まず「備品・管理番号範囲」の場合は、３３～３４行目を実行します。３３行目でダブルクリック機能（編集機能）をキャンセルし、３４行目で「userInput（図４－１２）」を呼び出します。
なお「ダブルクリック機能をキャンセル」するのは、編集モードに入ってしまうのを中断するためです。編集モードに入ろうとしても、今回はシート保護をしているために編集モードにはなりませんが、その代わり図４－１１のようなエラー表示が出てしまいます。「ダブルクリック機能をキャンセル」するのは、そのエラー表示を出さないためです。

次に「チェック範囲」の場合は、３６～３７行目を実行します。３６行目でダブルクリック機能（編集機能）をキャンセルし、３７行目で「adminCheck（図４－１３）」を呼び出します。
最後に「シート保護・解除範囲」の場合は、３９～４０行目を実行します。３９行目でダブルクリック機能（編集機能）をキャンセルし、４０行目で「adminLock（図４－１４）」を呼び出します。

呼び出すプロシージャ「userInput」と「adminCheck」には、引数として２１行目で得られるTargetを渡しています。
これは「どの備品の処理をしているのか」が必要なためです。

上記３つの範囲「備品・管理番号範囲」「チェック範囲」「シート保護・解除範囲」以外でダブルクリックをした時は、「Cancel=True でダブルクリック機能をキャンセル」していませんので、通常ですと「編集モード」になります。しかし今回は「シート保護」を掛けた状態で使用しますので、図４－１１のようなエラーが表示され編集モードにはなりません。
シート保護上でのダブルクリックした時のエラー表示
図４－１１

４３～４６行目は、２７～３０行目で設定したオブジェクトの参照解除です。
実際には、プロシージャ内で参照設定したオブジェクトですので、４７行目の「End Sub」で自動的に解除・破棄されます。しかし明示的に「自分で作ったものは自分の責任で消す」ことが大切と考え、参照解除をしています。

４－２－３．備品予約

図４－１０の３４行目から呼び出される「userInput」プロシージャが図４－１２です。引数として、ダブルクリックしたセル範囲Targetを受け取ります。
  1. '==========　⇩(3)　備品予約　====================
  2. Private Sub userInput(target As Range)
  3. 　Dim ans As Variant　　　　　　　　　 '予約用のダイアログ(UserForm2)から受け取る値
  4. 　Dim reserveRange As Range　　　　 '予約情報を貼り付けるセル範囲
  6. 　Set reserveRange = Range(Cells(target.Row, reserveSCol), Cells(target.Row, reserveECol))
  8. 　If Not Application.WorksheetFunction .CountA(reserveRange) = 0 Then
  9. 　　MsgBox "既に予約済みか借用中です"
  10. 　Else
  11. 　　ans = UserForm2.UF2start
  12. 　　If IsArray(ans) = True Then
  13. 　　　reserveRange = ans
  14. 　　End If
  15. 　End If
  17. 　Set reserveRange = Nothing
  18. End Sub
図４－１２

５３行目は「備品借用情報」の貼り付け範囲を、図４－８の定数と引数で渡されたダブルクリックしたセル範囲を使用して設定します。
５５行目のIf文はワークシート関数の「CountA」を使用し、５３行目で設定した情報貼付け範囲内の値が入っているセル数を調べています。その数がゼロで無い（＝貼付け範囲内に値が入っている　→予約されている）時に、５６行目のコメントを表示します。

ゼロの場合（＝貼付け範囲には値が入っていない　→予約されていない）は、５８～６１行目を実行します。
５８行目でUserForm2を呼び出し、備品予約情報を得ます。UserForm2は直接表示（.Show）するのでは無く、UserForm2の中にある「UF2start」関数を呼び出し、その戻り値として「備品予約情報が入った配列」が返ってきます。
但しUserForm2側でキャンセルボタンが押された（＝予約をやめた）時にはFalseが返ってきます。
返ってきた値（配列 または False）は、変数ansに代入されます。

５９行目は、その変数ansの型を「IsArray」で調べ、配列であれば６０行目で「貼り付け範囲reserveRange」に貼り付けます。
UserForm2側でキャンセル（＝予約をやめた）した時には、そのまま終了します。
最後にreserveRangeオブジェクトの参照解除をしています。

なお、借用情報をセルに貼り付ける際、書式を整える作業はしていません。後述する「貸出日」「返却日」も同様で、初期状態でしたら「2020/12/3」や「2020年12月3日」の表示になるかと思います。
日程表の方が２週間くらいの短い期間しか表示されないのであれば、「年」の単位まで表示する必要は無いと思います。
ですので「日付を貼り付けるセル範囲.NumberFormatLocal = "m/d"」などのコードを貼り付けるたびに実行するようにするか、または管理者が備品を追加した際に、あらかじめ日付部分の書式を手動で整えると良いと思います。

４－２－４．管理者チェック

図４－１０の３７行目から呼び出される「adminCheck」プロシージャが図４－１３です。引数として、ダブルクリックしたセル範囲Targetと、データシートのオブジェクトの２つを受け取ります。
  1. '==========　⇩(4)　管理者チェック　====================
  2. Private Sub adminCheck(target As Range, Sh As Worksheet)
  3. 　Dim reserveRange As Range　　　 '予約情報が記入してあるセル範囲
  4. 　Dim rentRange As Range　　　　　 '貸出日のセル範囲
  5. 　Dim returnRange As Range　　　　 '返却日のセル範囲
  6. 　Dim copyRange As Range　　　　　 'データシートにコピーをするセル範囲
  7. 　Dim delRange As Range　　　　　　 '借用情報を削除するセル範囲
  9. 　Set reserveRange = Range(Cells(target.Row, reserveSCol), Cells(target.Row, reserveECol))
  10. 　Set rentRange = Cells(target.Row, rentCol)
  11. 　Set returnRange = Cells(target.Row, returnCol)
  12. 　Set copyRange = Range(Cells(target.Row, copySCol), Cells(target.Row, copyECol))
  13. 　Set delRange = Range(Cells(target.Row, reserveSCol), Cells(target.Row, returnCol))
  15. 　If Application.WorksheetFunction .CountA(reserveRange) = 0 Then
  16. 　　MsgBox "この備品は予約されていません。"
  17. 　ElseIf UserForm1.UF1start(PW1) = True Then
  18. 　　If rentRange = "" Then
  19. 　　　rentRange = Date
  20. 　　Else
  21. 　　　returnRange = Date
  22. 　　　copyRange.Copy Destination:=pasteRange(Sh)
  23. 　　　delRange.ClearContents
  24. 　　End If
  25. 　End If
  27. 　Set reserveRange = Nothing
  28. 　Set rentRange = Nothing
  29. 　Set returnRange = Nothing
  30. 　Set copyRange = Nothing
  31. 　Set delRange = Nothing
  32. End Sub
図４－１３

７４～７８行目は、「予約情報が記入してあるセル範囲reserveRange」「貸出日のセル範囲rentRange」「返却日のセル範囲returnRange」「データシートにコピーをするセル範囲copyRange」「借用情報を削除するセル範囲delRange」の５つのRangeオブジェクトを設定しています。

８０～９０行目は、セルの状態によって処理を分岐しています。
まず８０行目では、ワークシート関数のCountAを使って「予約情報セル範囲reserveRange」に値が存在するか否かを調べ、無かった（＝０個）時には「予約されていない状態」と判断し、８１行目でコメントを出します。

「予約されている状態（＝予約情報セル範囲に値が存在する）」の時には、８２行目でUserForm1内のUF1start関数プロシージャを呼び出し、パスワード入力用のダイアログを表示させます。
「UF1start」関数プロシージャには、引数として「パスワード値」を渡しますが、８２行目で渡している値は定数PW1です。したがって図４－８の２行目で設定している値を入力した時に「パスワードが合致」と判断し、関数の戻り値としてTrueを返してきます。間違っていた時、またキャンセルをクリックした時にはFalseが返ります。

パスワードが合っていたときは、８３～８９行目を実行します。間違っていた時、キャンセルした時は何もせず終了します（UserForm1のUF1start関数内では、間違っていた時のみコメントを出しています）。

８３行目では、「貸出日のセル範囲rentRange」が空白セルか否かをチェックしています。空白の場合は「まだ備品貸し出しを行っていない予約段階」と判断されますので、８４行目で「今日の日付を貸出日に入力」します。
「貸出日のセル範囲rentRange」が空白セルで無いときは「貸出は終了しており返却の段階」と判断し、８６行目で「今日の日付を返却日に入力」します。

返却が完了したら、その備品は次の人のために「貸出可能な状態」にする必要がありますので、返却済みの借用情報をデータシートに保存した後、消去します。
データシートへの保存は、８７行目の「copyRange.Copy Destination:=pasteRange(Sh)」で行っています。「コピーするセル範囲範囲copyRange」をコピーし、「Destination:=」に指定した「貼り付け範囲pasteRange(Sh)」に貼り付けます。「pasteRange」は図４－１６のpasteRange関数プロシージャで、データシート（ここではSheet2）を渡すことで、そのデータシートの最終行＋１行目のセル範囲を戻してくれます。
データシートに貼付け後、８８行目で追加された借用情報（備品名と管理番号を除いたDelRange範囲）を消去します。

８９～９６行目は、不要になったRangeオブジェクトの参照を解除しています。

４－２－５．シート保護設定・解除

図４－１０の４０行目から呼び出される「adminLock」が図４－１４です。引数として「データシートのオブジェクト」を受け取ります。
  1. '==========　⇩(5)　シート保護設定・解除　====================
  2. Private Sub adminLock(Sh As Worksheet)
  4. 　If UserForm1.UF1start(PW2) = True Then　　 'パスワードが正しい時
  5. 　　If Me.ProtectContents = True Then　　　　 'シートが保護されている時
  6. 　　　Me.Unprotect
  7. 　　　Sh.Visible = xlSheetVisible
  8. 　　Else　　　　　　　　　　　　　　　　　　　　 'シートが保護されていない時
  9. 　　　Me.Protect userinterfaceonly:=True
  10. 　　　Sh.Visible = xlSheetVeryHidden
  11. 　　End If
  12. 　End If
  14. 　Set Sh = Nothing
  15. End Sub
図４－１４

１０１行目で、パスワードダイアログを表示させるUserForm1上のUF1start関数プロシージャを呼出し、パスワードが正しかった場合のみ１０２～１０８行目を実行します。ここでのパスワードは定数PW2と、図４－１３の８２行目のPW1とは異なるものを設定しています。一応貸出・返却時とは権限を分けているつもりです。

１０２行目では「シートが保護されているか否か」を確認しています。「ProtectContents」プロパティは、保護されている時にTrueになります。その「保護されている場合」は、１０３行目で保護を外す（Unprotect）のと同時に、１０４行目で「データシートを非表示から表示に変更」をしています。

シートが「保護されていない場合」は、１０６行目でシートを保護しています。この時に使用している「userinterfaceonly:=True」はProtectメソッドのパラメータの１つで、「ユーザー操作による変更は拒否するが、マクロからの変更は可能」となります。
今回マクロ側から予約情報や貸出日・返却日の書込み、およびそれら情報のデータシートへの移動をしていますが、それらの実行は許可されます。
また１０７行目では、データシートの「Visible」プロパティに「xlSheetVeryHidden」を設定して非表示にしています。「Visible」プロパティには、図４－１５の３つの値を設定することができ、今回は「xlSheetVeryHidden」を設定することでExcelの操作（シートのタブ上で右クリックし「再表示」）では再表示できない様にしています。一般ユーザーがデータ改ざんできないようにするためです。
定数　値内容
xlSheetHidden0シートを非表示。但しExcel操作で再表示が可能
xlSheetVeryHidden2シートを非表示。Excel操作では再表示が不可能
xlSheetVisible-1シートを表示
図４－１５

４－２－６．データ貼付け位置取得

借用済み情報をデータシートに保存するためには、そのデータシートのデータ範囲が必要です。直接的には、その最下行位置が必要です。その位置を計算するのが「pasteRange」関数プロシージャ（図４－１６）で、図４－１３の８７行目から呼び出されるものです。
データシートのオブジェクトを受け取り、「借用情報を貼り付けるセル範囲」を戻します。
  1. '==========　⇩(6)　データ貼付け位置取得　====================
  2. Private Function pasteRange(Sh As Worksheet) As Range
  3. 　Dim pasteRow As Long　　　 '現在の最終行位置
  5. 　pasteRow = Sh.Cells(1, 1).CurrentRegion.Rows.Count + 1
  7. 　Set pasteRange = Sh.Range(Sh.Cells(pasteRow, 1), Sh.Cells(pasteRow, copyECol - copySCol + 1))
  8. End Function
図４－１６

今回のデータシートは「１行目にタイトル」「２行目からデータ」「列方向はＡ列から隙間を空けずに」を前提としています。
もし隙間を空ける必要があったりする場合は、１１７行目・１１９行目のコードは見直して下さい。

１１７行目では、データシート（引数で受け取ったSh）のＡ１セルを基準とするアクティブセル領域をCurrentRegionプロパティで取得します。アクティブセル領域は空白行・空白列で区切られた範囲ですので、保存されているデータの最下行までの範囲が取得でき、その行位置を「.Rows.Count」で計算し、最後の「 + 1」で「新たにデータを記入すべき行位置」を割り出しています。

１１９行目では、列幅も含めて「新たにデータを記入すべきセル範囲」を計算し、pasteRange関数プロシージャの戻り値にしています。

５．データ保管用ワークシート（今回はSheet2）

返却が完了した借用済み情報は、データシート（今回はSheet2）に保管されます。
そのデータシートは図５－１の右側ように、１行目にタイトルを表示し、２行目からデータが並ぶようにしています。ですので入力用シート（今回Sheet1）のタイトル行をコピーし、データシートの１行目に貼り付ければOKです。
データシートの準備
図５－１

また、データシートが全くの空シートだとしても、２行目からデータが埋まっていきますが、それでは何のデータか分からなくなりますので、タイトルは有った方が良いと思います。
なお、データシート側にはVBAコードはありません。
また異なるシートをデータシートにする際は、新規ワークシートを挿入後、データシートの「オブジェクト名」を図４－８の１９行目に定数として登録する必要があります。

６．パスワード入力用ダイアログ（UserForm1）

６－１．フォームの設定

パスワードを入力するには、本来はInputBox関数やInputBoxメソッドで良いのですが、入力文字を隠す（文字を＊や●印で置き換える）のが大変なので、今回は自作のフォームで対応します。
（InputBox関数の文字を隠す方法は「TEK-TIPS.com の VBA Visual Basic for Applications (Microsoft) FAQ」等で紹介されています。APIを使う方法です。）
パスワード用フォームの各コントロールの配置
図６－１

図６－１のように、フォーム上にTextBoxを１つとCommandButtonを２つ配置しています。プロパティはマクロ側から設定します。
その他のLabelは事前に文字（今回は「パスワードを入力してください」）を設定しています。

６－２．フォームモジュール

６－２－１．宣言部および起動・表示の準備

フォームが起動する時の設定関係が図６－２になります。
  1. '==========　⇩(7)　フォームレベル変数宣言　====================
  2. Dim ans As Variant　　　 '入力文字列、およびどのボタンがクリックされたか
  4. '==========　⇩(8)　起動時のフォーム初期化　====================
  5. Private Sub UserForm_Initialize()
  6. 　Me.CommandButton1.Caption = "OK"
  7. 　Me.CommandButton1.Default = True
  8. 　Me.CommandButton2.Caption = "キャンセル"
  9. 　Me.CommandButton2.Cancel = True
  11. 　Me.TextBox1.PasswordChar = "＊"
  12. 　Me.TextBox1.IMEMode = fmIMEModeDisable
  13. End Sub
  15. '==========　⇩(9)　フォーム表示時の初期化　====================
  16. Private Sub UserForm_Activate()
  17. 　Me.TextBox1.Value = ""
  18. 　Me.TextBox1.SetFocus
  19. End Sub
図６－２

１２２行目のフォームレベル変数ansは、ユーザーがパスワードとして入力した値、およびボタン操作状況を保持します。変数ansに入る値はパスワード入力値の場合はString型となりますが、例えばキャンセルボタンはBoolean型のFalseとして値保持させますので、どちらも可能なVariant型で宣言しています。

１２５～１３３行目はInitializeイベントプロシージャで、初めてフォームが起動する際に実行されます。
１２６行目・１２８行目ではボタンに文字列を記入しています。
また「リターンキー押下 ＝ ＯＫボタンのクリック」とするために１２７行目で「.Default = True」とし、「ESCキー押下 ＝ キャンセルボタンのクリック」とするために１２９行目で「.Cancel = True」の設定を行っています。
この設定は、単純なダイアログの場合にマウス操作無しでダイアログ操作が出来るため、便利と思います。

１３１行目は、ユーザーがパスワードを入力する際、背後から覗かれてパスワードを盗まれる（ソーシャルエンジニアリングの内のショルダーハッキングに当たる）のを防止する対策の１つで、テキストボックスの文字を隠すものです。
一般的に良く使われるのは「*（アスタリスク）」ですが、Fontサイズが小さいと「何文字目まで入力したか分からなくなる」ので、全角のアスタリスクや「●（黒丸）」「〇（白丸）」も使い易いと思います。
また数値（例えば、1）を設定すると何を入力しても「1111」等となり、「一時的には守れる」事になるかもしれません。
いずれにしても、すぐに見破られてしまう事を覚悟し、定期的にパスワード値を変更する等をお勧めします。

１３２行目は「TextBoxにカーソルが来た時のIMEモードの設定」です。IMEModeプロパティには図６－３のような種類があります。
定数値内容
xlIMEModeNoControl0IME のモードを変更せず
xlIMEModeOn1IMEをオン
xlIMEModeOff2IMEをオフ (英語モードにする)
xlIMEModeDisable3IMEをオフにし、ユーザーはキー操作でオンに出来ない
xlIMEModeHiragana4全角ひらがな
xlIMEModeKatakana5全角カタカナ
xlIMEModeKatakanaHalf6半角カタカナ
xlIMEModeAlphaFull7全角英数字
xlIMEModeAlpha8半角英数字
図６－３

パスワードは通常半角ですから、一般的には値が２の「xlIMEModeOff」を設定すれば良さそうです。しかし、図６－４のようにフォーム表示中に手動でIMEモードを変更されてしまうと、次回再表示時にはその変更したIMEモードになってしまいます。
xlIMEModeOffの設定時はIMEモードを手動変更されると再表示時に影響される
図６－４

なお「マクロでIMEModeを設定するから手動切替が反映されてしまのでは？」とも思いましたが、フォーム設計時にTextBoxのプロパティに書き込んでも同じでした。
また、ダイアログを右上×印で閉じた時は、次回はInitializeイベントを通りますので、Initializeで設定したIMEモードが得られます。

この不具合を防ぐためには図６－５のように、２つの方法があります。
　方法１：Activeイベント内にIMEMode設定をする。（表示初期のみ固定。その後のIME変更はユーザーの自由）
　方法２：InitializeイベントでxlIMEModeDisable設定することで、ユーザーは全く変更できなくする。
IMEモードを固定する方法
図６－５

今回は「パスワードは半角」と決めつけて、図６－５の方法２を採用しました。

なお図６－３には記載しませんでしたが、「値9、10」を設定すると「ハングル文字全角、半角」になるそうです。しかし、日本語IMEではなりません。もしかしたら日本語とハングル語は、何かのシステムファイルを共通で使っているのかもしれません。

１３６～１３９行目は、Activeイベントです。フォームを起動する際には、図６－６の順番でイベントが発生します。今回は図６－７の１４２行目で「.Show」で起動しますので、初回起動時も、再表示時もActiveイベントが発生します。
フォーム起動・終了時の発生イベントの順番
図６－６

１３７行目ではTextBoxの値に「" "(長さゼロの文字列)」を設定しています。これは前回表示時に入力したパスワードが残っている可能性がありますので、消去をしています。これを実行しないと、最悪「権限が無くても、そのままOKキーのクリックで開錠」できてしまいます。
１３８行目は、ユーザーがすぐにTextBoxにパスワードを入力できるように、TextBoxにカーソルを移動しています。

６－２－２．シート側から呼び出されるプロシージャ

図４－１３の８２行目、図４－１４の１０１行目から呼び出されるのが図６－７のUF1start関数プロシージャです。
引数として「開錠できるパスワード値」を受取り、戻り値としては「成功すればTrue、失敗・キャンセルであればFalse」を返します。
  1. '==========　⇩(10)　シート側から呼び出されるプロシージャ　====================
  2. Function UF1start(PW As String) As Boolean
  3. 　Me.Show
  5. 　If ans = False Then 　　 'キャンセル時
  6. 　　UF1start = False
  7. 　ElseIf Not ans = PW Then
  8. 　　MsgBox "パスワードが違います"
  9. 　　UF1start = False
  10. 　Else
  11. 　　UF1start = True
  12. 　End If
  14. End Function
図６－７

１４２行目では「Me」、つまり自分である「UserForm1」を起動します。「Show」メソッドの引数には「vbModal」「vbModeless」の２つがあり、既定値はvbModalですので何も指定していない１４２行目はvbModal（フォームを閉じるまでワークシートは操作不可）になります。

ここでもしvbModeless（引数値でゼロ）を設定してしまうと、ダイアログ表示と同時に制御は次のコードに移ってしまいます。
つまり変数ansはEmptyの状態のまま１４４行目を実行しますので、Empty = Falseとなり「UF1start関数プロシージャとしてはFalseを戻す」ことになります。ですのでプログラム上は「キャンセルした、又はパスワードが間違った」事と認識され、ワークシート上は何も変化せず終了してしまいます。
ですので、１４２行目は必ず「vbModal（引数なし）」で実行しなければなりません。

１４４～１５１行目では、パスワード入力ダイアログが閉じた（Hideで隠された）のち、フォームレベル変数ansの値により処理を分岐させています。
変数ansには、ダイアログをどの様に終了させるかで、入る値が異なります。また、その値を調べてUF1start関数プロシージャの戻り値にしますが、それらをまとめたのが図６－８です。
　　　　　　　　　　　　　　　　　　　　　
ダイアログ終了の操作変数ansに入る値ans値の確認UF1start関数として戻す値
ＯＫボタンTextBoxの値PW値と合っているTrue
PW値と違っているFalse
キャンセルボタンFalseー
右上×印Falseー
図６－８

まず変数ansの値がFalseの場合はキャンセル（またはダイアログ右上×印）ですので、１４５行目で戻り値（UF1start関数）にFalseを代入しています。
次のキャンセル時以外は、TextBoxの値が変数ansに入っていますので、１４６行目で引数のPW値（ワークシートモジュール側から渡された開錠できるパスワード値）と比較をします。
比較し違っていれば、１４７行目でコメントを出し、１４８行目で戻り値にFalseを代入します。
残りは「開錠できるパスワード値と合っている」事になりますので、１５０行目で戻り値にTrueを代入します。

６－２－３．ボタン等による操作

２つのCommandButton、およびダイアログ右上×印の操作によるイベントプロシージャが、図６－９です。
  1. '==========　⇩(11)　OKボタンをクリック時　====================
  2. Private Sub CommandButton1_Click()
  3. 　ans = Me.TextBox1.Value
  4. 　Me.Hide
  5. End Sub
  7. '==========　⇩(12)　キャンセルボタンをクリック時　====================
  8. Private Sub CommandButton2_Click()
  9. 　ans = False
  10. 　Me.Hide
  11. End Sub
  13. '==========　⇩(13)　ダイアログ右上×印をクリック時　====================
  14. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  15. 　Cancel = True
  16. 　ans = False
  17. 　Me.Hide
  18. End Sub
図６－９

ＯＫボタン（CommadButton1）をクリックした時は、１５６行目で「ユーザーが入力したパスワード値」を変数ansに代入したのち、１５７行目でUserForm1を閉じます（Hideなので隠す）。
キャンセルボタンボタン（CommadButton2）をクリックした時は、１６２行目で変数ansにFalseを代入したのち、１６３行目でUserForm1を閉じます。

ダイアログ右上×印をクリックした時は少し複雑です。
キャンセル相当なので１６９行目で変数ansにはFalseを入れますが、「右上×印をクリックする」＝「ダイアログを閉じる（Unload UserFormと同じ状態）」と、１６９行目で代入した変数ansが初期化（False → Empty）されてしまうのです。

これは、フォームレベル変数ansの型が何かで異なるようで、データ型を色々変えて試してみたところ図６－１０のように、「値が保持される型」と「保持されずに初期化されてしまう型」があることが分かりました。
値が保持されるデータ型とされないデータ型
図６－１０

今回の変数ansは図６－２の１２２行目でVariant型として宣言されていますので、「QueryCloseイベント内で変数ansにFalseを設定」しても「ダイアログを抜けた図６－７の１４４行目時点ではEmpty」になってしまいます。
ですので、１６８行目で「Cancel = True」で「自動的に閉じてしまうのを中止」し、１７０行目の「Me.Hide」でキャンセルボタンと同様にダイアログを閉じて（＝隠して）います。

なお「Empty ＝ False」は成立しますので、今回QueryCloseイベントプロシージャそのものが無くても、ダイアログ右上×印をクリックするとUF1start関数は「False」を戻すことになります。
しかし、結果オーライではなく狙った通りに動かすことが大切と思い、今回のQueryCloseイベントプロシージャ内容にしました。


一般的に「長さゼロの文字列」と呼ばれているものは、実は２種類に分けられます。
１つ目は「String型で宣言しただけの文字列（vbNullString）」、二つ目が「長さゼロの文字列（" "）が入った文字列」です。
String型宣言しただけの文字列は「値ゼロの文字列」、「" "」が入った文字列を「長さゼロの文字列」と呼ぶようです。

VBEのローカルウィンドウやウォッチウィンドウで見ても、どちらも同じ「" " ／ String型」と見分けがつきません。しかし、String型データの先頭ポインタを取得する「StrPtr関数」を使用すると、「値ゼロの文字列」は「ゼロ」になり、「長さゼロの文字列」は「ゼロではない整数」になります。

この違いを利用するとInputBox関数の戻り値で「どちらのボタンをクリックしたか」が判別可能です。
InputBox関数の「何も入力せずにＯＫボタン」で返ってくる値は「長さゼロの文字列」で、「キャンセルボタン」で返ってくる値が「値ゼロの文字列」ですので、StrPtr関数で得られる値で分岐すれば良いことになります。
また、図６－１０のString型も初期化されていると考えれば「値ゼロの文字列」となることが理解できます。


７．借用情報入力用ダイアログ（UserForm2）

７－１．フォームの設定

借用情報入力用に、図７－１のようなフォームを準備しました。
借用情報入力用フォームの各コントロールの配置
図７－１

フォーム上に３つのTextboxとCommandButton２つを配置しています。表示等のプロパティはマクロ側から設定します。
その他のLabelは事前に文字を設定しています。

７－２．フォームモジュール

７－２－１．宣言部および起動・表示の準備

フォームが起動する時の設定関係が図７－２になります。
  1. '==========　⇩(14)　フォームレベル変数宣言　====================
  2. Dim ans As Variant
  4. '==========　⇩(15)　起動時のフォーム初期化　====================
  5. Private Sub UserForm_Initialize()
  6. 　Me.CommandButton1.Caption = "OK"
  7. 　Me.CommandButton1.Default = True
  8. 　Me.CommandButton2.Caption = "キャンセル"
  9. 　Me.CommandButton2.Cancel = True
  11. 　Me.TextBox2.IMEMode = fmIMEModeDisable
  12. 　Me.TextBox3.IMEMode = fmIMEModeDisable
  13. End Sub
  15. '==========　⇩(16)　フォーム表示時の初期化　====================
  16. Private Sub UserForm_Activate()
  17. 　Me.TextBox1.Value = ""
  18. 　Me.TextBox1.SetFocus
  19. 　Me.TextBox2.Value = Date
  20. 　Me.TextBox3.Value = ""
  21. End Sub
図７－２

１７３行目のフォームレベル変数ansは、ユーザーが入力した借用情報データ、およびボタン操作状況を保持します。借用情報データは複数ありますので配列の形にし、キャンセルボタンはBoolean型のFalseとして値を保持させるため、Variant型で宣言しています。

１７６～１８４行目はInitializeイベントプロシージャで、初めてフォームが起動する際に実行されます。
１７７行目・１７９行目は、ボタンに文字列を記入しています。
また「リターンキー押下 ＝ ＯＫボタンのクリック」とするために１７８行目で「.Default = True」とし、「ESCキー押下 ＝ キャンセルボタンのクリック」とするために１８０行目で「.Cancel = True」の設定を行っています。

１８２～１８３行目は「TextBoxにカーソルが来た時のIMEモードの設定」です。TextBox2とTextBox3は日付欄で「半角のみ」なので、半角以外には出来ないように「xlIMEModeDisable」を設定しています。

１８７～１９２行目はActiveイベントです。
２回目以降のフォーム起動時に前回入力した値が再表示されないようにする為、１８８行目ではTextBox1を空に、１９１行目ではTextBox3を空にしています。また１９０行目では「予約した日付（今日の日付）」をTextBox2に設定することで、前回の値をクリアすると共に「予約日≒貸出希望日」であろうという推定で「ユーザー側の手数を少なくする」ことを狙っています。

１８９行目の「SetFocus」は、フォーム起動直後にTextBox1にカーソルがあるようにしています。通常は上の方（借用者）から入力するでしょうから、すぐに入力できるようにするためです。

７－２－２．シート側から呼び出されるプロシージャ

図４－１２の５８行目から呼び出されるのが図７－３の関数プロシージャです。
戻り値は「借用情報の配列」で、キャンセルした場合は「False」を返します。
  1. '==========　⇩(17)　シート側から呼び出されるプロシージャ　====================
  2. Function UF2start() As Variant
  3. 　Me.Show
  4. 　UF2start = ans
  5. End Function
図７－３

１９５行目は、「UserForm2」を起動します。
フォーム起動中にはフォームレベル変数であるansには、入力した借用情報が配列の形で図７－４の２０１行目で代入され、キャンセル（ダイアログ右上×印クリックを含む）時にはFalseが代入されます。
１９６行目では、その変数ansをUF2startの戻り値にして、呼び出されたワークシート側に返しています。

７－２－３．ボタン等による操作

２つのCommandButton、およびダイアログ右上×印の操作によるイベントプロシージャは、図７－４です。
  1. '==========　⇩(18)　ＯＫボタンをクリック時　====================
  2. Private Sub CommandButton1_Click()
  3. 　If textboxCheck() = True Then
  4. 　　ans = Array(Me.TextBox1.Value, Me.TextBox2.Value, Me.TextBox3.Value)
  5. 　　Me.Hide
  6. 　End If
  7. End Sub
  9. '==========　⇩(19)　キャンセルボタンをクリック時　====================
  10. Private Sub CommandButton2_Click()
  11. 　ans = False
  12. 　Me.Hide
  13. End Sub
  15. '==========　⇩(20)　ダイアログ右上×印をクリック時　====================
  16. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  17. 　Cancel = True
  18. 　ans = False
  19. 　Me.Hide
  20. End Sub
図７－４

ＯＫボタン（CommadButton1）をクリックした時は、２００行目で「入力した借用情報のチェック（図７－６）」をし、ＯＫであれば２０１行目で３つのTextBoxの内容を配列にし、フォームレベル変数ansに代入します。その後、２０２行目でUserForm2を閉じます（Hideなので隠す）。

キャンセルボタン（CommadButton2）をクリックした時は、２０８行目で変数ansにFalseを代入したのち、UserForm2を閉じます。

ダイアログ右上×印をクリックした時は、UserForm1と同様に２１４行目で「QueryCloseで閉じる事を中止」します。その後キャンセルボタンと同じ動作をするように、変数ansにFalseを代入（２１５行目）しダイアログを閉じて（２１６行目）います。

７－２－４．日付データの確認

図７－５は「日付を入れるべきTextBoxを抜ける時に、入力値が日付型かをチェック」する機能です。最終的にはＯＫボタンをクリックした時に図７－６の「各TextBoxの最終チェック」をしますので、この機能は必須ではありません。
しかし全項目を一生懸命に入力したのに、最後になって「アレは入力されてない、コレは入れ方が違ってる」と言われるよりは、入力の途中で「入れ方が違います」と言われた方が、ユーザーにとっては親切に思えるのでこの機能を作りました。
  1. '==========　⇩(21)　日付用TextBox（借用予定日）から抜け出る時　====================
  2. Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  3. 　If Not (IsDate(TextBox2.Value) Or TextBox2.Value = "") Then
  4. 　　MsgBox "「貸出予定日」に入力した値は" & vbNewLine & "日付ではありません"
  5. 　End If
  6. End Sub
  8. '==========　⇩(22)　日付用TextBox（返却予定日）から抜け出る時　====================
  9. Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  10. 　If Not (IsDate(TextBox3.Value) Or TextBox3.Value = "") Then
  11. 　　MsgBox "「返却予定日」に入力した値は" & vbNewLine & "日付ではありません"
  12. 　End If
  13. End Sub
図７－５

２１９～２２３行目がTextBox2（借用予定日）から抜け出る時に発生するプロシージャ、２２６～２３０行目がTextBox3（返却予定日）から抜け出る時に発生するプロシージャで、内容的には一緒です。代表してTextBox2の方で説明します。

なお、このExitイベントプロシージャには引数Cancelがありますが、これを「Cancel ＝ True」としてしまうと、「正しくならない限りTextBoxを抜けられない」ことになり、入力を止めてキャンセルしたい時でも「キャンセルボタンをクリックできなくなる」ので注意が必要です。

「日付型ではない」ときに出すコメントは２１１行目ですが、そのコメントを「出さない条件」としては以下の２つです。
　・入れた値が日付型
　・TextBoxには何も入っていない状態
つまりカーソルがTextBoxを通過しただけの時は、コメントを出さないようにしています。この条件決めは設計者の思いもあるので法則がある訳ではありませんが、あまり過敏にコメントを出すとユーザーにうるさがられると思います。

７－２－５．借用データの最終チェック

図７－４の２００行目から呼び出される「入力データの最終チェック」が図７－６です。
チェックをし、違っている場所を示すやり方としては色々あると思います。例えば「違っている項目の色などを変える」とか「何番と何番が違います」のようなコメントを出す、というものもあります。一方「〇か所違っています」というような間違い探し的なものは、なるべく避けるべきと思います。
今回のは「上の方から調べていき、違っているところで再入力を求める」というような方法です。
  1. '==========　⇩(23)　借用条件のチェック　====================
  2. Private Function textboxCheck() As Boolean
  3. 　Select Case True
  4. 　　Case Trim(Me.TextBox1.Value) = ""
  5. 　　　Me.TextBox1.SetFocus
  6. 　　　MsgBox "借用者を入力して下さい"
  7. 　　Case Not IsDate(Me.TextBox2.Value)
  8. 　　　Me.TextBox2.SetFocus
  9. 　　　MsgBox "借用予定日を入力して下さい"
  10. 　　Case Not IsDate(Me.TextBox3.Value)
  11. 　　　Me.TextBox3.SetFocus
  12. 　　　MsgBox "返却予定日を入力して下さい"
  13. 　　Case Else
  14. 　　　textboxCheck = True
  15. 　End Select
  16. End Function
図７－６

今回の入力箇所は３箇所ですので、２３４行目・２３７行目・２４０行目で値を調べ、どれも問題なかったら２４３行目のElseでＯＫを出します。Boolean型の既定値はFalseですので、問題があった時は既定値のFalseになり、全てＯＫの時のみTrueを返すようにしています。

まずTextBox1の借用者欄は「値が空」だったら２３５行目でカーソルをTextBox1に置き、２３６行目でコメントを出しています。
ただし「スペースのみを入力」するようなユーザーもいますので、「Trim関数」を使って前後のスペースをカットしてから「値が空」か否かを判断しています。

TextBox2とTextBox3は日付型ですので、「IsDate関数」を使って日付か否かの判断を行っています。カーソルを持ってくることとコメントを出す事は、TextBox1と同じです。

８．ワークブックモジュール（ThisWorkbook）

このシステムは、シートを保護して使用することを前提に考えており、管理者がロック設置解除セル（サンプルファイルのＪ３セル）をダブルクリックすることで設置・解除が出来ます。
この時、マクロでのセル書込みを許可するため、シート保護時（Protect時）に「userinterfaceonly:=True 」のパラメータを付けて実行しています。しかし残念ながらこのパラメータは、Excelを再起動すると「この設定を忘れてしまう」のです。

つまりシステムを起動した時は、「ユーザー操作はもちろん、マクロからの操作からも保護」されているのです。
ですので、起動時には「userinterfaceonly:=True」付きでシート保護を掛け直す必要があります。それが図８－１になります。
  1. '==========　⇩(24)　ブック起動時のシート再保護　====================
  2. Private Sub Workbook_Open()
  3. 　With Sheet1
  4. 　　If .ProtectContents = True Then 　　 'シートが保護されている時
  5. 　　　.Unprotect
  6. 　　　.Protect userinterfaceonly:=True
  7. 　　End If
  8. 　End With
  9. End Sub
図８－１

２５０行目で、シート保護が掛かっているかを調べます。
掛かっているのであれば、２５１行目で一旦シート保護を解除し、２５２行目で再度「userinterfaceonly:=True」付きでシート保護（Protect）を掛けます。

起動時に「シート保護が掛かっていない」場合は、今回そのまま放置することにしました。中には「シート保護なしで運用」するところもあるでしょうから、その場合には邪魔なコードになるというのが理由です。
もし保護し忘れ防止のためにコードを追加するのでしたら、データシートの非表示も同時に実行することを忘れないようにして下さい。

９．最後に

この備品管理表は、たまたま見ていた「YAHOO知恵袋」の中で見つけた課題です。
この時まで、管理表と言えば「現在の貸出状況と合わせて過去の使用状況も一覧として把握可能」で、借りる時も「１か月先の予約ができるもの」「カレンダーで日付設定できるもの」というような、ある程度の機能を備えたものが要求されるはず、と思い込んでいました。
しかし「直近の日程が見えれば良い」場面もあるようだったので、今回の簡単なシステムを考えました。
ですので、今回のシステムを見て「こんな子供だましのものは、いらない」と怒る方もいるとは思いますが、「簡単でいいね」と思っていただけるような方の目に留まってくれることを期待します。


備品の予約・貸出・記録ができる貸出管理表（it-044.xlsm）

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