Question

excelr的 VBA 运行时错误1004"save"作用于对象"-wrokbook"时失败，求高手解决！

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.Color = xlNone
ActiveCell().EntireRow.Interior.Color = RGB(255, 255, 160)
If Target.Address = "$B$5" Then
Dim dic As Object
Dim arr, i%
arr = Sheet4.Range("B8:B" & Sheet4.[B100000].End(3).Row)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then dic(arr(i, 1)) = ""
Next
With Range("$B$5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(dic.keys, ",")
End With
End If
If Target.Address = "$C$5" Then
arr = Sheet4.Range("A7:A" & Sheet4.[A100000].End(3).Row)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then dic(arr(i, 1)) = ""
Next
With Range("$C$5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(dic.keys, ",")
End With
End If
If Target.Address = "$D$5" Then
arr = Sheet4.Range("A7:A" & Sheet4.[A100000].End(3).Row)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then dic(arr(i, 1)) = ""
Next
With Range("$D$5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(dic.keys, ",")
End With
End If
End Sub

Answer 1

代码中并没有使用 save 方法，应该不会出现这个错误。
我检验了一下代码，没有错误。只是代码设计得不够精练，重复代码太多。另外存在一个小小问题，举例来说：arr = Sheet4.Range("A7:A" & Sheet4.[A100000].End(3).Row)，如果A6以下没有数据，程序设置的数据有效性系列就背离了你的设计意图，会设置为A7上面的最近一个数据。