Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2013)
シート未使用状態のチェックは?
投稿日時: 20/11/17 06:17:23
投稿者: ロードランナー
投稿者のウェブサイトに移動

いつもお力添えいただき、ありがとうございます。
 
今度は、シートの未使用状態をどのように考えればチェックできるか、という質問です。
 
いま考えているのは
@Cells.SpecialCells(xlCellTypeLastCell) がA1セルであること
AそのA1セルのIsemptyがTrueであること
の2つの条件が揃えば、シート未使用と判定
でいいのかなと思っています。
 
ところが、例えばA1セル以外に何か書き込み、全セル選択して削除しても
Cells.SpecialCells(xlCellTypeLastCell)は、前のセルが残っています。
 
これでは@、AのAnd条件で未使用と判定することができません。
 
なぜ全セル削除したのに、前のセルがLastCellになるのか分かりません。
 
もし、他に方法があればご教示いただきたいと思います。
 
よろしくお願いいたします。
 
※なぜこれをやりたかと言いますと、誰かが誤ってシートを追加してしまったのか、何か作りこんでいるのかが分からないからです。複数の人がPCを供用して使うと必ず起きてしまいます。

回答
投稿日時: 20/11/17 09:32:59
投稿者: Suzu

VBA で考えるより まず一般機能で考えましょう。
その上で、その機能をVBAでどう実現できるかを考えましょう。
 
Cells.SpecialCells(xlCellTypeLastCell)
 
は、一般機能では、
検索と選択 - 条件を選択してジャンプ - 最後のセル です。
(それよりは、ショートカットキー ctrl + End の方が馴染みがあるかも知れません。)
 
セルを全範囲削除して ctrl+End を実行しても 削除前のセル位置にジャンプします。
 
これは、ワークブックを保存すると範囲がリセットされますので保存すれば良いです。
 
ただし、
セルの書式設定を検知します(表示形式・配置・フォント・罫線・塗りつぶし・保護)
Shapeは検知できません
条件付書式は検知できません
 
その他、
 名前定義
 シートへのVBAコード(間違って追加したシートで シートクラスモジュールを使うとは思えませんが)
 検知範囲がWorkSheetコレクションにするのか Sheetコレクションにするのか
   (Chartや、マクロシート等もありえますよね)
 
何を以って 未使用 とするかは 検討は必要と思います。
 
 
 
別件で恐縮ですが
 
BeforeRightClick の件ですが、
 

引用:
・API関数を標準モジュールで宣言
・判定の関数も標準モジュールに作り、クラスからCall
するように変えたところ、今までの問題が一挙に解決しました。

 
ロードランナーさんの 実装コードが判らないのですが
 
【Re: BeforeRightClick時の動作】
https://www.moug.net/faq/viewtopic.php?t=79990
 
にてsimpleさんが提示くださっている
If (GetAsyncKeyState(vbKeyControl) And KEY_PRESSED) = KEY_PRESSED Then
このビット演算がミソです
 
当方も、API GetAsyncKeyState のみでは、判定できず、
-32768 とのビット演算で判定できる様になりました。

投稿日時: 20/11/17 10:54:11
投稿者: ロードランナー
投稿者のウェブサイトに移動

Suzuさん、コメントありがとうございます。
 
誤ってシートの追加操作を防ぎたかったのですが、NewSheetイベントを使って以下のようにしました。
 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Static flg As Boolean
    If Not flg Then
        If MsgBox("シートが追加されました。削除しますか?", vbYesNo) = vbYes Then
            Application.DisplayAlerts = False
            Sh.Delete
        End If
        If MsgBox("今後このメッセージを表示しない?", vbYesNo) = vbYes Then
            flg = True
        End If
    End If
End Sub
 
シートが未使用かどうかの判定は、意外と難しいものですね。
本当は、空白シートと簡単に比較できればいいと思いました。
 
条件付き書式がLastCellで検出できないとは…。
 
そもそもLqastCellってどんなときに使うのでしょうか。

回答
投稿日時: 20/11/17 11:42:16
投稿者: WinArrow
投稿者のウェブサイトに移動

感想
 
普通に考えて、
Newsheetイベントは、新しいシートですから「空白」を判断sる必要はないと
考えます。
テンプレートシートを指定した場合も考慮して、
「削除しますか?」という無味感想的なアナウンスではなく、
申し越し丁寧ななうんすの方がよいでしょうね・・・
 

