我会说我不是VBA专家。我有一个VBA代码,可以在一个单元格中附加来自选定列表的多个值。该代码有效,但现在我想将代码应用于多个单元格。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$A$7" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & "," & Chr(10) & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
如何扩展代码以使用单元格范围
$A$7:$A$18
而不是"$A$7"
这样,以便我可以将VBA代码应用于Excel中的多个单元格。
更改
If Target.Address = "$A$7" Then
至
If Not Intersect(Target, Range("A7:A18")) Is Nothing Then
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句