Global Worksheet_BeforeDoubleClick [SOLVED]

  • I have included the doubleclick worksheet event into my vba code. This private function calls another sub that opens a new worksheet and copies data onto it. My problem is that I want this new worksheet that I create using the doubleclick event, to also have the doubleclick event embedded in it. I'm not sure whether it is possible to make the Private Sub Worksheet_BeforeDoubleClick global, ie. active for an entire workbook. Another option I thought of, but wasnt sure of, was whether code to open the new sheet could also embed the Private Sub Worksheet_BeforeDoubleClick procedure in the new sheet?
    Any ideas would be greatly appreciated.

  • Hi Scoobinski

    The easiest way to do this is, open a new Workbook. Delete all but one sheet. Now put the needed code in the Private Module of that Sheet Object. Now save the Sheet as a Template. All you now need to do is, record a macro using that sheet and then use the code generated in your macro.

  • You already have a workable solution, but I just thought I'd add that it would be just as easy to use the SheetBeforeDoubleClick event in the Workbook object. i.e. in "ThisWorbook" you will find the event:

    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    End Sub

    This may produce a more maintainable solution. e.g. maybe not in your specific example, but if you had a workbook with 20 sheets that had identical doubleclick code that needed modified, this would be a pain to maintain.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!