回答
投稿日時: 20/11/17 11:53:57
投稿者: sk

引用:
誤ってシートの追加操作を防ぎたかったのですが

[ブックの保護]機能によって、シート構成を保護しておくように
なさればよろしいのでは。

投稿日時: 20/11/17 11:56:45
投稿者: ロードランナー
投稿者のウェブサイトに移動

う〜ん、言葉足らずだったようです。
 
シートタブの右端にある+ボタンを誤って押したとき、そのまま放置されることを防ぎたかったのです。
このボタンのせいで無駄なシートができ、それを知らない人は放置してしまう、というのが現状。
 
シートを挿入した意識もないまま削除もしない(やり方がわからない)、って、こういう人も多いようなのです。
 
また仕事ではテンプレートを使うような人もいませんので、これくらいにします。

投稿日時: 20/11/17 12:31:30
投稿者: ロードランナー
投稿者のウェブサイトに移動

引用:
[ブックの保護]機能によって、シート構成を保護しておくように
なさればよろしいのでは。

 
以前、この方法でやっていたのですが、共有して使うファイルがたくさんあるため現実的ではなくなりました。
 
そこで個人用マクロブックに先のコードを組み込むことで考えています。
(先のコードは、試しにブックのイベントに作ってみたものです)[/quote]

回答
投稿日時: 20/11/17 16:02:12
投稿者: Suzu

引用:
そもそもLqastCellってどんなときに使うのでしょうか。

 値や書式が設定されていたセルを取得する。そのままです。
 ただ、先に述べた条件があるのでそれを考慮して使用するという事です。
 
 
シート削除をする目的の為の判定に、シートに値があるかどうかの判定の為に
 SpecialCells(xlCellTypeLastCell) を使用する。そこは判ります。
 
 が、その判定の直前に、全セルのクリア がされる状況とは、どのような状況でしょうか?
 VBAにて全セルクリアを行っているのであれば、そのクリアを行う判定基準があるのですよね?
 それが、シート削除の基準にはなり得ませんでしょうか?
 
 
xlCellTypeLastCell では、書式や条件付書式は判断できません。
ですが、「条件付書式」のみが設定されている 空白シート の利用価値はありますでしょうか?
 
現実的に考えてて条件を決めるとすれば
・セルに値が入っている
・Shapeが存在する
になるのではないかと思います。
 
とすると、
・提示頂いていた xlCellTypeLastCell にて A1 であり、かつ 値が入っていない
     或いはCOUNTA関数にて値の入っているセルの数が 「0」の場合
・ShapesコレクションのCountプロパティが 「0」の場合
この 二つの条件が満たされた場合にはそのシートを削除して良いと考えます。
 
 
未使用のシートを放置されて削除しなければならない
現実的に思い当たる状況が無いのですが、どの様な状況なのでしょうか?

回答
投稿日時: 20/11/17 16:48:40
投稿者: WinArrow
投稿者のウェブサイトに移動

感想2
 
NewSheetイベントで
誤って、新しいシートを作ってしまった場合
 
疑念1
新しいシートならば未使用に決まっているから、未使用チェックは、ナンセンスだと思います。
 
疑念2
ブックには、シートを追加してよいものと、シートを追加してはいけないものが
あります。
個人用マクロブックに、全てのブックの制御を持たせるのは、
やり過ぎではないでしょうか?
ブック固有の問題は、ブックに限定する方がよいと思います。
 
疑念3
シート追加には、新しいシート追加もあれば、
誤って既存シートを複写しても追加されてしまいます。
これをどうするのか?
 
 

回答
投稿日時: 20/11/17 18:04:50
投稿者: mattuwan44

えっと、、、、
 
やりたいことは、
マクロを、個人用マクロブック等にセットしておいて、
編集中のブックに対して、未使用シートがあるかどうかを検索したいということでしょうか?

投稿日時: 20/11/18 09:16:25
投稿者: ロードランナー
投稿者のウェブサイトに移動

みなさま
 
たくさんのResをいただきありがとうございます。
 
やりたいことより仕事の内容(パソコンやファイルをどう扱っているか)というを先にお話ししたほうがいいような気がしました。
 
仕事は複数のスタッフがシフト勤務で、一台のパソコンを共有しています。
また、扱うファイルもたくさんあり、それも共有して使っています。
 
