Macro gives message box every time something is deleted

[jason90]

Hi All!

I have the below macro that used to work fantastically for me, i would have two columns, one for the keyword and one for the message i want to appear if that word is used
I would like to use it again but for some reason now when i put it into my workbook whenever i delete anything in the workbook i get a message box showing just a singular line "-"

if i remove the line of code for that message box the macro doesn't function at all, I've tried various ways of trying to get it to stop to no avail, please help!

FYI this is not my code and I'm very much an amateur at this

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Dim fnd As Range

    'Set the keyword range
    'Make sure the first column lists postcode
    'and second column with desiredinformation message
    Set rng = Range("Keywords")
   
    'DON'T enter anything apart from problems in this tab.
    'that we store the keywords
    If Sh Is rng.Worksheet Then Exit Sub
   
    'Use "Find" method to search in the first column of keyword range
    Set fnd = rng.Columns(1).Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
    If Not fnd Is Nothing Then
        MsgBox fnd.Cells(, 1).Value & " - " & fnd.Cells(, 2).Value, vbOKOnly + vbExclamation
    End If
End Sub

 

[offthelip]

Have you defined the named range 'Keywords'

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

My guess is that the 'Keywords' range has one or more blank rows.

 

[jason90]

Have you defined the named range 'Keywords'

yeah its all set

 

[jason90]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

My guess is that the 'Keywords' range has one or more blank rows.

thank you very much for your help, this has resolved it, i made the keywords area larger as i thought i could just add to it without having to extend it every time, is this a possibility?

 

[Peter_SSs]

i made the keywords area larger as i thought i could just add to it without having to extend it every time, is this a possibility?

Yes, just change this line near the end

If Len(fnd.Value) > 0 Then MsgBox fnd.Cells(, 1).Value & " - " & fnd.Cells(, 2).Value, vbOKOnly + vbExclamation

 

[jason90]

Yes, just change this line near the end

If Len(fnd.Value) > 0 Then MsgBox fnd.Cells(, 1).Value & " - " & fnd.Cells(, 2).Value, vbOKOnly + vbExclamation

Absolute hero, have a fantastic day!

 

[Peter_SSs]

Glad it worked for you. Thanks for the follow-up.