Help me use my script on all sheets or at least the active sheet.

  • I am really scratching my head at this. I have been using this script for years to allow me to input times by using a decimal instead of a colon for quicker entry. It works perfectly. As you can see my range is defined in the second line and is used in the first If/Then. Now I want to add some new tabs to the worksheet and use this same scrip on all sheets or at least on the active sheet. I have banged my head on the desk for hours now with different combinations of ActiveSheet, Range and others to no avail. Please can some one help?


    Private Sub Worksheet_Change(ByVal Target As Range)

    Const WS_RANGE As String = "b3:u268"


    On Error GoTo ws_exit:

    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

    With Target

    If IsNumeric(.Value) And .Value >= 0.01 Then

    .Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) / 24

    .NumberFormat = "[h]:[m]"

    End If

    End With

    End If


    ws_exit:

    Application.EnableEvents = True

    End Sub

  • Make these two changes I marked and then move the macro to the ThisWorkBook module. It will then work on every sheet.