Macro syntax

[alainfranco]

Hello,

Trying to replace a cell that could be emptied by a user by a formula.

Is am using this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("O24").Value = "" Then
Range("O24").Formula = "=IF(G3<>"Completed", "Project not Completed", "Enter Forecasted Budget at Completion")"


End If
End Sub



As you can imagine, its causing an error on the Range("O24").Formula = ....

Any idea how I can get this to work?

Thanks,

 

[jproffer]

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("O24").Value = "" Then
Range("O24").Formula = "=IF(G3<>""Completed"", ""Project not Completed"", ""Enter Forecasted Budget at Completion"")"
End If
End Sub

You can do this, but it will still allow your cell to be changed. If a user changes it to anything other than nothing, it will remain whatever they enter and your formula will be gone.

 

[My Aswer Is This]

A sheet change event script will only be activated when a manual change is made to a cell value.
Not when a formula causes a cell value to change.

 

[alainfranco]

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("O24").Value = "" Then
Range("O24").Formula = "=IF(G3<>""Completed"", ""Project not Completed"", ""Enter Forecasted Budget at Completion"")"
End If
End Sub

You can do this, but it will still allow your cell to be changed. If a user changes it to anything other than nothing, it will remain whatever they enter and your formula will be gone.

Hi,

Thanks, yup, that did it.... Its the double quotes I was forgetting!

Thanks, much appreciated!

 

[MARK858]

You also don't need the End If as you are only testing one condition so it can all go on the one line.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("O24").Value = "" Then Range("O24").Formula = "=IF(G3<>""Completed"", ""Project not Completed"", ""Enter Forecasted Budget at Completion"")"
End Sub