FC2ブログ

Excelで特定のシートのセル範囲でOnKeyイベントを取得してみた

ExcelからIEをコントロールするVBAを書いてきたなかで
Excelの特定のセルをダブルクリックすると、そのセルの値でIEを検索する処理を作ってきた

Worksheet_BeforeDoubleClickのTargetの値を使えばそれなりの動作はする
IEの検索キーテキストにセルの値をセットして、検索ボタンを押せばいい

たとえば
顧客番号のセルをダブルクリックすれば
その顧客のトップ画面が表示される仕組みだ

このエクセルで、だいぶ業務改善がなされたのではあるが
職場には、ダブルクリックが満足にできない人がいることが問題になった
「えっ、ダブルクリックができない人がいるんデツカ?」

と、マウス操作が満足にできない事例を書き始めたのだが
それはまたこんどにしておく

●Excelでキーボード入力を検知する
論理的には
テンキーのEnterは受け付けない
Ctrl+Cは受け付けない(コピーさせない)
とかも可能と思われるが

今回のお題は、特定のセル範囲でEnterが押されたときの動作である

キーボードの入力を感知して、自分が作成したマクロを動作させる方法には2つある
1.Application.MacroOptions Macro:="作ったマクロ", ShortcutKey:="KEY文字"
2.Application.OnKey "KEY文字", "作ったマクロ"

1は、マクロオプションダイヤログで作成済マクロにキーを割り付けるもので
一度実行すればそのブックの中でだけ有効となり、この設定はブックとともに保存されるので、次回もブックを開けば有効となる。
ただし、割り付けできるキーは、コントロールとの組み合わせに限られるので、今回のお題には対応できない。

2は、起動中のExcel自身に設定されるもので
様々なキーを割り付けることができるが、1と違いブックに依存するものではないので、誤爆対策をしなければならない
ネットをOnkey VBAなどで検索すると、文法の説明は出てくるが、誤爆対策の情報が少なかったので、これを実装してみたわけだ

●OnKeyの誤爆対策
特定のブックの、特定のシートの、特定のセル範囲でのみキー検知を有効にする
おおまかなポイントは以下の通り

1.Workbook_Openで特定のブックの、特定のシートの、特定のセル範囲を指定
2.Workbook_BeforeClose OnKeyの無効化
3.Workbook_WindowDeactivate OnKeyの無効化
4.Workbook_WindowActivate OnKeyを設定すべきかを判定
5.Workbook_SheetActivate OnKeyを設定すべきかを判定
6.Workbook_SheetSelectionChange OnKeyを設定すべきかを判定
7.OnKeyを設定すべきかを判定するマクロ

これらをThisWorkBookモジュールに記載する
上記のうち1から6は、このブックにおけるイベントを感知するマクロで

1.Workbook_Openブックを開いた時
2.Workbook_BeforeClose ブックを閉じるとき
3.Workbook_WindowDeactivate ブックが非アクティブ化(ほかのブックを選択など)
4.Workbook_WindowActivate ブックがアクティブ化(対象のシートとは限らない)
 ひょっとしたらいらないかも
5.Workbook_SheetActivate シートのアクティブ化
 今回のサンプルでは、Workbook_SheetDeactivateは使っていない
 イベントの発声順序を気にする処理であれば、こうしたイベントを利用する必要もあるが
 シートの選択変更があればSheetActivateが必ず発生するので充分とみました
 なお、このイベントは、このブックの中でだけ感知します。他のブックでのシート変更は感知しません
 また、このイベントはWorkbook_Openの直後に必ず起動されるので、Openイベントを汚しません
6.Workbook_SheetSelectionChange 選択セル範囲の変更
 このブックのいずれかのシートで選択セル範囲の変更が発生した場合に感知します

7.OnKeyを設定すべきかを判定するマクロ 単なるユーザーマクロ
 Workbook_Openで標準モジュールのグローバル変数に対象となるRangeを保存しており
 このマクロでは、引数の現在シートと現在Rangeを元に、OnKey設定の是非を判断します
 なお、OnKey設定時に起動するマクロは標準モジュールに記載します

●制限
1.複数のブックで同一のKeyを割り当てての実行はできません
 OnKeyはApplicationつまりExcel内で共通に監視されるものであることから
 複数のブックから同一のキーに割り当てると衝突が発生します
2.セルの重なりに判定にIntersectを使っているので、マウスで複数のセル範囲を選択した場合
 一部でも指定範囲にあればOnKey設定します。厳密な処理をするのであればここを改良してください。

