PDA

View Full Version : [SOLVED] Help with Worksheet_Change



Ghost662
07-25-2015, 10:38 PM
Hello,

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

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.
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
07-26-2015, 05:10 AM
I would suspect that your two Application.EnableEvents statements need to be swapped.

Paul_Hossler
07-26-2015, 06:46 AM
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
07-27-2015, 09:09 PM
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
07-28-2015, 02:03 AM
That last application.screenupdating should probably be True rather than False (otherwise it's a pain).

Ghost662
07-28-2015, 07:52 AM
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
07-28-2015, 06:59 PM
Yes -- glad p45cal caught my overly enthusiastic copy/pasting