MS Excel How to create a warning or validation box based upon text in a particular cell

Issue: Currently there is an IF formula to Enter text here if the "Status" in Column F = "On Hold"; Then there is conditional formatting for if the Comments Column is >0, then Highlight Yellow.

The PROBLEM is that once text is auto entered or a user types in the cell, the formula is automatically erased.

Is there a way to have a Warning Box associated with the Comments Column, so anytime a corresponding "On Hold" status is there a box will pop up informing the user to please enter comments?

This thread is directed towards fixes for issue 6- the description of the issues are also located on the Desc tab of the workbook. Issues 7-11 issues will be following in subsequent posts.

Thank you in advance for your assistance.
Test-Report-v2.xlsm
ckwillGWUAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
Do you want to lock the user at the cell until comments are posted?

This just prompts the user to enter comments, then lets the user continue.  See the last 2 if statements.  It prompts the user if the yellow comment is not there because it has been overwritten:

in Sheet1 codepage:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value <> PreviousValue Then
        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
            Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
    End If
    If Target.Count > 1 Then Exit Sub 'not handling at the moment - this is if more than one cell is changed simultaneously
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
        If Target.Value = "On Hold" And Left(Range("I" & Target.Row).Formula, 1) <> "=" Then 'prompt user
            MsgBox "Please Enter comments for On HOLD status"
        End If
    End If
End Sub

Open in new window


One, perhaps better alternative could be to erase the comments and put the formula back in there?  What do you think?

See attached.

Dave
Test-Report-v2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ckwillGWUAuthor Commented:
Thanks Dave, this works perfectly. I just modified the warning to read "Enter or Update" the comments. I'd rather leave the old comments for a running trail with each account.

Again, thanks again. 1 down :)
0
ckwillGWUAuthor Commented:
Very quick and accurate response on the first go! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.