Thread: VBA to create a message box when a value is entered into a cell

Dear all

I am creating a complaints DB in Excel and would l like some VBA.

Database Tab

I would like that when the user enters a "Closure Date" col AA10:AA107, this automaticaly populates the "Status" col F10:F107 as closed. The Status col contains a DV List otherwise i'd use an if statment.

If this is too tricky then i was thinking of -- if a date value is entered in cells AA10:AA107 then having a msg box with a reminder to change the status.

I have found some code which i am trying to tinker with...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
   Set rng = Range("AA10:AA107")
   If Not Intersect(Target, rng) Is Nothing Then
       If rng = "HOW DO I PUT ANY VALUE HERE" Then
        MsgBox "Cell " & _
         rng.Address & " = PLEASE remember to change the complaint status to CLOSED"
       End If
   End If

   Set rng = Nothing

End Sub

Any help appreciated.

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim rCell As Range
    Set rng = Range("AA10:AA107")
    If Not Intersect(Target, rng) Is Nothing Then
        For Each rCell In Intersect(Target, rng).Cells
            If Len(rCell.Value) > 0 Then
                MsgBox "Cell " & _
                       rCell.Address & " = PLEASE remember to change the complaint status to CLOSED"
                ' OR USE
                ' Application.enableevents = false
                ' Cells(rcell.row, "F").Value = "Closed"
                ' Application.enableevents = true
            End If
        Next rCell
    End If

    Set rng = Nothing

End Sub

Hi R - thanks v much for responding.

I inserted the above code and voila, the msg box worked perfectly. Thanks

Regarding

' OR USE
' Application.enableevents = false
' Cells(rcell.row, "F").Value = "Closed"
' Application.enableevents = true


Please forgive me but could you explain further how to use this code?

Thanks v much

Just uncomment it and comment out your message box line. I just put it there to give you the option to autopopulate the Closed bit.

many thanks. Perfect