View Full Version : Solved: BeforeDoubleClick Question

03-19-2008, 05:40 PM
The code below works great at the individual sheet level, but I want it to work at the entire workbook level (ThisWorkBook).

I tried to change the code to use the WorkbookEvents_SheetBeforeDoubleClickEventHandler, but this failed.

How can this code be extended to work at the workbook level?

' Code at the worksheet level
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range("A:F")) Is Nothing Then
With Target

If Target.Interior.ColorIndex = 6 And Target.Font.Bold Then

Target.EntireRow.Interior.ColorIndex = xlColorIndexNone
Target.EntireRow.Font.Bold = False

End If
End With
End If

Application.EnableEvents = True

End Sub

Thanks in advance for any support.

03-19-2008, 05:49 PM
In the sheet's code module, the keyword Me refers to that sheet. In ThisWorkbook, it refers to the whole workbook. Changing this line should set things right

If Not Intersect(Target, Sh.Range("A:F")) Is Nothing Then

The declaration is also off. For event code, I always use the dropdowns from the VB editor to get the finiky details right on that.
In ThisWorbook, the DoubleClick event is declared

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

Note the Sh argument (used above) that passes the sheet to the sub.

03-19-2008, 06:13 PM
Thanks! Your corrections saved me from adding this code to every sheet in the workbook!