エクセルを自動で閉じるVBAの導入方法【 複数の人が扱うエクセルへの設定がおすすめ 】
疑問がある人
こんな要望にお応えします。
この記事では、時間が経過するとエクセルを自動で閉じるVBAの作り方と導入方法について詳しく解説しています。
職場にはみんなが扱うことができる共有のエクセルファイルがあり、１つのエクセルファイルを複数の人が同時に開こうとすると「読み取り専用」になってしまいます。ある人が編集中のエクセルを閉じ忘れると、いつまでたってもそのエクセルを編集できない！という問題が発生してしまいます。
制限時間でエクセルファイルを閉じるVBAを設定すれば、上記のような問題を解消することができます。複数の人が１つのエクセルファイルを編集する可能性がある方にとってもおすすめのVBAなので、是非設定して利用してみてください。
下記のサイトではこの記事で紹介している「時間が経過するとエクセルが自動で閉じるVBA」をダウンロードすることができます。
<<時間が経過するとエクセルが自動で閉じるVBAのダウンロードはこちら
目次
時間が経過するとエクセルを自動で閉じるVBAとは
時間が経過するとエクセルを自動で閉じるVBAとはどういうものか、下記の動画で紹介しています。
VBAは下記のような流れで実行されていきます。
- エクセルを開いたときに制限時間のカウントダウンがスタート
- 残り時間がわかるようにカウントダウンタイマーを表示
- 制限時間の設定は簡単に変更可能に
- カウントダウンタイマーに一時停止機能を搭載
- タイマーがゼロになると「上書き保存」をして閉じる
制限時間を設定するVBA、カウントダウンタイマーを表示するVBAなどが導入されていて難しい！と感じるかもしれませんが、VBAを一つ一つ分けて考えてみるとさほど難しいVBAは使われていません。
下記で紹介しているVBAの導入方法では、一つ一つのVBAを詳しく解説しているので是非参考にしてみてください。
時間が経過するとエクセルが自動で閉じるVBAの導入方法
1.様式を作成する
- A4セル・・・「時」
- C4セル・・・「分」
- E4セル・・・「秒」
入力する数字を変更すると、エクセルを閉じるまでの制限時間が変更されます。
2.VBAを起動
3.標準無ジュールを作成
4.標準無ジュールにVBAを記入
5.サンプルVBA
サンプルVBA①
| 1 | Sub Timer() |
| 2 | Dim L As Date, cnt As Double |
| 3 | L = DateAdd(“h", Range(“A4"), Time) |
| 4 | L = DateAdd(“n", Range(“C4"), L) |
| 5 | L = DateAdd(“s", Range(“E4"), L) |
| 6 | rng = 0 |
| 7 | UserForm1.Show vbModeless |
| 8 | Do |
| 9 | cnt = DateDiff(“s", Time, L) + rng |
| 10 | UserForm1.TextBox1 = Format(TimeSerial(0, 0, cnt), “hh:nn:ss") |
| 11 | If UserForm1.TextBox1 = “00:00:00" Then Exit Do |
| 12 | DoEvents |
| 13 | Loop |
| 14 | Call CloseMe |
| 15 | End Sub |
サンプルVBA②
| 1 | Sub CloseMe() |
| 2 | ActiveWorkbook.Save |
| 3 | ThisWorkbook.Saved = True |
| 4 | If Workbooks.Count <= 1 Then |
| 5 | Application.Quit |
| 6 | Else |
| 7 | ThisWorkbook.Close False |
| 8 | End If |
| 9 | End Sub |
6.sheet1にVBAを記入
7.サンプルVBA
サンプルVBA③
| 1 | Private Sub WorkSheet_Activate() |
| 2 | UserForm1.Show vbModeless |
| 3 | End Sub |
8.ユーザーフォームの作成
9.ユーザーフォームにVBAを記入
10.サンプルVBA
サンプルVBA④
| 1 | Private Sub CommandButton1_Click() |
| 2 | Dim rng_t As Date |
| 3 | rng_t = Time |
| 4 | MsgBox (“タイマーがストップされました" & vbCrLf & “再開するには｢OK｣を押してください") |
| 5 | rng = rng + DateDiff(“s", rng_t, Time) |
| 6 | End Sub |
11.ユーザーフォームの表示設定
12.ワークブックにVBAを記入
7.サンプルVBA
サンプルVAB⑤
| 1 | Private Sub Workbook_Open() |
| 2 | Call Timer |
| 3 | End Sub |
12.VBAの実行
以上でVBAの導入が完了します。
ユーザーフォームの設定など難しい手順もありますが、上記で紹介している順番に操作すれば設定できるはずです。是非挑戦してみてください。
時間が経過するとエクセルが自動で閉じるVBAの運用方法
時間が経過するとエクセルが自動で閉じるVBAは複数の人が扱うエクセルへの導入がおすすめです。エクセルの閉じ忘れで「読み取り専用」になってしまうという問題を解消することができます。
ただし、強制的にエクセルを閉じることになるので運用には注意が必要です。以下の項目をしっかり守ることでトラブルを発生させることなく運用することがきるはずです。
- 時間設定を長くして編集している最中にエクセルが閉じない設定にすること
- 上書保存してからエクセルを閉じること
- 自動でエクセルが閉じることを職場の全員が理解すること
ひらめく人
悩んでる人
私が勤めていた会社は、独自のネットワーク回線を持っていて共有のサーバーにあるエクセルファイルを地方にいる社員も操作することができる！という環境でした。そのため地方の人がエクセルを開きっぱなしで外出してしまった場合、そのエクセルファイルを開くとずっと「読み取り専用」となり編集することができない！という問題がありました。
地方の人が操作する頻度が高いエクセルに「時間が経過するとエクセルが自動で閉じるVBA」を設定することで問題を解消することができました。運用には注意が必要なVBAですが、とても便利なVBAであることは間違いないので気になる方は上手に使ってみてください。
自動で閉じるVBAに使われているプログラム解説
時間が経過するとエクセルが自動で閉じるVBAには大きくわけて以下のVBAが設定されています。
- エクセルを開くとVBAが実行させる
- 制限時間を決めるVBA
- カウントダウンタイマーを表示させるVBA
- 上書き保存してエクセルを閉じるVBA
各項目ごとにどのようにVBAを作成すればいいのか解説していきます。
エクセルを開くとVBAが実行させる
エクセルを開いたときにVBAを実行させるには、「Workbook_Open」を使用します。
上記サンプルVBA⑤で「Workbook_Open」が利用されているので確認してみてください。
「Workbook_Open」の他に、「Auto_Open」でもエクセルを開いたときにVBAを実行させることができます。
似たようなVBAですが、Auto_Openの処理よりもWorkbook_Openの方が優先されるようです。
ひらめく人
制限時間を決めるVBA
制限時間を決めるVBAには、時間に関するVBAが使われています。
- TimeSerial関数
- DateAdd関数
- DateDiff関数
3つのタイマーを組み合わせることでカウントダウンタイマーを作り出しています。
TimeSerial関数
引数で指定した(時 , 分 , 秒 )値を返すもの
基本構造
TimeSerial( 時 , 分 , 秒 )
| 時 | 0~23の数値を指定 |
| 分 | 0~59の数値を指定 |
| 秒 | 0~59の数値を指定 |
戻り値は日付型(Date)の宣言になります。
ひらめく人
TimeSerial関数のサンプル
| 使用例 | 結果 |
| Cells(1,1) = TimeSerial(0,0,1) | 12:00:01 AM |
| Cells(1,1) = TimeSerial(23,59,59) | 11:59:59 PM |
| Cells(1,1) = TimeSerial(24,60,60) | 1900/1/1 1:01:00 |
TimeSerial関数を使ってセルに時刻を入力してみると、入力値によって書式設定が変わることがわかりました。
上記サンプルの１つ目と２つ目の書式設定は「h:mm:ss AM/PM」
上記サンプルの3つ目の書式設定は「yyyy/m/d h:mm」
ひらめく人
Dateadd関数
Dateadd関数は日付を計算するときに使用される関数です。例えば、「今から1時間後の日付をA1セルに表示させる」「今からA1セルに記入された時間を加えた日付を表示させる」という使い方ができます。
基本構造
Dateadd( 単位 , 数量 , 基準時間 )
DateDiff関数
DateDiff関数は日付を計算するときに使用される関数です。例えば、「今から1時間前の日付をA1セルに表示させる」「今からA1セルに記入された時間を引いた日付を表示させる」という使い方ができます。
基本構造
DateDiff( 単位 , 数量 , 基準時間 )
ひらめく人
カウントダウンタイマーを表示させるVBA
カウントダウンタイマーは、ユーザーフォームと３つの関数を組み合わせることで作ることができます。カウントダウンタイマーを表示させるVBAは以下のようになります。
カウントダウンタイマーを表示させるVBA
cnt = DateDiff(“s", Time, L) + rng
UserForm1.TextBox1 = Format(TimeSerial(0, 0, cnt), “hh:nn:ss")
上記のカウントダウンタイマーを表示させるVBAの意味は以下のようになります。
カウントダウンタイマーを表示させるVBAの意味
変数cntに、値(時間を1秒ずつ減らしていく)をいれる
関数TimeSerialに変数cntを入れ、取得した時間を関数Formatで文字列に変換し、テキストボックス(ユーザーフォーム)で表示させる
カウントダウンタイマーを表示させるVBAのメインとなる関数がFormat関数です。
基本構造
Format( 指定した値 , “指定した書式" )
指定した書式一覧
| 指定した書式 | 意味 | 結果 |
| yyyy | 年数(西暦) | 2020 |
| m | 月 | 6 |
| d | 日付 | 1 |
| h | 時間 | 0~23 |
| n | 分 | 0~59 |
| s | 秒 | 0~59 |
| hh | 時間 | 00~23(2桁表示) |
| nn | 分 | 00~59(2桁表示) |
| ss | 秒 | 00~59(2桁表示) |
ひらめく人
カウントダウンタイマーには、ユーザーフォームの表示設定が必要になります。
色を変えたり、文字の大きさを変えることで見やすいカウントダウンタイマーにすることができます。いろいろ設定してみてください。
上書き保存してエクセルを閉じるVBA
エクセルを閉じるVBAをには「上書き保存するVBA」の設定が必須事項です。決められた時間が経過したからといって保存せずにエクセルを閉じてしまうと編集した内容が消えてしまいます。
ひらめく人
上書き保存して閉じるVBAには「CloseMe」を使用しています。上記で紹介しているサンプルVBA②が「上書き保存して閉じるVBA」なので参考にしてみてください。
単純に時間が経過したら閉じるという設定ではなく、いくつか条件を持たせています。
- 作業中のエクセルを上書き保存する
- 複数のエクセルを開いているとき、エクセルは閉じない(現状維持させる)
- 開いているエクセルが一つだけのとき閉じるを実行する
上記の条件を設定しておけば、制限時間で閉じるという処理をしてもトラブルが発生する心配はありません。