Preventing formulas being entered into cells via VBA (with added msgbox)

[M1111]

Hi,

I can't seem to get my code to pick up a Worksheet_Change event for when a cells has a formula.

This is what I have so far (the top part is to prevent pasting of formulas, I have highlighted the relevant code in red).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


Dim Rng As Range
Dim c As Range


If Intersect(ActiveCell, Range("B6:B25")) Is Nothing Then


Exit Sub


        ElseIf Not Intersect(ActiveCell, Range("B6:B25")) Is Nothing And Application.CutCopyMode = xlCopy Then


            Application.EnableEvents = False


                Application.Undo
                Target.PasteSpecial Paste:=xlPasteValues
            
                Application.EnableEvents = True


[COLOR=#ff0000][B]        ElseIf Intersect(ActiveCell, Range("X6:X25")) Is Nothing Then[/B][/COLOR]

[COLOR=#ff0000][B]Exit Sub[/B][/COLOR]

[COLOR=#ff0000][B]        ElseIf Not Intersect(ActiveCell, Range("X6:X25")) Is Nothing Then[/B][/COLOR]

[COLOR=#ff0000][B]        Application.EnableEvents = False[/B][/COLOR]

[COLOR=#ff0000][B]        For Each c In Target.Cells[/B][/COLOR]
[COLOR=#ff0000][B]            If c.HasFormula Then[/B][/COLOR]
[COLOR=#ff0000][B]            Application.Undo[/B][/COLOR]
[COLOR=#ff0000][B]            MsgBox "You must not enter a formula"[/B][/COLOR]
[COLOR=#ff0000][B]            End If[/B][/COLOR]
[COLOR=#ff0000][B]        Next[/B][/COLOR]
[COLOR=#ff0000][B]End If[/B][/COLOR]

[COLOR=#ff0000][B]Application.EnableEvents = True[/B][/COLOR]

[COLOR=#ff0000][B]End Sub[/B][/COLOR]

Thanks

 

[M1111]

Managed to fix this one myself, here's the code. I changed it to a Worksheet_Calculate:

Private Sub Worksheet_Calculate()

Dim c As Range


        If Intersect(ActiveCell, Range("X6:X25")) Is Nothing Then
        
Exit Sub


        ElseIf Not Intersect(ActiveCell, Range("X6:X25")) Is Nothing Then
        
        Application.EnableEvents = False
    
        For Each c In Range("X6:X25")
            If c.HasFormula = True Then
            Application.Undo
            MsgBox "You must not enter a formula"
            End If
        Next


Application.EnableEvents = True
End If
End Sub