« 「配列がありません」(。´・д・)エッ~ | トップページ | CallByName関数と コールバック関数と Eventで呼んでみる »

2013年3月31日 (日)

作業グループの禁止、複数シートの選択を禁止するマクロを考えてみた。

作業グループの禁止、複数シートの選択を禁止するマクロを考えてみた。

エクセルのワークシートを複数選択し、まとめて編集できる機能を 禁止させたい場合がある。 複数シートを選択すると、エクセルのタイトルバーのキャプションに [作業グループ] という文字が追加されて表示される。

Photo_2

マウスの操作だと、Shift キーとか Ctrl キーを押しながら複数のシートを選択する事ができ る。 また、キーボードの操作だけでも出来る。 この時、デスクトップPCとノートPCではキーの場所の操作が同じ とは限らない。 機種によっての操作も同じである保証もなく、ついうっかり複数シート選択の状態にしてしまう事だってある。 そんな時、ワークシートのチェンジイベントになんらかのマクロが仕込んであると Undo する事も出来なくなる。 保存せずに閉じてやり直せば良い場合もあるだろうが、マクロの仕様で強制的に保存されてしまう場合だってあるので、この 場合は「取り返しのつかない」状態になる。 出来る事ならこの悲劇は起きないように作りたい。

作業グループ状態を解除するには、

ActiveSheet.Select

と書けばよく、複数シートが選択されているかどうかは、

ActiveWindow.SelectedSheets.Count

で、判断できる。

問題はこのコードをいつ実行させるか、であるが、 どうしたものか。シートが複数選ばれた時に発生する Cancel の引数を持つイベントでもあれば良いのだが、そんなイベントは無いっぽい。ワークシートの Activateイベントに ActiveSheet.Select と書くだけで Key操作による複数シートの選択は出来なくする事は出来る。 しかし、マウス操作による複数シートの選択ではActivateイベントが発生しない。 Worksheet_SelectionChange イベントだと、高い確率で複数シートの選択を抑止する事は出来るが、選択セルの変更が無いまま複数シートを選択し値を編集する事は出来てしまう。また、このイベントに処理を仕込むとシートの動作が軽快さを失うので避けたいところ。

結論として、 Worksheet_Change に仕込んでみた。 ご明察の通りセルの値が書き換えられた後に発生するイベントであるため、書き換えられた後に複数シート選択を解除できた ところで手遅れなのだ。だが其処は Undo で凌ぐ事が出来た。Chengeイベント自体にメインとなる処理が仕込まれていても複数シートが選択されていた場合は実行 されないようにしてある。そして Undo 自体を再実行できないようにするために、ダミーのプロシージャを実行させて いる。

何か処理をさせるさせないに関わらず、 各ワークシート Worksheet_Change イベントを仕込まなくてはならない。そしてThisworkbook モジュールにはWorkbook_SheetChangeイベントを作ってはならない。両方に用意すると、個々のシートにあるイベントの方が先に実行されてしまい抑止する筈だった処理が実行されてしまうのと、Thisworkbook モジュールのイベントが実行された後に複数の選択されたシートのイベントがそれぞれ交互に発生してしまう。イベントを復活させるタイミングを、既に発生してしまったイベントの処理数を数えて判断しているので、ワークシートの Worksheet_Change に処理が仕込んである場合に、実行されてしまい実害が出る可能性がある。

大まかな処理の流れ

  1. 複数シートを選択してセルの値を編集する
  2. アクティブシートの Worksheet_Change イベントにより ProtectGrpSh がRUNされる
  3. 作業グループ状態ならば
    1. 作業グループの解除
    2. イベントの停止
    3. アンドゥ実行で編集したセルが未編集の状態に復元される
    4. イベントの開始
    5. アクティブシート以外のグループ化されていたシートの Worksheet_Change イベントにより ProtectGrpSh がRUNするが作業グループ状態ではない為何も起きない。
  4. 作業グループ状態でない場合はスル―

