Copy code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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
 
Back
Top