Changing Target without re-triggering the Worksheet_Change event

Question

My code takes data entered into first sheet, and enters it into others.

If I leave out the shPD.Cells(tr, tc) = tv line, it converts all the other sheets to upper case before entering.

If I put the above line code in to change my target's value, it loops back to the beginning of the event over and over again. It will fix the case of the first sheet like I want, but never goes past that line of code to do the rest.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim thv As String 'Target header value
Dim tr As Integer 'Target row
Dim tv As String


'Any changes to the header rows are to be ignored
If Target.Row <= 2 Then Exit Sub
'If user selects an array, ignore also
If IsArray(Target) = True Then Exit Sub

'Set target header value and target row variables
thv = shPD.Cells(2, Target.Column).Value
tr = Target.Row
tc = Target.Column
tv = Target.Value


Select Case thv
Case "Length (in)", "Width (in)", "Height (in)"
    Target.Offset(0, 1) = Target * 25.4
Case "Length (mm)", "Width (mm)", "Height (mm)"
    Target.Offset(0, -1) = Target / 25.4
Case "Weight (lbs)"
    Target.Offset(0, 1) = Target * 0.453592
Case "Weight (kg)"
    Target.Offset(0, -1) = Target / 0.453592
Case "JS Product Number"
    If IsEmpty(Target) = False Then
    tv = UCase(tv)
    shPD.Cells(tr, tc) = tv

How can I get the first instance to update to UCase as well?

Answer 1

As per @Tim Williams comment, changing the Target will re-trigger the Worksheet_Change event and you run the risk for an endless loop.

The advise to use Application.EnableEvents = False is a good one though you may want to ensure that your application always switches it back on, even if something goes wrong...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorExit
    
    Application.EnableEvents = False
    
    
    Dim thv As String 'Target header value
    Dim tr As Integer 'Target row
    Dim tv As String
    
    
    'Any changes to the header rows are to be ignored
    If Target.Row <= 2 Then Exit Sub
    'If user selects an array, ignore also
    If IsArray(Target) = True Then Exit Sub
    
    'Set target header value and target row variables
    thv = shPD.Cells(2, Target.Column).Value
    tr = Target.Row
    tc = Target.Column
    tv = Target.Value
    
    
    Select Case thv
    Case "Length (in)", "Width (in)", "Height (in)"
        Target.Offset(0, 1) = Target * 25.4
    Case "Length (mm)", "Width (mm)", "Height (mm)"
        Target.Offset(0, -1) = Target / 25.4
    Case "Weight (lbs)"
        Target.Offset(0, 1) = Target * 0.453592
    Case "Weight (kg)"
        Target.Offset(0, -1) = Target / 0.453592
    Case "JS Product Number"
        If IsEmpty(Target) = False Then
        tv = UCase(tv)
        shPD.Cells(tr, tc) = tv

ErrorExit:
    Application.EnableEvents = True

End Sub