jeudi 18 mai 2023

Worksheet_change not working anymore & trying to uncheck boxes when target changes

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