ですので、次のシフトの時にはファイルが更新されていることも当然あります。
 
このとき、たぶん誤操作だと思うのですが、シートタブの右にある「シートタブの挿入」をクリックしてしまうようで、削除してくれればいいのにそのまま放置されていることがよくあります。
 
各スタッフのパソコンスキルはピンキリで、ショートカットを知らない人もいます。
また,スタッフは毎年入れ替わりますから、いくら教えてもきりがありません。
 
このようなスタイルの仕事なので、まず
@誤操作でのシート挿入を簡単に取り消しできるようにしたい
A残ってしまった空シートを判定して削除したい
の2つを考えていました。
 
@は、既に書いたコードをクラスに書いて実装できましたが、Aをどうすればいいか、というのが残された課題です。
 
Suzuさんの

引用:
・提示頂いていた xlCellTypeLastCell にて A1 であり、かつ 値が入っていない
     或いはCOUNTA関数にて値の入っているセルの数が 「0」の場合
・ShapesコレクションのCountプロパティが 「0」の場合
この 二つの条件が満たされた場合にはそのシートを削除して良いと考えます。

 
という考え方でいいのかな、と思います。図形や写真を貼り付けることもあるので、Shapes.Countの判定も入れたいと思います。
 
本当は、空白シートと比較することができれば、一番簡単なのですが…。

回答
投稿日時: 20/11/18 11:02:35
投稿者: Suzu

シートが増えてはいけない理由についての説明は頂けないようですが
 
当方が気にするのは、見た目や 管理者・開発者 のエゴ・自己満足になっていないかです。
 
初心者の方に、マクロで手助けや制限を掛けると、それが「普通」と勘違いする事になります。
そのパソコン以外で作業を行ったときに混乱・戸惑いが発生します。
 
シートが増えたことにより、どんな不具合が生じるのか。
数シートしか無いブックにて、不必要なシートがあるなら、放っておいても不具合はありません。
あったとして、多くなって来たときにシート選択が面倒になる。程度なのではないでしょうか。
 
管理者の方が見るのに面倒 と言うなら 管理者側のマクロに空白シートは非表示 とすれば良いと思います。
 
ブック内のシート数が多くなるのであれば、ファイルを分割する。
ファイル数が多くなりすぎるなら、データベースの運用を考える。
それが、管理者・開発者と思います。
 
 
どうしても削除するのであれば私なら
 
個人マクロブック にて、WithEvents にて、各WorkBookのイベントを監視し
WorkBookの BeforeSaveイベントにて、
空白チェック → 空白シート名の表示 削除確認/Cancel=Treu → シート削除
でしょうかね。

投稿日時: 20/11/18 12:27:21
投稿者: ロードランナー
投稿者のウェブサイトに移動

仕事の実態、困りごとをこれ以上書いても理解いただけないような気がします。
 
シニアだらけの事務職では、この件だけではなく
あったはずのシートが消えた、あるべきフォルダからファイルが他に移動していたなど
何があってもおかしくないのが日常茶飯事。
この前もサーバーのファイルが削除され、Trashboxからなんとか復元できたときは、冷や汗ものです。
 
無駄なシートを増やした意識もなく、シート選択に苦慮するのは嫌なので今回の対応を考えました。
 
もちろん黙って仕込んでいるわけではなく、きちんと説明しますから、自分のパソコンとの違いはわかるはずです。
 
シートが増えて困る理由は

引用:
多くなって来たときにシート選択が面倒になる。程度なのではないでしょうか。

 
程度問題ですね。たくさんあるシートの途中にいきなり空白のシートができる。
しかも、それが意図して作られたのか、誤操作でできてしまったのかも分からない。
こんなシートがあちこちにできるのは、他のスタッフにとっても迷惑な話なのです。
 
 
引用:
ブック内のシート数が多くなるのであれば、ファイルを分割する。
ファイル数が多くなりすぎるなら、データベースの運用を考える。
それが、管理者・開発者と思います。

 
さいわい、こんな大袈裟な職場ではありません。それにファイル分割なんてやったら、よけい混乱させるだけ。自分一人が使うファイルではないので。
 
BeforeCloseの起動はいいかも知れないですね。ちょっと考えます。
(時間がかかるような処理なら、別な方法でしょうか)