[已解决]请教一个事件程序的问题

雄鹰2013

当工作表1中的A列不断添加数据时，工作表2和工作表3中的A列能用数据有效性下拉出表1中的数据

最佳答案

月排行榜 / 总排行榜

qh8600

2015-6-13 23:21

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim ar, d
   
3.     Set d = CreateObject("scripting.dictionary")
   
4.     With Sheets("Sheet1")
   
5.         ar = .Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
   
6.     End With
   
7.     For i = 1 To UBound(ar)
   
8.         d(ar(i, 1)) = ""
   
9.     Next
   
10.     If Target.Column = 1 Then
    
11.         With Target.Validation
    
12.             .Delete
    
13.             .Add Type:=xlValidateList, Formula1:=Join(d.keys, ",")
    
14.         End With
    
15.     End If
    
16. End Sub

复制代码

下拉去重的

跳转到最佳答案楼层

雄鹰2013

用事件程序表示

thinkersky

'在工作表2和工作表3中选择A列单元格的事件
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    With Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=Sheet1!" & Sheet1.Range("A1").CurrentRegion.Address
    End With
End If
End Sub

qh8600

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim ar, d
   
3.     Set d = CreateObject("scripting.dictionary")
   
4.     With Sheets("Sheet1")
   
5.         ar = .Range("A1:a" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
   
6.     End With
   
7.     For i = 1 To UBound(ar)
   
8.         d(ar(i, 1)) = ""
   
9.     Next
   
10.     If Target.Column = 1 Then
    
11.         With Target.Validation
    
12.             .Delete
    
13.             .Add Type:=xlValidateList, Formula1:=Join(d.keys, ",")
    
14.         End With
    
15.     End If
    
16. End Sub

复制代码

下拉去重的