There is no event under thisworkbook called Worksheet_Change--and you can't just
make them up.

But there is a workbook_sheetchange event that you can tie into.

Notice that the me that used to point a worksheet (when it was under a worksheet
module) has changed to sh (and is passed to the subroutine.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler:

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("G7:P2041"), Target) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If


If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("N7:N2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("R7:R2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("T7:T2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("V7:V2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub



"Patrick C. Simonds" wrote:

I want the code below to work on all worksheets in the workbook so I placed
it in "ThisWorkbook" but it does not work. It does work if I copy the code
to each worksheet, so what am I missing?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler:

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("G7:P2041"), Target) Is Nothing Then
Application.EnableEvents = False
Target.Value = UCase$(Target.Value)
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("N7:N2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("R7:R2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("T7:T2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

If Target.Count = 1 And Not Application.Intersect( _
Me.Range("V7:V2041"), Target) Is Nothing Then
Application.EnableEvents = False
If IsNumeric(Target.Value) And InStr(Target.Value, ":") = 0 _
And Len(Target.Value) < 5 Then
Target.Value = Format$(Target.Value, "00\:00")
Else
Target.Value = UCase$(Target.Value)
End If
End If

ErrHandler:
Application.EnableEvents = True
End Sub

--

Dave Peterson