CODEWITHSUNDEEP
excel
User247365
User247365

Reputation: 685

Conditional pop up window in Excel

I am looking to display a pop up window in an excel sheet based on the content of certain cells. For example, if the contents of either B3, C3, D3 or F3 are not equal to "Option A" or "Option B", then display the pop up window with the text "User Notification". I do not want to use data validation to create a dropdown with "Option A", "option B" and "Other" because I want the user to be able to describe that "Other" in the designated cell (B3, C3, D3 or F3), but I want to notify them of what they need to describe. Is there a macro that can accomplish this? Or better yet, some function of excel that I may not know about that doesn't require a macro?

Upvotes: 0

Views: 502

Answers (1)

Gary's Student
Gary's Student

Reputation: 96771

Place the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellsOfInterest As Range
    Dim Rint As Range
    Dim r As Range
    Dim s As String

    Set CellsOfInterest = Range("B3:D3, F3")
    Set Rint = Intersect(Target, CellsOfInterest)

    If Not Rint Is Nothing Then
        For Each r In Rint
            s = r.Value
            addy = r.Address(0, 0)
            If s <> "Option A" And s <> "Option B" And s <> "" Then
                MsgBox "User Notification " & r.Address(0, 0)
            End If
        Next r
    End If
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Upvotes: 1

Related Questions