Insert code
 

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.

Insert code
 

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.

Insert code
 

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.