Help with Worksheet_Change

[Ghost662]

Hello,

I'm having two issues with "Private Sub Worksheet_Change(ByVal Target As Range)":

1. It asks me to select a Macro, but I'm already telling it which Macro to run. The change event is in the appropriate sheet and the Macro is in Module 1.
2. There's a For Loop (For Each) in the Macro that's in Module 1 that affects multiple cells (i.e. targets) and, when I run the change event (and manually select the Macro from my first problem), it will only run for the current cell and then loop back to the change event.. instead of to the beginning of the For Loop.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("Q9:T50")) Is Nothing Then
        Availability
    End If


End Sub

Below is the Macro. This is an old version with a few issues, but the loop setup and enableevents setup is the same in the current version. I suspect the issue is with the actual loop or the enableevents settings, but please let me know if it's not and I'll send the current code.

Sub Availability()    
    Application.EnableEvents = True
    Application.ScreenUpdating = False
    
    Dim totalRange As Range, totalCell As Range
    Dim kdRange As Range, kdCell As Range
    Dim jpRange As Range, jpCell As Range
    Dim tcRange As Range, tcCell As Range
    Dim assRange As Range, assCell As Range
    
    FinalRow = Cells(Rows.Count, 16).End(xlUp).Row
    
    Set totalRange = Sheet1.Range("Q9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
    Set kdRange = Sheet1.Range("Q9:Q" & FinalRow).SpecialCells(xlCellTypeVisible)
    Set jpRange = Sheet1.Range("R9:R" & FinalRow).SpecialCells(xlCellTypeVisible)
    Set tcRange = Sheet1.Range("S9:S" & FinalRow).SpecialCells(xlCellTypeVisible)
    Set assRange = Sheet1.Range("T9:T" & FinalRow).SpecialCells(xlCellTypeVisible)
    
    For Each kdCell In kdRange
        If kdCell.Offset(0, 1) = "Yes" Or kdCell.Offset(0, 2) = "Yes" Then
            kdCell = "N/A"
        ElseIf kdCell.Offset(0, 1) = "No" And kdCell.Offset(0, 2) = "No" Then
            kdCell = "Yes"
            kdCell.Offset(0, 3) = "Davidson, Ken"
            MsgBox "You're our last hope!"
        Else
            MsgBox "Finish coding"
        
        End If
        
        Next kdCell
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
End Sub

Thank you in advance for your help!

[p45cal]

I would suspect that your two Application.EnableEvents statements need to be swapped.

[Paul_Hossler]

Like p45cal said: You need to turn them off (=False) then do things to the sheet and then turn them on again (=True)

If it were me, I'd move them to the event handler (just personal preference) unless there were a reason to keep them where they were. Probably even the .ScreenUpdating also

Private Sub Worksheet_Change(ByVal Target As Range) 
     
    If Not Intersect(Target, Range("Q9:T50")) Is Nothing Then 
        Application.EnableEvents = False
        Availability 
     Application.EnableEvents = True
    End If 
     
End Sub

[Ghost662]

Thank you both! It works! I get the enable events part, but I have no idea why this fixed the problem where it kept asking me for a macro. But I'm glad works!

[p45cal]

That last application.screenupdating should probably be True rather than False (otherwise it's a pain).

[Ghost662]

I added screen updating to the handler set the pre-macro one to False and the post-macro one to True. I think that's correct and it works well! Thanks again man!

[Paul_Hossler]

Yes -- glad p45cal caught my overly enthusiastic copy/pasting