PDA

View Full Version : Pop-up Error Message



rojashan
06-30-2013, 06:14 AM
Hi,

Attached is my excel.



Here is what I'm hoping to accomplish.

If Q column is "Yes", T column is "No", U column is "Close" - a pop-up message will appear saying "Not yet resolved. Still needs to be actioned. Please review"

If Q column is "No", T column is "Yes", U column is "Close" - a pop-up message will appear saying "Not yet resolved. Still needs to be actioned. Please review"

Thanks.

snb
06-30-2013, 06:46 AM
=CHOOSE(WEEKDAY(D12),"C","A","B","A","B","A","B")

rojashan
06-30-2013, 06:47 AM
what?

rojashan
06-30-2013, 06:48 AM
I want the error message to appear when close is selected in U column? selected

Paul_Hossler
06-30-2013, 07:58 AM
You want to use the Worksheet_Change event to see if a cell in Col U has changed, and then check the Col Q and T cells in that row


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range, rData As Range

Set rData = Nothing
On Error Resume Next
Set rData = Intersect(Target, Me.Columns(21))
If rData Is Nothing Then Exit Sub

For Each rCell In rData.Cells
'col U
If rCell.Value = "Close" Then
'COl Q
If rCell.EntireRow.Cells(1, 17).Value = "Yes" Then
'col T
If rCell.EntireRow.Cells(1, 20).Value = "No" Then
Call MsgBox("Not yet resolved. Still needs to be actioned. Please review", vbCritical + vbOKOnly, "Data Check")
End If

'Col Q
ElseIf rCell.EntireRow.Cells(1, 17).Value = "No" Then
'col T
If rCell.EntireRow.Cells(1, 20).Value = "Yes" Then
Call MsgBox("Not yet resolved. Still needs to be actioned. Please review", vbCritical + vbOKOnly, "Data Check")
End If
End If
End If
Next
End Sub


These are your rules, but you might want to review them to make sure that all cases are covered.

For example, this only senses Col U changes, but can be extended to react to Col T and Q also

I added this to the Worksheet module where you had your Sub, but I suggest that when 'general purpose' sub (not an event handler) is used you add it to a Standard module, and not a Worlsheet.

Paul