Preventing code continuing running
 

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("D9:P21").Select
Selection.ClearContents
Range("D24:P36").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

Preventing code continuing running
 

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("D9:P21").Select
Selection.ClearContents
Range("D24:P36").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

Preventing code continuing running
 

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.


"Bill" wrote:

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("D9:P21").Select
Selection.ClearContents
Range("D24:P36").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

Preventing code continuing running
 

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

"Bill" wrote:

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("D9:P21").Select
Selection.ClearContents
Range("D24:P36").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