检测到不明确的名称:Worksheet_change
问题描述:
我正在尝试将第二个代码添加到单个工作表,并不断收到“检测到不明确的名称”错误。意识到我需要结合这两个代码,但遇到麻烦。这里有两个代码,一个在另一个之下:检测到不明确的名称:Worksheet_change
Private Sub Worksheet_Change(ByVal Target As Range)
'are changes made within answer range?
Set isect = Application.Intersect(Target, Range("Answers"))
If Not (isect Is Nothing) Then
For Each chng In Target.Cells
'Get row number
startY = Impact.Range("Answers").Row
targetY = chng.Row
row_offset = (targetY - startY) + 1
rating_type = Impact.Range("Impacts").Cells(row_offset, 1)
If rating_type = "Major/V.High" Then cols = 16711884
If rating_type = "Significant/High" Then cols = 255
If rating_type = "Important/Moderate" Then cols = 49407
If rating_type = "Minor/Low" Then cols = 5287936
If rating_type = "" Then cols = 16777215
Impact.Range("Ratings").Cells(row_offset, 1).Interior.Color = cols
Impact.Range("Impacts").Cells(row_offset, 1).Interior.Color = cols
Next chng
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' 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 = "$C$2" 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 & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
希望有人知道如何在两个为了绕过这个错误结合。
在此先感谢!
答
根据我的评论,您可以跟踪以下示例代码中所示的多个范围内的更改。
Private Sub Worksheet_Change(ByVal Target As Range)
'Exit the sub if more than one cells are changed at the same time
If Target.CountLarge > 1 Then Exit Sub
'Disable the event so that if the code changes the cell content of any cell, the code is not triggered again
Application.EnableEvents = False
'Error handling to skip the code if an error occurs during the code execution and enable the events again
On Error GoTo ErrorHandling
'Change event code will be triggered if any cell in column A is changed
If Not Intersect(Target, Range("A:A")) Is Nothing Then
MsgBox "The content of a cell in colunm A has been changed."
'Change event code will be triggered if any cell in column C is changed
ElseIf Not Intersect(Target, Range("C:C")) Is Nothing Then
MsgBox "The content of a cell in colunm C has been changed."
'Change event code will be triggered if any cell in column E is changed
ElseIf Not Intersect(Target, Range("E:E")) Is Nothing Then
MsgBox "The content of a cell in colunm E has been changed."
End If
ErrorHandling:
Application.EnableEvents = True
End Sub
在工作表模块上不能有两个单独的更改事件代码。如果必须跟踪多个范围内的更改,则可以在IF和ElseIf块中的一个更改事件代码中加入条件。由于错误的不明确名称是不言自明的,即您有两个具有相同名称的宏。 – sktneer
您有两个名称相同的程序。在这一点上,这个名字是为事件处理程序保留的。在一个代码模块中不能有两个具有相同名称的过程。如果您现在需要在工作表更改上做更多事情,请将新代码复制到现有的“Worksheet_Change”处理程序中。只有你知道它应该如何结合已经存在的代码。 – GSerg