PDA

View Full Version : Solved: One Script for Multiple Worksheets



Opv
04-28-2011, 10:13 AM
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:


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

'my code
End Sub

Is there a way to create a single BeforeDoubleClick code in a regular Module and make it apply to all sheets?

BrianMH
04-28-2011, 10:20 AM
use Workbook_SheetBeforeDoubleClick in the workbook module.

Kenneth Hobs
04-28-2011, 10:25 AM
As Brian said, in the VBE's ThisWorkbook object add it:
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

Opv
04-28-2011, 10:51 AM
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.


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?

Paul_Hossler
04-28-2011, 10:57 AM
Maybe



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



Paul

BrianMH
04-28-2011, 10:59 AM
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)


change the top

Opv
04-28-2011, 11:01 AM
Maybe



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


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
04-28-2011, 11:03 AM
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

change the top

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

Opv
04-28-2011, 12:18 PM
I'm not sure what I did but the error is gone now. Thanks for all the help.