Prevent sub from running

Question

In sheet1 (which I've Called "MainSheet") I have a sub in my VBA script that check the values of some cells whenever a cell is changed in this sheet. (one of the main actions that will occur when a cell is changed is modifying it's color, green for Cell's with a value, red for empty cells)

But now I've got some other sub's that also change cells (in the main sheet) but in this case I don't need (and don't want) VBA to check the cells and adapt the color to their values after every cell change. (annoying when editing a large amount of cells).

(I've already tried to put this sub in the "ThisWorkbook"part of VBA instead of the Sheet1(MainSheet) part, but unfortunately this made no difference at all).

Question one: is it possible to prevent this?

I also have a correlated problem with another sub that worth mentioning in the same question I think: In this sub a new sheet is created, named and filled with text from a .txt document. Then the sheet will be saved as new workbook, and the sheet will be deleted. (The name of the sheet equals the name it will get when it's saved, and varies ever new occurrence.) When I'm copying the .txt lines into this sheet one by one, the first sub I mentioned (the one editing cell color) is called. one of the first things happening in this sub is calling my MainSheet. When thin sub is finished the line copying sub will continue but will start pasting the lines in my Main Sheet. I tried to enter lines in this sub that select the sheet with variable name, but it keeps jumping to the MainSheet.

Question two: How do I prevent jumping to the MainSheet?

(Both questions probably could have the same solution.)

The sub that modifies the cell colours:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Integer
'Collor all cells green containing values, collor empty cells red.
''Starts automaticly after every cell change within this sheet
'Huidige Cell onthouden

If Not Intersect(Target, Range("A9:A29")) Is Nothing Then
        On Error GoTo bm_Safe_Exit3
        Application.EnableEvents = False
        If Intersect(Target, Range("A9:A29")).Cells.Count > 1 Then
            Application.Undo
            MsgBox "Please edit one cell at a time!"
        Else
            Dim newVal3 As Variant

            newVal3 = Target.Value
            Range("A9:A29").ClearContents
            Target.Value = newVal3
        End If
End If

bm_Safe_Exit3:
    Application.EnableEvents = True


Set myActiveCell = ActiveCell
Set myActiveWorksheet = ActiveSheet
Set myActiveWorkbook = ActiveWorkbook

Sheets("MainSheet").Select
Range("C5").Select
j = 0

Do While j < 6
    If ActiveCell.Offset(0, j).Value = "" Then
        ActiveCell.Offset(-1, j).Interior.Color = RGB(255, 0, 0)
            Else: ActiveCell.Offset(-1, j).Interior.Color = RGB(0, 255, 0)
    End If
    j = j + 1
Loop

'Terug naar de voormalig active cell
    myActiveWorkbook.Activate
    myActiveWorksheet.Activate
    myActiveCell.Activate

End Sub

Apparently, you allready know about `Application.EnableEvents` so if it is not enough, the **usual workaround is to create a Public or Global Boolean** that you **set in the sub that you are working** in and at the **very start of the other sub** that would be launch you use something like `If Boolean Then Exit Sub` to quit the procedure before doing any other actions. — R3uK, Nov 10 '15 at 08:34
You are enabling events too soon. This Worksheet_Change is trying to run on top of itself. Put the bn_Safe_Exit and Application.EnableEvents = True at the bottom just before `End Sub`. — , Nov 10 '15 at 08:37
I thought Application.EnableEvents would'nt work in this case, but it seem to do juist it! Thanks, the other sollution you've mentioned is a cleaver one aswell! — Cornelis, Nov 10 '15 at 08:44

score 1 · Accepted Answer · edited May 23 '17 at 10:27

Using .Select and .Activate is inefficient at the best of times; in a Worksheet_Change event macro it can really foul the waters.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False

    If Not Intersect(Target, Range("A9:A29")) Is Nothing Then
        If Intersect(Target, Range("A9:A29")).Cells.Count > 1 Then
            Application.Undo
            MsgBox "Please edit one cell at a time!"
            'intentionally throw an error; no more code run; sent to bm_Safe_Exit
            Err.Raise 0
        Else
            Dim newVal3 As Variant
            newVal3 = Intersect(Target, Range("A9:A29")).Cells(1).Value
            Range("A9:A29").ClearContents
            Intersect(Target, Range("A9:A29")).Cells(1) = newVal3
        End If
    End If

    Dim j As Integer
    With Worksheets("MainSheet").Range("C5")
        For j = 0 To 6
            If Not CBool(Len(.Offset(0, j).Value)) Then
                .Offset(-1, j).Interior.Color = RGB(255, 0, 0)
            Else
                .Offset(-1, j).Interior.Color = RGB(0, 255, 0)
            End If
        Next j
    End With

bm_Safe_Exit:
    Application.EnableEvents = True

End Sub

It isn't clear exactly what worksheet this is running under; I hope it isn't the MainSheet as I've used direct referencing to the cells on that worksheet.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Thanks! Now i don't only have two sollutions, but alwso know the root causes of the problem. — Cornelis, Nov 10 '15 at 08:55

Prevent sub from running

1 Answers1