Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wait for Pivot Refresh before continuing Code Execution | Excel Programming | |||
Finish one section of code before continuing | Excel Programming | |||
continuing code on another line? | Excel Programming | |||
Continuing print code problem | Excel Programming | |||
Preventing Duplicates using VBA (code) | Excel Programming |