Preventing code continuing running

[Guest]

I use the code to update the sheet. My problem is that when I update cells
other than the Target.Address of "$M$4" the sheet wants to run the code. What
adjustments can i make to make the code only run when cell M4 is changed?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Update As Long
Dim Period As Date
Dim UPeriod As Date
Update = MsgBox("Do you want to initialize the period?", vbYesNo, "Schedule")

' Turn off Screen Updating and disables events
Application.ScreenUpdating = False
Application.EnableEvents = False

If Target.Address = "$M$4" Then
If Update = vbYes Then

'Clear designated cells
Range("D921").Select
Selection.ClearContents
Range("D2436").Select
Selection.ClearContents
Range("D9").Select

'Updates Period
Period = Range("M4").Value
UPeriod = Period
Range("M4").Value = UPeriod

'Updates Dates
Dim Cell As Range, j As Long
j = 13
For Each Cell In
Range("B9,B11,B13,B15,B17,B19,B21,B24,B26,B28,B30,B32,B34,B36")
Cell.Value = Range("M4").Value - j
Cell.NumberFormat = "dd-Mmm-yyyy"
j = j - 1
Next
End If
End If

line_end:

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


Thanks
Bill

 

[Guest]

Hi Bill,

If I understand you correctly, you want to fire this only if $M$4 is
changed. This might be what you're looking for:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Update As Long
Dim Period As Date
Dim UPeriod As Date

If Target.Address = "$M$4" Then
Update = MsgBox("Do you want to initialize the period?", vbYesNo,
"Schedule")

If Update = vbYes Then

'Turn off Screen Updating and disables events
Application.ScreenUpdating = False
Application.EnableEvents = False

'Clear designated cells
Range("D921").Select
Selection.ClearContents
Range("D2436").Select
Selection.ClearContents
Range("D9").Select

'Updates Period
Period = Range("M4").Value
UPeriod = Period
Range("M4").Value = UPeriod

'Updates Dates
Dim Cell As Range, j As Long
j = 13
For Each Cell In
Range("B9,B11,B13,B15,B17,B19,B21,B24,B26,B28,B30,B32,B34,B36")
Cell.Value = Range("M4").Value - j
Cell.NumberFormat = "dd-Mmm-yyyy"
j = j - 1
Next
End If
End If

line_end:

'**this next line isn't necessary! Since you're exiting the procedure, it
resets.
'Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Regards,
GS

 

[Guest]

The only portion of the code that runs when the Target is not M4 is

Update = MsgBox("Do you want to initialize the period?", vbYesNo, "Schedule")

Just test the address first.

If Target.Address <> "$M$4" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

If MsgBox("Do you want to initialize the period?", _
vbYesNo, "Schedule") = vbYes Then

<rest of your code>
End If


Since the If test for the address is all on one line, the second End If at
the end of the code will need to be deleted.

 

[Guest]

Sorry - didn't scroll down far enough to see this has already been answered.