「sample.xls」をダウンロード


'Thisworkbookモジュール
Option Explicit
Private mSelShCnt As Long
Private mGroupFlg As Boolean

'-----------------------------------------------------------------
''▲ProtectGrpSh が正常に動作する為にはThisworkbookモジュールに
' Workbook_SheetChange イベントを作ってはならない
'-----------------------------------------------------------------

''キー操作によるシートのグループ化を回避する
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    ProtectGrpSh True
    Saved = True
End Sub
' ProtectGrpSh が正常に動作する為には
'個々のシートのイベントがThisworkbookのイベントより
'先に実行されてしまう為
'各ワークシートのWorksheet_Changeイベントに何らかの処理を
'仕込む場合、は個別に以下のコードを仕込む必要がある。

        
        'Private Sub Worksheet_Change(ByVal Target As Range)
        '    'マウス操作でグループ化され編集されてしまったシートを回復する
        '    If ThisWorkbook.ProtectGrpSh Then
        '        Exit Sub
        '    End If
            '何らかの処理~
        'End Sub

'省略可能な引数は ThisworkbookモジュールのWorkbook_SheetActivateから実行する
'場合に引数として渡す事
Friend Function ProtectGrpSh(Optional ShActive As Boolean = False) As Boolean
    Dim UndoFlg As Boolean
    Application.EnableEvents = False
    
    If ActiveWindow.SelectedSheets.Count > 1 Then
        
        mSelShCnt = _
            ActiveWindow.SelectedSheets.Count
        
        If ShActive Then
            mGroupFlg = False
        Else
            mGroupFlg = True
        End If
        
        ActiveSheet.Select
            
        On Error Resume Next
        Application.Undo
        'Undo を繰り返し使用出来ないようにするために
        '無害なプロシージャを実行しておく
        Call dummyProc
        If Err.Number = 0 Then
            UndoFlg = True
        Else
            UndoFlg = False
            Err.Clear
        End If
        On Error GoTo 0
        
        If UndoFlg Then
            MsgBox _
                "作業グループの使用は禁止されています" & _
                "直前の操作はキャンセルされました"
                
        Else
            MsgBox _
                "作業グループの使用は禁止されています"
                
        End If
    End If
    
    Application.EnableEvents = True
    mSelShCnt = mSelShCnt - 1

    If mSelShCnt < 0 Then
        mGroupFlg = False
        mSelShCnt = 0
    End If

    ProtectGrpSh = mGroupFlg
    
End Function

Private Sub dummyProc()

    Dim WS As Worksheet
    Set WS = Worksheets.Add
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    WS.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

バグが無いといいのだけれど… ( ̄ー ̄;

別の提案

ツールの作り方の正解が何処にあるのか判らんのですが、 Worksheet_Change や、Worksheet_SelectionChange イベントの 使用はやめた方が良いと、今回もまたつくづく思っています。少なくとも僕は今まで、この2つのイベント 使って何かを処理して、良い評判を得たという経験がありません orz 。

良い評価を得られない原因は、ワークシートへの入力作業が重ったくなる事だと思われます(| 柱 |ヽ(・´_`・。)反省)。

で、「作業グループによる操作ミスが発生する可能性を排除する」 作り方として、「常にユーザーには一枚のワークシートしか見せない作り方」、を 別解として提案しておきます。たぶん、その方が良い結果になるような気がする…  たぶん、きっと… ( ̄▽ ̄;

以上

Update history

  • 2013/4/4:文章少しなおしました。
  • 2013/4/4:エラーメッセージもちょっと直しました。
  • 2013/4/24:「別の提案」を追加しました。

« 「配列がありません」(。´・д・)エッ~ | トップページ | CallByName関数と コールバック関数と Eventで呼んでみる »

コメント

コメントを書く

(ウェブ上には掲載しません)

トラックバック

« 「配列がありません」(。´・д・)エッ~ | トップページ | CallByName関数と コールバック関数と Eventで呼んでみる »