Having trouble making VBA code work for more than 1 worksheet

[robnsd]

I have a workbook with 65 tabs. I found some code that changes a cell to green when double clicked. I pasted it behind one of the worksheets and it works great. However, I don't want to have to paste it to all 65 sheets. I know there is a way to make it work on all the workbook's sheets without copying it behind each one but I can not figure it out. I tried copying it behind "This workbook" to no avail. Perhaps the code is only meant for an individual sheet. I'm using Excel 2007. The code is below.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors

' If the cell is clear
If Target.Interior.ColorIndex = xlNone Then

' Then change the background color to red
Target.Interior.ColorIndex = 4

' Else if the cell background color is red
ElseIf Target.Interior.ColorIndex = 4 Then

' Then clear the background
Target.Interior.ColorIndex = xlNone

End If

' This is to prevent the cell from being edited when double-clicked
Cancel = True

End Sub

[protonLeah]

Workbook_SheetBeforeDoubleClick(By ....

[ThirtyTwo]

protonLeah is correct

so, remove the code from the sheet module, open up the module for ThisWorkbook

Please Login or Register  to view this content.

[robnsd]

Thanks that worked great!