|
本帖最后由 雪上人家 于 2011-10-23 10:00 编辑
想在B5:B10用VBA产生数据有效性的序列,不在错在哪,在论坛和百度上找了很久,也没办法解决。有高手能解决吗?
本帖最后由 zjdh 于 2011-10-23 10:47 编辑
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- ActiveSheet.Unprotect
- If Not Application.Intersect(Target, Range("$B$5:$B$10")) Is Nothing Then
- Dim conn, Arr, i&, myPath$, bb$
- myPath = ThisWorkbook.Path & "\源数据\生成序列的源数据.xls"
- Set conn = CreateObject("adodb.connection") '定义对象
- conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & myPath
- Arr = conn.Execute("select * from [生成序列的源数据$]").getrows
- For i = 3 To UBound(Arr, 2)
- If Arr(0, i) <> "" Then bb = bb & Arr(0, i) & ","
- Next
- bb = Left(bb, Len(bb) - 1) '去除最后一个多余的逗号
- With Target.Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
- Operator:=xlBetween, Formula1:=bb '增加新的数据有效性
- End With
- Set conn = Nothing
- End If
- ActiveSheet.Protect
- End Sub
复制代码
|
|