Changing Target Case-CodePudding

Dealing with a worksheet change event. 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 runs fine, and converts all the other sheets to upper case before entering, just doesn't fix the first instance, obviously. But as soon as I put the above line code in to change my target's value, it keeps looping 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. Here's the code up to that point.

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, without crashing my code? Thanks

CodePudding user response：

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