CODEWITHSUNDEEP
vbaexcelcell
Ella
Ella

Reputation: 51

Keeping cell unchanged under specified conditions

I am looking for way to keep a formula in a cell every time another cell is active. 

Private Sub Worksheet_Change(ByVal Target As Range)

     If ActiveSheet.Range("AL2").Value = 1 Then
          ActiveSheet.Range("AK14").Value = ActiveSheet.Range("AL8").Value
     Else
     End If
 End Sub

So if Cell AL2 is equal to 1 (so my desired active cell) I want to have a certain value in cell AK14. If cell AL2 is NOT equal to 1 I want to just keep value in AK14 unchanged (so for example somebody can overwrite it).

At the moment Excel seems to get lost with the second part: if AL2 = 0 and I am getting an error.

If I need two conditions, do I just put another If?

Private Sub Worksheet_Change(ByVal Target As Range)

     Application.EnableEvents = False
     If Range("AL2").Value = 1 Then Range("F11").Value = Range("AK7").Value
     Application.EnableEvents = True

 End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

     Application.EnableEvents = False
     If Range("AL2").Value = 2 Then Range("J11").Value = Range("AL7").Value
     Application.EnableEvents = True

 End Sub

so I want to have those two macros..

Upvotes: 3

Views: 54

Answers (1)

Vityata
Vityata

Reputation: 43585

When you change value in a cell, in a Worksheet_Change event, you should disable the events. Otherwise, it starts calling itself:

Private Sub Worksheet_Change(ByVal Target As Range)

     Application.EnableEvents = False
     If Range("AL2").Value = 1 Then Range("AK14").Value = Range("AL8").Value
     Application.EnableEvents = True

 End Sub

Then, as a next step it is really a good practice to use Error-catcher here with the .EnableEvents:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo Worksheet_Change_Error

    Application.EnableEvents = False
    If Range("AL2").Value = 1 Then Range("AK14").Value = Range("AL8").Value
    Application.EnableEvents = True

    On Error GoTo 0
    Exit Sub

Worksheet_Change_Error:

    Debug.Print "Error " & Err.Number & " (" & Err.Description & ") "
    Application.EnableEvents = True

End Sub

Upvotes: 2

Related Questions