Insert code

  • Thread starter Thread starter MAX
  • Start date Start date
M

MAX

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