Copy code

[Hamster07]

Is there a way I can copy code across multiple worksheets without having to
open each worksheet code page and individually paste the code in.

 

[Don Guillett]

More detail and code?
Maybe have a look in the ThisWorkbook module

 

[Hamster07]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As _
Boolean)
Const WS_RANGE As String = "G2:G22"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Target.Interior.ColorIndex = 5 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 5
End If
Next
Cancel = True
End If
End Sub

Don,
Thanks for the reply.
The above code is to re-colour the selected cells when double clicked. I
have 77 worksheets and its a bit laborious copying the code to each sheet.
Thanks

 

[Don Guillett]

As I said, look in the ThisWorkbook module and restrict for sheet you don't
want used.

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

End Sub

 

[Gord Dibben]

Hamster

Don't copy it to all sheets.

Place this in the Thisworkbook module..........will operate on all sheets.

Remove any code you have in the sheet module.

Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "G2:G22"
If Not Intersect(Target, ActiveSheet.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Target.Interior.ColorIndex = 5 Then
Target.Interior.ColorIndex = xlNone
Else
Target.Interior.ColorIndex = 5
End If
Next
Cancel = True
End If
End Sub


Gord Dibben MS Excel MVP