VBA message box based on text being present

**LewisK** · 12-09-2014, 07:04 AM

Hi All,

Regular visitor first time poster here, can someone offer help with this:

I've got code for a message box popup for text within a cell if that is the only text in the cell, what I need is a popup box if the text within the cell contains a particular string of letters, specifically one of my customers names..

So the customers name is "ST CROLLES", but the cell will contain other text too..just need a popup if the text "ST CROLLES" is contained within the whole text entered into any cell in column H.

Hope I've explained what I need properly!

Many thanks in advance guys..

**Olly** · 12-09-2014, 07:13 AM

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, stMatch As String

    stMatch = "ST CROLLES"  'change this value as necessary

    For Each c In Target
        If c.Column = 8 And InStr(LCase(c.Value), LCase(stMatch)) > 0 Then
            MsgBox Chr(34) & stMatch & Chr(34) & " entered in cell " & c.Address(0, 0)
        End If
    Next c
End Sub

**LewisK** · 12-09-2014, 07:17 AM

Perfect! Many many thanks Olly! Top man!

**JOHN H. DAVIS** · 12-09-2014, 07:24 AM

FWIW:

Sub LewisK()
Dim i As Long
For i = 2 To Range("H" & Rows.Count).End(3).Row
    If Cells(i, "H") Like "*ST CROLLES*" Then
        MsgBox "Row " & i & " Column " & "H" & " has the word"
    End If
Next i
End sub

**LewisK** · 12-09-2014, 07:41 AM

Thanks John!

**JOHN H. DAVIS** · 12-09-2014, 07:55 AM

You're welcome. Glad to help out.

VBA message box based on text being present

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA message box based on text being present

Re: VBA message box based on text being present

Re: VBA message box based on text being present

Re: VBA message box based on text being present

Re: VBA message box based on text being present

Re: VBA message box based on text being present

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Conditional Formatting of an entire Row or Rows based on text found/present in any one cel

[SOLVED] Change cell colour based on text being present - conditional formatting?

[SOLVED] Auto Hiding rows based on range/data present or not present.

[SOLVED] Macro to sum based on certain text being present, range changes at variable rows

[SOLVED] Merge/unmerge based on whether specific text is present in a column

Bookmarks

Bookmarks

Posting Permissions