Hello
I have an excel file with 31 sheets and I have a code where I want to insert
it in these sheets. How can I write this code once and appears in all the 31
sheets.
This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance.

Max,

There is a global worksheet change, put 1 copy of the code there. In VB
editor, double click 'ThisWorkbook' and paste the code below in

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"MAX" wrote:

Hello
I have an excel file with 31 sheets and I have a code where I want to insert
it in these sheets. How can I write this code once and appears in all the 31
sheets.
This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance.

Modify the event type.

Enter it once in Thisworkbook module to run on whichever sheet is active.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub


Gord Dibben MS Excel MVP

On Fri, 5 Feb 2010 11:49:02 -0800, MAX
wrote:

Hello
I have an excel file with 31 sheets and I have a code where I want to insert
it in these sheets. How can I write this code once and appears in all the 31
sheets.
This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A7:A68")) Is Nothing Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target = Left(Format(Target.Value, "0000"), 2) & ":" & _
Right(Format(Target.Value, "0000"), 2)
Application.EnableEvents = True
End If
End If
End Sub

Thanks in advance.