How to stop a sub from executing

[DAD]

Hi All,

I have an increasingly complex workbook that has numerous subs to carry out specific tasks. One of the subs (lets call it "CellChange") checks to see if any of a range of cells on a worksheet is changed, and if any cells in that range are changed, it will execute 4 other separate subs to perform their task.

However, I now have a situation where I do not want "CellChange" to execute IF another sub is pasting data into the range of cells that would normally force "CellChange" to execute. Is there a way to do that?

Thanks in advance

 

[MrIfOnly]

You could do something like this (note that the declaration of the boolean is outside any sub):

Dim cellchangecheck As Boolean

Sub change1()
    Sheets(1).Range("A1").Value = Sheets(1).Range("C1").Value
    cellchangecheck = True
End Sub


Sub CellChange()
    If cellchangecheck = True Then Exit Sub
    MsgBox "I am going to execute now"
End Sub

Regards,

CJ

 

[lhartono]

1. Add public boolean flag in public module so can be access from anywhere (maybe in Module1).

[COLOR=#0000cd]public booPaste as boolean[/COLOR]

2. in pasting sub, change the flag to true just before first pasting operation the change back to false afterwards.

[COLOR=#0000cd]sub Pasting

booPaste=true

[/COLOR][COLOR=#808080]..... pasting operations[/COLOR][COLOR=#0000cd]

booPaste=false

end sub[/COLOR]

3. in CellChange sub, add code to check the flag.

[COLOR=#0000cd]sub CellChange

if booPaste then 
     exit sub
end if

[/COLOR][COLOR=#808080].... cell change operations[/COLOR][COLOR=#0000cd]

end sub[/COLOR]

 

[DAD]

Hi guys,

Thanks for the tips. I will try both methods tonight and let you know how I go.

Cheers

Pete

 

[Fluff]

If your CellChange is an event (such as Private Sub Worksheet_Change(ByVal Target As Range)) then you can disable them like this

Sub Macro1()

Application.EnableEvents = False

' Your code here

Application.EnableEvents = True

End Sub

 

[DAD]

Hi Fluff,

Brilliant, thank you for that. The CellChange Event was indeed a Private Sub. I entered the code as you suggested, and now it works a treat. Thank you.

Pete

 

[Fluff]

Glad to help & thanks for the feedback