MsgBox that returns the information of another cell if a cell in a range contains certain value

Nexus125 · Feb 25th 2020

Hi everyone!

I am trying to create a MsgBox that will pop up a notification for all cases I must follow up in the day. Please see the requirements:

1. Should run automatically once I open the spreadsheet.

2. If the text PLEASE SEND NOTIFICATION" is present on range CE3:CE1000, then the MsgBox should inform returning the reference numbers (in this example Ref 01 and Ref 05)

3. The message box should contain an exclamation mark

The final message would be then "Please send notification regarding Ref 01 and Ref 05".

Thank you in advance.

Joy

Mumps · Feb 25th 2020

It's hard to work with a picture. Can you post a copy your file (de-sensitized if necessary)?

Nexus125 · Feb 25th 2020

I can't share the file. It would be something like this one on the attached though.

The important bits are the ones in blue.

Mumps · Feb 25th 2020

I assume that you want the message to pop up when you open the workbook.

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Save the workbook as a macro-enabled file. Close it and the re-open it.

Code

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, ws As Worksheet, SN As String
    Set ws = Sheets("ASSESSMENTS")
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With ws.Cells(1).CurrentRegion
        .AutoFilter 4, "SEND NOTIFICATION"
    End With
    For Each rng In ws.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible)
        If SN = "" Then SN = rng.Offset(, -3).Value Else SN = SN & ", " & rng.Offset(, -3).Value
    Next rng
    MsgBox "Please send notification regarding " & SN
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Display More

Nexus125 · Feb 26th 2020

Thank you so much for your help Mumps

Mumps · Feb 26th 2020

You are very welcome.

MsgBox that returns the information of another cell if a cell in a range contains certain value

Participate now!

Share

Tags