質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.11%

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 676

Naoko_Coco

score 44

できるかできないかご教示ください。
VBAにてダブルクリックイベントを書いてます。
これは1シート毎に書かなくてはならないのでしょうか?
書くとしても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
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

+4

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/06/05 13:37

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

    キャンセル

  • 2019/06/05 14:29 編集

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

    キャンセル

  • 2019/06/05 16:59

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

    キャンセル

checkベストアンサー

+2

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

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


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

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関数を実行するといいでしょう。

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/29 11:45

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

    キャンセル

  • 2019/05/29 17:01

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

    キャンセル

  • 2019/05/30 14:44 編集

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

    どうも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さんからのアドバイスにある通りワークブックに用意されているワークシートイベントでも実現可能です。

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

    キャンセル

+2

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

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

ダブルクリックはアクティブなシートでしか発生しないので、シートは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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/24 11:03 編集

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

    キャンセル

  • 2019/05/24 11:23

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

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

    キャンセル

  • 2019/05/29 12:10

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

    キャンセル

+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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/24 09:13

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

    キャンセル

  • 2019/05/24 10:35

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.11%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる