多列触发change事件如何操作

simba999

已经实现的是A列选择相应的状态则在B、C、D输出相应的时间，但现在业务增加还需要新增一列下拉菜单选择在后面显示对应状态的时间。

求助各位大神。怎么在E列变动的同时在F列显示时间。


Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
n = Target.Column
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
  For Each Rng In Application.Intersect(Target, Columns(1))
    If Rng = "选1" Then
      Cells(Target.Row, "B").Value = Date
    End If   
    If Rng = "选2" Then
      Cells(Target.Row, "C").Value = Date
    End If   
    If Rng = "选3" Then
      Cells(Target.Row, "D").Value = Date
    End If
  Next
Application.EnableEvents = True
End Sub

多列change触发.zip (18.27 KB, 下载次数: 5)

2019-11-8 00:22 上传

点击文件名下载附件

liulang0808

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. r = Target.Row
   
3. n = Target.Column
   
4.   
   
5. 
   
6. If Target.Column = 1 Then
   
7.     Application.EnableEvents = False
   
8.    
   
9.       For Each Rng In Application.Intersect(Target, Columns(1))
   
10.         If Rng = "选1" Then
    
11.           Cells(Target.Row, "B").Value = Date
    
12.         End If
    
13.         
    
14.         If Rng = "选2" Then
    
15.           Cells(Target.Row, "C").Value = Date
    
16.         End If
    
17.         
    
18.         If Rng = "选3" Then
    
19.           Cells(Target.Row, "D").Value = Date
    
20.         End If
    
21.    
    
22.      
    
23.       Next
    
24.     Application.EnableEvents = True
    
25. Else
    
26.     If Target.Column = 5 Then
    
27.         Application.EnableEvents = False
    
28.         
    
29.           For Each Rng In Application.Intersect(Target, Columns(5))
    
30.             If InStr("12345", Rng.Value) Then
    
31.                 Cells(Rng.Row, Val(Rng.Value) + 5) = Date
    
32.             End If
    
33.          
    
34.           Next
    
35.         Application.EnableEvents = True
    
36.     End If
    
37. End If
    
38. End Sub
    

复制代码

liulang0808

附件内容供参考。。。。。。

renkangjizhen

Private Sub Worksheet_Change(ByVal Target As Range)
r = Target.Row
n = Target.Column
Set rngs = Range([a:a], [e:e])

If Target.Column <> 1 And Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False

  For Each Rng In Application.Intersect(Target, rngs)
    If Rng = "选1" Then
      Target.Offset(0, 1).Value = Date
    End If
   
    If Rng = "选2" Then
      Target.Offset(0, 2).Value = Date
    End If
   
    If Rng = "选3" Then
      Target.Offset(0, 3).Value = Date
    End If


  Next
Application.EnableEvents = True

End Sub
在本人电脑上经试验通过