Prevent change but allow overwrite

[davie1982]

Hello.

I have the following VBA code within my worksheet:

If Target.Count > 1 Then Exit Sub   'this means only reacts to single cell changes
If Target.Column <> 5 Then Exit Sub 'Target column 5 (E) no more no less
{IF statement}

I wish to add to this so that in the cells in column D, if there is a formula, it cannot be changed, but if one overwrites or deletes the formula it's ok. For example...

The formula is below.

=IF(E215="u","u",IF(F215="","",IF(ISERROR(VLOOKUP(F215,Check!$A:$B,2,0)),"u",VLOOKUP(F215,Check!$A:$B,2,0))))

This formula relies with what is in column E. However, I have someone who likes to mess around and changes all the "u" into "?". I'd like that to be prevented so to avoid future issues with the spreadsheet when it's using the formula.

However.
It doesn't matter if this is overwritten with data manually typed. So a big no to editing the formula, but an ok to overwriting and deleting it. Can you help me please?

 

[davie1982]

I'm doing some trial and error work right now, but i've come up with an issue.

If Target.Column <> 4 Then Exit Sub
Select Case IsNumeric(Target.Formula)
    Case ""
    Exit Sub
    Case Is = False
    Application.Undo
    MsgBox "Do not edit formula"
    End Select

So I have it checking if the formula is numeric. In the case that numbers are entered over the formula, or the formula is deleted, that is fine, but when the formula is edited it will loop. The msgbox will keep popping up and it will keep undoing. Also if a letter is typed into the cell, overwriting the formula, then it will revert back to the loop again.

 

[davie1982]

And i found my own solution which seems to solve everything.

If Target.Column <> 4 Then Exit Sub
Select Case IsNumeric(Target.Formula)
    Case Is = False
    Target.FormulaR1C1 = "=IF(RC[1]=""u"",""u"",IF(RC[2]="""","""",IF(ISERROR(VLOOKUP(RC[2],Check!C1:C2,2,0)),""u"",VLOOKUP(RC[2],Check!C1:C2,2,0))))"
    Exit Sub
    End Select

So what happens is.. if the formula is numeric. for example, they type "101" then 101 will display. If they change the formula or put a letter in there however, the formula will be put back in how it was. I do sense a little lag when that happens.. it lasts for about a second then Excel can be used again.. i think it's looping around for that second then giving up.

If anyone could clean this up for me and give me tips on how to improve such a thing, let me know.

My own solution.