Excel Shuts Down After Running VBA Code

[PatHay]

Hi, for a few years now I have used a lot of VBA to achieve various things in Excel, but it is true to say that my in depth understanding of the code is minimal. Mostly, I have managed to resolve various issues along the way with Googles help and also from this forum which has been totally invaluable.

I have hit an issue now re the code below, which puts the value True into the range of cell(s) required, but then Excel crashes/quits and just shuts itself down. What am I missing in the code please to stop this from happening?

================================================
Private Sub Worksheet_Change(ByVal Target As Range)


Dim FndRng As Range
Dim cell As Range

Set FndRng = Range("BA1:BA20")

For Each cell In FndRng
If cell.Value = "True" Then

cell.Offset(0, 3) = cell.Value

End If

Next cell

End Sub
=============================================

Any help here will be very much appreciated. Thank you.

 

[jolivanes]

Your code will never stop because every time it changes a cell value, the code will be triggered again by the "Worksheet_Change".
It is also recommended not to use "cell" as it is a word used by the system. Use "c" or "ce" or even "cel".

 

[Joe4]

You can temporarily disable events to prevent the changes that your code is making to trigger the code to run again like this:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim FndRng As Range
Dim cell As Range

Set FndRng = Range("BA1:BA20")

For Each cell In FndRng
If cell.Value = "True" Then
    Application.EnableEvents = False
    cell.Offset(0, 3) = cell.Value
    Application.EnableEvents = True
End If

Next cell

End Sub

You should always do that when you have "Worksheet_Change" event procedure code making more data changes/updates.

 

[PatHay]

Ah, thank you. I will definitely remember that Cell naming advice, makes sense now you mention it. And can understand re the Worksheet_Change looping issue. I will adapt things and se how it all goes. Thank you.

 

[PatHay]

I have amended the code to - and this works exactly as required, no crashing.. well happy. Really appreciated your advice, guidance and very swift response.
=================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim FndRng As Range
Dim cll As Range

Set FndRng = Range("BA1:BA20")

For Each cll In FndRng
If cll.Value = "True" Then
cll.Offset(0, 3) = cll.Value
End If

Next cll

End Sub

=======================

 

[Joe4]

I have amended the code to - and this works exactly as required, no crashing.. well happy. Really appreciated your advice, guidance and very swift response.
=================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim FndRng As Range
Dim cll As Range

Set FndRng = Range("BA1:BA20")

For Each cll In FndRng
If cll.Value = "True" Then
cll.Offset(0, 3) = cll.Value
End If

Next cll

End Sub

=======================

Just be aware what that is doing - your code is going to run EVERY time you select a cell anywhere on your sheet.
That could negatively effect sheet performance, as it is usually unnecessary to run the code that often.
A "Worksheet_Change" would be better, if you heed the advice I gave you.

Are you manually entering the values of "True" in the range BA1:BA20?
If so, then you really do not need to loop through the range anytime any cell is changed or any cell is selected.
You can simply run the code when a value in that particular range is changed, and only run the code against that cell.

That code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FndRng As Range
    Dim cell As Range

'   See if any cells updated in range BA1:BA20 (if not, exit sub)
    Set FndRng = Intersect(Range("BA1:BA20"), Target)
    If FndRng Is Nothing Then Exit Sub

'   Loop through cells just changed in range BA1:BA20
    For Each cell In FndRng
        If cell.Value = "True" Then
            Application.EnableEvents = False
            cell.Offset(0, 3) = cell.Value
            Application.EnableEvents = True
        End If
    Next cell

End Sub

This code will be far more efficient that the other codes, and only run on the cells that were changed in BA1:BA20, and nothing else.

 

[PatHay]

Thank you for this, is very much appreciated and understand the logic behind this.

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[PatHay]

I meant to answer your question re the input of 'True' into the target cell range - which is not a manual input by a user, but changes status when a formula is met to make the cell value True.

On further testing, the new VBA code works, but only when True is manually inputted into a cell in the range BA1:BA20 - but not when the cell value becomes True as a result of a formula action. I assume there is some further tweaking to do to the code to make it run as intended. Am way out of my depth re that, any ideas please? Thank you.

 

[Joe4]

The "Worksheet_Change" event procedure only automatically fires when a cell is manually updated.
There is a "Worksheet_Calculate" event procedure, which automatically fires when any cell is re-calculated on the sheet. However, there is a caveat to that -- it cannot tell which cell changed, only that SOME cell SOMEWHERE on the sheet was re-calculated.

But there may be other "tricks" we can use in your case, depending on the details.
What exactly do those formulas in cell BA1:BA20 look like?
Can you post the exact formula in cell BA1?