●ThisWorkBookモジュールに
Option Explicit
'------------------------------------
'特定のブックの特定のシートの特定のセル範囲でEnterが押されたことを感知して
'指定した処理を実行します
'●制限
'複数のブックで同一のKeyを割り当てての実行はできません
'OnKeyはApplicationつまりExcel内で共通に監視されるものであることから
'複数のブックから同一のキーに割り当てると衝突が発生します
'セルの重なりに判定にIntersectを使っているので、マウスで複数のセル範囲を選択した場合
'一部でも指定範囲にあればOnKey設定します。厳密な処理をするのであればここを改良してください。
'------------------------------------
Private Sub Workbook_Open()
'------------------------------------
'ブックを開いたときに対象範囲を設定
'------------------------------------
    Dim Book_Name As String
    Dim Sheet_Name As String
    Dim StartCell As String
    Dim Endcell As String
    
    Book_Name = Me.Name
    Sheet_Name = Worksheets("Menu").Range("B1").Value
    '開始セル、終了セルは、A4:B6、4:6、A:BなどRange指定として存在しうる表現である必要があります
    StartCell = Worksheets("Menu").Range("B2").Value
    Endcell = Worksheets("Menu").Range("B3").Value
    
    'gbl_Target_Rangeは標準モジュールにPublic変数として定義
    Set gbl_Target_Range = Workbooks(Book_Name).Worksheets(Sheet_Name).Range(StartCell & ":" & Endcell)
    MsgBox gbl_Target_Range.Parent.Parent.Name & "." & gbl_Target_Range.Parent.Name & "." & gbl_Target_Range.Address
    
    'ブックを開いたときのOnKey設定は、保存時に選択されていたシートがアクティブになるイベントで起動されます
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'------------------------------------
'自分ブックが閉じられたときOnKeyの解除
'------------------------------------
    Debug.Print "ブックが閉じられました"
    Application.OnKey "{Enter}"
    Application.OnKey "~"
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'------------------------------------
'自分ブックがアクティブ化されたときOnKeyの設定判断
'------------------------------------
    Debug.Print "ブックがアクティブされました"
    Call Check_Cell(ActiveSheet, ActiveCell)
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'------------------------------------
'自分ブックが非アクティブ化されたときOnKeyの解除
'------------------------------------
    Debug.Print "ブックが非アクティブされました"
    Application.OnKey "{Enter}"
    Application.OnKey "~"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'------------------------------------
'自分ブックがアクティブ中にシートの選択が変更されたときOnKeyの設定判断
'------------------------------------
    Debug.Print "シートの選択が変更されました"
    Call Check_Cell(Sh, ActiveCell)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'------------------------------------
'自分ブックがアクティブ中に各シートのアクティブセルが変更されたときOnKeyの設定判断
'------------------------------------
    Debug.Print "セルの選択が変更されました"
    Call Check_Cell(Sh, Target)
End Sub

Private Sub Check_Cell(ByVal Sh As Object, ByVal Target As Range)
'------------------------------------
'OnKeyの設定判断
'------------------------------------
    '自分ブックか
    If gbl_Target_Range.Parent.Parent.Name <> Me.Name Then
        Debug.Print "ブックが違います"
        Application.OnKey "{Enter}"
        Application.OnKey "~"
    Else
        '自分シートか
        If gbl_Target_Range.Parent.Name <> Sh.Name Then
            Debug.Print "Sheetが違います"
            Application.OnKey "{Enter}"
            Application.OnKey "~"
        Else
            '指定セル範囲か
            Dim TempTarget As Range
            Set TempTarget = Application.Intersect(Target, gbl_Target_Range)
            If TempTarget Is Nothing Then
                Debug.Print "範囲外です"
                Application.OnKey "{Enter}"
                Application.OnKey "~"
            Else
                'テンキーと文字キーのEnterにイベントをセット
                Debug.Print "OnKeyが設定されました"
                '起動される"OnKey_EVT_MSG"は標準モジュールに定義
                Application.OnKey "{Enter}", "OnKey_EVT_MSG"
                Application.OnKey "~", "OnKey_EVT_MSG"
                Set TempTarget = Nothing
            End If
        End If
    End If
End Sub

●標準モジュールに
Option Explicit

'ThisWorkBookで参照する変数
Public gbl_Target_Range As Range

'ThisWorkBookで参照するSub
Sub OnKey_EVT_MSG()
    MsgBox ActiveCell.Address & "でEnterが押されました。"
End Sub

●補足
今回は対象範囲の設定に汎用性を持たせるため
Menuというシートに設定値を保存しておき
これをOpenイベントで取りに行っています

ここを改良すれば、複数のセル範囲に、様々なマクロを、様々なキーに割り付けることも可能でしょう
逆に、ユーザーにここを変更されてしまうと誤動作にもつながりますので、マクロの中に埋め込む必要もあるかもしれません

●おまけ
ThisWorkBookのWorkbook_SheetChangeイベントや、各シートのWorksheet_Changeイベントは
セルの値に変更があったときに発生するイベントです

今回はOnKeyによるEnter感知という、少々大げさな仕様になりましたが
同等のことが簡単に実現できる方法としては以下の通り


チェックしたい値を持つセルとは別のダミーセルを用意
そのシートのWorksheet_Changeイベントに実行させたいマクロを起動するよう記載

例えばA1セルに検索キーがあって、B1セルに1を入力したとして
Worksheet_Changeの引数にはTarget As Rangeがありますので
Target.AddressがB1で、Valueが1なら、A1のValueを取り出して処理する

これなら、このマクロは、このブックの、このシートの、特定のセルだけに反応しますので
誤爆は発生しにくいし、作成も簡単です
以上、みなさまの参考になれば

( ̄ー ̄)/~~ジャ
関連記事
スポンサーサイト



コメントの投稿

非公開コメント

おもちゃ箱
Count from 2010/01/14

にほんブログ村 バイクブログ ドゥカティへ


カレンダー
09 | 2019/10 | 11
- - 1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31 - -
最新記事
最新コメント
カテゴリ
月別アーカイブ
リンク
検索フォーム
RSSリンクの表示
ブロとも申請フォーム

この人とブロともになる

全記事表示リンク

全ての記事を表示する