VBA ダブルクリックイベントのコードを一つにする方法？

Question

できるかできないかご教示ください。
VBAにてダブルクリックイベントを書いてます。
これは１シート毎に書かなくてはならないのでしょうか？
書くとしても5枚コピペすればよいだけなので、なんてことはないんですが
5シート分のモジュールができるのはあまり美しくないなぁと思い。
15シートのうちの5シート分に同じダブルクリックイベントを発生させたいのです。
できる様であれば、そのやり方もご教示ください。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim jan As String
Dim name As String
Dim i As Long
Dim r As Long
Dim sh As Worksheet
Dim rng As Range

Set sh = Worksheets("couponSku")
Set rng = ActiveCell
'    B列を選んでいればJanを取得
    If rng.Column = 2 And rng.Row >= 47 Then
       jan = Target.Address(False, False)
       jan = Range(jan).Value
       r = sh.Range("A1").End(xlDown).Row
       For i = 2 To r
           If jan = sh.Cells(i, 1).Value Then
                jan = sh.Cells(i, 2).Value
           End If
       Next
       Call Google_serch(jan)
'    C列を選んでいればクーポン名取得
    ElseIf rng.Column = 3 And rng.Row >= 47 Then
        name = Target.Address(False, False)
        name = Range(name).Value
        Call Google_serch(name)
    End If
        
End Sub

Answer 1

ワークブックにもダブルクリックイベントありますよね。
今のワークシートに書いてる処理をまるっとワークブックの方のダブルクリックイベントに持っていったらどうでしょうか。

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
’ 今までの処理をコピペ
End Sub

ただこれだけだと「15シートのうち5シートのみ」の要件は満たせませんから、イベント内冒頭でActiveSheetの名前で判断すればよいかと思います。
本格的な実装にするのであれば他の回答にあるようにクラスを使うことになります。

Comments

[hatena19]

今回の場合、これがベストの解決法ですね！頭固くなってるわ！

[ttyp03]

まったくスルーされた。悲しい(T_T)

[hatena19]

もっと+をあげたいのですが + ボタンは一回しか押せない(T_T)

[ttyp03]

hatena19氏のおかげで傷は癒えましたｗ

[jawa]

今回の要件だとこれが一番手っ取り早いですよね。

私が紹介したユーザークラスの方は、フォームに同じ入力チェックをするコントロールをたくさん配置するときとか、ファイルダイアログをひらく参照ボタンなんかをたくさん配置するときなどによく利用しますね。

