Solved: One Script for Multiple Worksheets

[Opv]

My brother has sent me a worksheet which contains 24 (more or less) worksheets. He's wanting some BeforeDoubleClick" actions to be added for each worksheet. (The same actions would apply to all of the worksheets.) I know how to do this on an individual worksheet, for example:

[vba]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'my code
End Sub
[/vba]
Is there a way to create a single BeforeDoubleClick code in a regular Module and make it apply to all sheets?

[BrianMH]

use Workbook_SheetBeforeDoubleClick in the workbook module.

[Kenneth Hobs]

As Brian said, in the VBE's ThisWorkbook object add it:
[VBA]Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "You doubleclicked in Sheet: " & Sh.Name & vbLf & "Target Range: " & Target.Address
End Sub
[/VBA]

[Opv]

Thanks. I think you guy have got me on the right tract. However, I'm receiving an error so there is still something wrong in my code.

[VBA]
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target

If .Interior.ColorIndex = 1 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 1
End If
If .Font.ColorIndex = 6 Then
.Font.ColorIndex = 0
Else
.Font.ColorIndex = 6
End If
If .Font.Bold = True Then
.Font.Bold = False
Else
.Font.Bold = True
End If
Cancel = True
End With
End Sub


Private Sub Workbook_SelectionChange(ByVal Target As Range)
With Cells
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
.Font.Bold = False
End With
End Sub

Any thoughts?

[/VBA]

[Paul_Hossler]

Maybe

[VBA]

Private Sub Workbook_SelectionChange(ByVal Target As Range)
With Target
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
.Font.Bold = False
End With
End Sub

[/VBA]

Paul

[BrianMH]

[VBA]Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
[/VBA]

change the top

[Opv]

Maybe

[vba]

Private Sub Workbook_SelectionChange(ByVal Target As Range)
With Target
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 0
.Font.Bold = False
End With
End Sub

[/vba]
Paul

Thanks. I made that change but still getting the error. The error is actually showing to be in the first subroutine. The message indicates that the procedure declaration does not match the description of the event or procedure.

[Opv]

[vba]Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
[/vba]
change the top

Thanks. tried that change anyway but it didn't remove the error.

[Opv]

I'm not sure what I did but the error is gone now. Thanks for all the help.