On Modifying a Cell with VB Code, Excel crashes

[raabbasi]

Hello everyone

I am trying to modify the content of a cell after an input and exit the cell. I am using the code below with 3 different statements (one at a time), commented here.
As soon as I input the B2 cell, for example, and leave the cell, Excel crashes. Please let me know, what is the mistake.

Regards

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CurrCell As Range

' The variable KeyCell contains the cells; that will cause an alert when they are changed.
    Set CurrCell = Range("B2")

    If Not (CurrCell Is Nothing) Then
        On Error GoTo Err_Msg
            Range("B2").Select
'            Range("B2").Value = 0
'           CurrCell.Value = 0
'            Cells(2, 2).Value = 0
            MsgBox "Cell " & Target.Address & " has changed to " & CurrCell.Value
    End If
Err_Msg:
        Debug.Print Err.Description
End Sub

 

[Joe4]

Welcome to the Board!

You need to be careful with Worksheet_Change code. It runs automatically anytime is cell value is manually updated or updated by code.
But if your code itself is updating cells, then the changes it makes will call the code again, and if not careful, you can get caught in an endless loop!

The way to avoid this is to temporarily suspend event procedure code while your VBA code is making those updates, so the code doesn't keep calling itself.
You can do that like this:

            Application.EnableEvents = False
            Range("B2").Value = 0
            CurrCell.Value = 0
            Cells(2, 2).Value = 0
            Application.EnableEvents = True

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

While modifying a cell content, Excel crashes

Hello everyone I am trying to modify the content of a cell after an input and exit the cell. I am using the code below with 3 different statements (one at a time), commented here. As soon as I input the B2 cell, for example, and leave the cell, Excel crashes. Please let me know, what is the...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[raabbasi]

Application.EnableEvents = True

Thank you very much, Joe for the help. Have a good time and regards.

 

[Joe4]

You are welcome.
Did that solve your issue?