>hatena19さん
マイナスは通知されないので、マイナスとプラスを交互に押せばttyp03さんはたくさんプラスが付いた気になれますよ(違

[Naoko_Coco]

ttyp03さん
ごめんなさい！！
これが一番簡単でしたね。
ありがとうございます。
こっちに変更します。
いつもアドバイス、ご回答ありがとうございます。

[ttyp03]

ようやく気づいてもらえた！
方法はいろいろありますので、作りやすくメンテナンスも容易なものをお選びください。

[Naoko_Coco]

ttyp03さん
いつもありがとうございます。

Answer 2

すでにいくつか回答は出ていますので、重箱の隅をつつく話ですがいくつか気になった点を記載しておきます。

Target から Worksheet は取得できる

Rangeオブジェクトには、そのオブジェクトが属しているワークシートを示すWorksheetプロパティがあります。
ワークシートを取得したい場合は、イベントの引数のTargetからTarget.Worksheetで取得した方が良いと思われます。

Worksheets()やActiveSheetと違い、ちゃんと型も明示されていますので扱いやすく、処理としても堅牢になると思われます。

BeforeDoubleClick イベントでも Target.Count が1とは限らない

BeforeDoubleClick イベントでもTargetには複数のセルが含まれる可能性はあります(複数のセルを選択した状態で他のソフトにアクティブを移し、Excelのウィンドウをダブルクリックすると発生しやすい。せっかちな人だとよく起きる)。

質問に記載の処理では、Targetに複数のセルが含まれる場合うまく動作しないため

If Target.CountLarge <> 1 Then Exit Sub

などをイベント処理の最初に追加しておくと良いと思います。

遠回りなコード

jan = Target.Address(False, False)
jan = Range(jan).Value

name = Target.Address(False, False)
name = Range(bame).Value

上記のコードは以下ではだめでしょうか？

jan = Target.Value

name = Target.Value

Comments

[ExcelVBAer]

複数セルのダブルクリックが出来るなんて初めて知りました！

[Naoko_Coco]

imihitoさん
アドバイスありがとうございます。
target.valueで値とれたんですね！
また、最初にIF文も追加しました。
ありがとうございます！

Answer 3

今回の要件なら、標準モジュールに共通モジュールを書いて、それを各シートから呼び出せはいいかと思います。

もう少し複雑なものなら、クラスモジュールを使うメリットも出てきますが、ダブルクリックの場合は、
それほどメリットはないと思います。

ダブルクリックはアクティブなシートでしか発生しないので、シートはActiveSheetでOK。
ダブルクリックすると、そのセルがアクティブになるので、セルは ActiveCell でOK。

ですので、それを使えば標準モジュールに共通に記述できます。

標準モジュール

Public Sub CellDoubleClick()
    Dim sh As Worksheet
    Dim rng As Range
    
    Set sh = ActiveSheet
    Set rng = ActiveCell
    
    MsgBox sh.Name & "シートの" & rng.Address(False, False) & "セルをダブルクリックしました。"
    '実際には sh と rng を使った処理を記述
End Sub

ダブルクリックで処理を実行したいシートのモジュール

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Call CellDoubleClick
End Sub

---

クラスモジュールを使う場合

シートが動的に追加されたり、削除されたりする場合で、追加されたシートにもダブルクリックでイベント処理したい場合は、クラスモジュールにするメリットもでてきます。

その場合は、Collection にクラスを格納するようすると、自由に追加、削除できます。

クラスモジュール clsWs

Option Explicit

Private WithEvents ws As Excel.Worksheet

Public Property Let MySheet(ByVal c As Excel.Worksheet)
    Set ws = c
End Property

'シートのダブルクリックイベント
Private Sub ws_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim Sh As Worksheet
    Dim rng As Range
    
    Set Sh = ActiveSheet
    Set rng = ActiveCell 'Target でもOK
    
    MsgBox Sh.Name & "シートの" & rng.Address(False, False) & "セルをダブルクリックしました。"
    '実際には sh と rng を使った処理を記述
End Sub

ThisWorkbook モジュール

Option Explicit
Public MySheets As Collection
'ブックを開く時
Private Sub Workbook_Open()
    Dim cWs As clsWs
    Dim i As Long

    Set MySheets = New Collection
    For i = 1 To ThisWorkbook.Worksheets.Count
        Set cWs = New clsWs
        cWs.MySheet = ThisWorkbook.Worksheets(i)
        MySheets.Add cWs, ThisWorkbook.Worksheets(i).CodeName
    Next

End Sub
'シートの追加時
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim cWs As clsWs
    Set cWs = New clsWs
    cWs.MySheet = Sh
    MySheets.Add cWs, Sh.CodeName
End Sub
'シートの削除前
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    MySheets(Sh.CodeName).MySheet = Nothing
    MySheets.Remove Sh.CodeName
End Sub

Comments

[Naoko_Coco]

hatena19さん
ありがとうございます。
でもjawaさんと同じくダブルクリックしても何も起きません。
メッセージボックス出てこないです。。。
完全にTestExcel作成して自分のコードは一切書かず、まるっとコピペしてます。
クラスモジュールに記述するやり方でやってます。

[hatena19]

クラスモジュールの名前は、clsWs にしてますか。

あと、ThisWorkbook モジュールのコードは間違いなく、ThisWorkbook に記述してますか。
してあれば、ブックを開く時に、Workbook_Open が実行されて、ダブルクリックイベントがシートに追加されるはずです。

[Naoko_Coco]

ありがとうございます。
無事にできました。
遅くなってすいません。

Answer 4

ワークシートイベントをフックするユーザークラスを作成し、ブックを開いた時に各シートをこのクラスにも格納してあげれば可能です。
「ユーザークラスの作成」というと敷居が高く感じられますが、実際のコードはそこまで複雑ではありません。

まずは試してみて、うまく動いたら、なぜこれで目的の動作が実現できたのか調べて理解を深めていけばいいと思います。

---

例えば以下のようなユーザークラスを作成します。

clsWs

Private WithEvents ws As Excel.Worksheet
'プロパティ設定で対象シートを受け取る
Public Property Let MySheet(ByVal c As Excel.Worksheet)
    Set ws = c
End Property

'シートのダブルクリックイベント
Private Sub ws_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    MsgBox "DBL!"
End Sub

次に共通モジュールで、このクラスの変数と、そこに各シートを格納する関数を用意します。

Module1

'ユーザークラスの配列
Public MySheets() As New clsWs

'現在あるシートをユーザークラスに格納
Public Sub SetMySheets()
    ReDim MySheets(ThisWorkbook.Sheets.Count)

    Dim i As Integer

    'ブック内の全シートをユーザークラスに格納する　⇒これでワークシートをダブルクリックしたときにclsWsのダブルクリックイベントが実行される
    For i = 1 To ThisWorkbook.Sheets.Count
        MySheets(i).MySheet = ThisWorkbook.Sheets(i)
    Next
End Sub

このSetWorkSheets関数でclsWsに格納されたワークシートでダブルクリックすると、clsWsのダブルクリックイベントに記載した処理が実行されることになります。
必要ならブックを開いた時や、シートが追加されたタイミングでSetWorkSheets関数を実行するといいでしょう。

まずはがんばってみてください。

Comments

[Naoko_Coco]

jawaさん
回答ありがとうございます。
クラスモジュールについて教えてください。
jawaさんのコードをまるっとコピペして実行すると、
Public MySheets() As New clsWs
の部分で、『ユーザ定義型は定義されてません。』のエラーが出ます。
clsWsの部分をクラスモジュールに書いて、Module1の内容をそのまま書いてます。
書く場所が間違っているのでしょうか？
また、1枚目のSheetに対してはダブルクリックで実行しますが、他のSheetをダブルクリックしても何も起こりません。

[jawa]

すみません、説明が足りなかったようです。

clsWsの内容をクラスモジュールに、Module1の内容を標準モジュールに記載します。

そしてclsWsを記述したクラスモジュールの名前をclsWsにします。
※おそらくこれが漏れているためのエラーと思われます。

ここまでできていればSetMySheets関数を実行することでダブルクリックイベントをフックできるようになると思います。

お試しください。

[Naoko_Coco]

ありがとうございます。
クラスモジュールの名前を変更すればよかったんですね！
が、しかし・・・エラーはでなくなりましたが、ダブルクリックしても何も起きないです。。。メッセージボックスも出てきません。
昨日の時点でシートに対しダブルクリックイベント記述したままだったので、実行できましたが今日はダブルクリックイベントコメントアウトにしてます。
なのでjawaさんのコードのみで試してます。

[jawa]

SetMySheets関数は実行されましたか？
していなければ、一度「マクロの表示」からSetMySheets関数を実行してみてください。

SetMySheets関数を実行すると、その時点でブックに含まれている全てのシートをclsWsクラス型の変数に格納します。
シートイベントをフックできるようになるのはそれ以降です。

ブックを開いたときに自動で割り当てたいようでしたら、workbook_openイベントでSetMySheets関数を呼ぶようにしておくといいです。

[Naoko_Coco]

遅くなってすいません。
他の作業をしていたため、後回しになってしまいごめんなさい。
で、できました！
ありがとうございます。

[Naoko_Coco]

もう少し教えてください。
OpenイベントでSetMySheetsを呼び出してるんですが、時間が経過すると外れてしまうようなんです。
なので、ダブルクリックイベントの先頭にSetMySheetsを呼び出すように書いてるんですが、それでもダブルクリックイベント自体が実行されません。
どうしたら常にダブルクリックイベントを呼び出すことができるようになりますでしょうか？

[jawa]

私もこれまで何度も使ってきた手法で、そういったことは起きたことがなかったのですが。。

どうもVBAのPublic変数は、アプリケーションの終了まで保証されるものでは無い(つまり意図せず解放される可能性がある)ようです。

私もこの質問をうけて調べてみて、初めて知りました。。おそろしい話です。

参考↓
https://seesaawiki.jp/wasure_matsu_program/d/Excel%20VBA%20%3A%20%A5%B0%A5%ED%A1%BC%A5%D0%A5%EB%CA%D1%BF%F4%A4%AC%BE%A1%BC%EA%A4%CB%BD%E9%B4%FC%B2%BD%A4%B5%A4%EC%A4%EB%BB%F6%A4%D8%A4%CE%C2%D0%B1%FE.

とりあえず標準モジュールで宣言したワークシート格納用の配列変数などをThisWorkbookに移せば解放されなくなるかも知れません。

手元で現象確認できないのであやふやな情報で申し訳ありませんが、ご確認ください。

なお、今回のようにワークシートのイベントであれば、ttyp03さんからのアドバイスにある通りワークブックに用意されているワークシートイベントでも実現可能です。

ユーザークラスの方が幅広く使えますが、それも選択肢のひとつでしかありませんので、今回はワークブックのイベントで実装するというのも手だと思います。