I am needing help with worksheet_change code that was working for a while but now is not recognizing changes after I've been trying to include more in the code (+2 lines).
Context: Table on each ws in a wb that serves a template that a user fills out. The table/template has drop-downs (dependent on prior drop-down), buttons (shapes), and various other worksheet changes related to column range updates.
The one I am focused on is a range (column B). This column does have data validation drop-downs attached to it. Previously, I was able to get excel to recognize any change made in this range--like being blank or changing to another drop down option-- and it would then clear out some following cells (in Col D:F) in the same row as a reset. These cleared cells also have DV drop-downs within them. Recently, I've been trying to uncheck some checkboxes located in additional columns at the end of this table if there were any in that same row. I added on to the code I wrote and no boxes unchecked and no col B changes were recognized at all.
Even as I worked backwards and ran the original version of the code I created, it now won't recognize any Col B (target) changes.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngB As Range
Set rngB = Range("B7:B28")
Dim chk As CheckBox
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If Not Intersect(rngB, Target) Is Nothing Then
Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Value = ""
Range(ActiveCell.Offset(0, 13), ActiveCell.Offset(0, 16)).chk.Value = False
For Each myCell In Intersect(Target, rngB)
If myCell.Value = "" Then
Range(myCell.Offset(0, 2), myCell.Offset(0, 4)).Value = ""
Range(myCell.Offset(0, 13), myCell.Offset(0, 16)).chk.Value = False
End If
Next myCell
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For reference, here is the original code that is no longer working either:
If Not Intersect(rngB, Target) Is Nothing Then
Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 4)).Value = ""
For Each myCell In Intersect(Target, rngB)
If myCell.Value = "" Then
Range(myCell.Offset(0, 2), myCell.Offset(0, 4)).Value = ""
End If
Next myCell
End If
If there is anything else I can provide, please let know. Would appreciate some assistance.
Aucun commentaire:
Enregistrer un commentaire