excel 更改目标大小写

vsdwdz23 于 2022-11-18 发布在其他

关注(0)|答案(1)|浏览(163)

处理工作表更改事件。我的代码将输入到第一个工作表中的数据输入到其他工作表中。如果我省略了shPD.Cells(tr, tc) = tv行，它运行良好，并在输入之前将所有其他工作表转换为大写，只是显然没有修复第一个示例。但当我将上面的代码放入以更改目标的值时，它会一遍又一遍地循环回到事件的开头。它会像我希望的那样修复第一张表的大小写，但不会越过这行代码去做剩下的事情。下面是到这一点的代码。

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

我怎样才能让第一个示例也更新到UCase，而不崩溃我的代码？谢谢

excel

来源：https://stackoverflow.com/questions/74467640/changing-target-case

1条答案

按热度按时间

vwkv1x7d1#

根据@Tim威廉姆斯的评论，更改Target将重新触发Worksheet_Change事件，您将面临无限循环的风险。
使用Application.EnableEvents = False的建议是一个很好的建议，尽管您可能希望确保您的应用程序总是将其重新打开，即使出现了错误...

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

赞(0）回复(0）举报 2022-11-18

我来回答

excel 更改目标大小写

1条答案

相关问题

热门标签

最新问答