Code comonr to all worksheets

[Patrick C. Simonds]

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("G72041"), 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

 

[Per Jessen]

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("G72041"), 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

Hi

It's a worksheet event code, so it will only work if it's placed under
each worksheet.

Otherwise put the code in an ordinary module, and call the sub from
each sheet with a worksheet_change event code. But then you will also
need to rewrite the code as you can not use the Target object and Me
which is an object refering to the worksheet.

regards,
Per

 

[FSt1]

hi
"what am i missing"
first, this is worksheet code. it works on worksheet. it is NOT workbook
code. it does not work in a this workbook module. i don't even work in a
standard module.
go back to the person who wrote this for you and explain you delema.

also see this site
http://www.mvps.org/dmcritchie/excel/getstarted.ht
http://msdn.microsoft.com/office/un...ibrary/en-us/odc_xl2003_ta/html/odc_super.asp
http://www.exceltip.com/excel_links.html

regards
FSt1

 

[Tom Hutchins]

You can't use a Worksheet event in the ThisWorkbook module; it is for
Workbook events. The following code for ThisWorkbook works okay for me:

Public OldSht As Worksheet, OldCell As Range

Private Sub Workbook_Activate()
Set OldSht = ActiveSheet
Set OldCell = ActiveCell
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set OldSht = Nothing
Set OldCell = Nothing
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Call ChgToUpper(OldSht, OldCell)
Set OldSht = ActiveSheet
Set OldCell = ActiveCell
End Sub

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

If Target.Count = 1 And Not Application.Intersect( _
Sh.Range("G72041"), 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

Target is the cell just selected. You want to process the cell you just
left, so we use variables (OldSht and OldCell) to keep track of where we just
were. We assign them a value when the workbook is activated, update them
every time a different cell is selected, and free the memory they use when
the workbook is closed.

Hope this helps,

Hutch

 

[Dave Peterson]

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("G72041"), 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