VBA代码中的多个Worksheet_Change事件 [英] Multiple Worksheet_Change events in vba code

问题描述

我在合并两个Worksheet_Change事件时遇到问题-请问一位专家可以给我一些建议吗?

I'm having problems merging two Worksheet_Change events - could I please get some advice from a guru?

该代码的目的是在给定小写字母的单元格范围内转换任何大写文本，但显然我不能有两个事件.

The aim of the code is to convert any uppercase text in the cell ranges given to lowercase, but obviously I can't have two events.

我尝试将它们都复制到同一Worksheet_Change中，但是Excel变得狂暴并崩溃了.

I've tried copying both into the same Worksheet_Change, but Excel goes berzerk and crashed.

范围1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ccr As Range
    Set ccr = Range("C6")
    For Each Cell In ccr
    Cell.Value = LCase(Cell)
    Next Cell
End Sub

范围2:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim acr As Range
    Set acr = Range("C9:G9")
    For Each Cell In acr
    Cell.Value = LCase(Cell)
    Next Cell
End Sub

非常感谢

推荐答案

像这样，您可以在同一事件中完成这两项操作

Like this you can do both the things in same event

为了避免出现竞争状况，必须在开始时添加 Application.EnableEvents = False .

You have to add Application.EnableEvents = False at the starting to avoid race condition.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False

    Dim ccr As Range, acr as Range

    Set ccr = Range("C6")
    For Each Cell In ccr
      Cell.Value = LCase(Cell)
    Next Cell

    Set acr = Range("C9:G9")
    For Each Cell In acr
      Cell.Value = LCase(Cell)
    Next Cell
 Application.EnableEvents = True

End Sub

这篇关于VBA代码中的多个Worksheet_Change事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！