VBA message popup based upon condition

[Andrew B]

Hi All,
I have an Excel workbook I use at what is basically a receiving desk. It has two password protected sheets labeled Received and Expedited. Each sheet has 12 columns. The cells of the first row of each sheet are locked and are used as column headers. The cells in each row below are available for editing and are used for data entry. Users need to be able on occasion to re-edit these cells to correct mistakes. Sometimes, however, a user will inadvertently overwrite a cell that already contains information. For this reason, when a user selects a cell that already contains data I would like a popup box to appear with a warning message (Something like this: Caution! You are about to change a logged entry. Do you wish to continue? Y/N.). If yes, I would like the popup box to close and the active cell to remain available for editing. If no, I would like the nearest empty cell in the row to become the active cell. Here's the catch: I only need the message to appear if a cell has has been selected that already contains data. If a user selects an empty cell , then nothing should happen. The types of data that the cells in my worksheets contains are date, time, text, and numbers. The numbers are Fedex, UPS and USPS Tracking numbers. All cells except for those containing date and time are formatted to treat data (including numbers) as text. All of the cells contain fill colors.

Can anyone out there help me?

I tried using Data Validation, but the message would appear regardless of whether the cell contained data or not. Besides that, Validation did not offer the other options that I have mentioned here.

I am currently using Office 2000 Professional, but will be upgrading to Office 2010 very soon.

Thanks to all read this post and attempt a solution. Even greater thanks to those who reply!

Regards,
Andrew

 

[Andrew B]

PS: I tried to go back and edit after posting but was unable to do so. I wanted to disclose that I have posted this question also at

http://www.ozgrid.com/forum/showthread.php?t=151000&p=543853#post543853

 

[tweedle]

Here's what I come up with.
I didn't find a reasonable possibility to check it on cell selection; it just yields too many false pop ups that seem too intrusive to be productive.
So this version waits until the cell is edited, and then asks the user if they are sure.
If yes, then the change is committed (could be logged)
If No, then the change they made is rolled back to the original value

'In a Module
Public preVal As Variant

In the sheet code for each sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column < 13 And preVal <> "" Then
        msg = "Caution! You are about to change a logged entry. Do you wish to continue? Y/N.)"
        retval = MsgBox(msg, vbYesNo, "CAUTION!!!")
        If retval = vbYes Then
            'Log it?
        Else
        Application.EnableEvents = False
            Target.Value = preVal
            Range(Cells(Target.Row, 100), Cells(Target.Row, 100)).End(xlToLeft).Offset(0, 1).Select
        Application.EnableEvents = True
    End If
End If
End Sub

Note: this is only valid for single-cell attempts to edit.
If a multi-cell select and clear contents for example is selected, then this code would not apply and would allow the user to procede.

 

[Andrew B]

First, Tweedle, thank you so much for replying to my post so quickly. Doubly impressed because you did so even though I had committed the faux pas of cross-posting, which I am very sorry about (I am new to these forums and have much to learn).

I opened my VBA Project and pasted "Public preVal As Variant" in module 1 and the Private Sub Worksheet Change in each worksheet, but when I went to the worksheet and edited the contents of a cell that already contained data, no message box appeared. I did not get any error messages either. Not sure what I'm doing wrong.

Further disclosure, I did get a reply on the other forum that contained a code that worked. However, I must say that I really prefer your idea about not having the message display until the cell has been edited (it does make more sense that way). So I am certainly willing to work through this if you are still willing to try.

If not, I understand. Either way, I can't thank you enough.

Sincerely,
Andrew

 

[tweedle]

Well that's because I neglected to post a very important 1-liner:
This would go in the code for each worksheet:
Stores the value of the cell for what it is before the edit, and used in testing if the cell was blank or not before the user got there.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    preVal = Target.Value
End Sub

 

[tweedle]

I think this actually should have been;

preVal = ""
If Target.Cells.Count = 1 Then preVal = Target.Value

though I can't remember why.
(Sorry -recreating @ work as home version is...well, at home.)
.
Can also give them information to allow an informed decision:

        msg = "Caution! You are about to change a logged entry. Do you wish to continue? Y/N.)"
        [COLOR=seagreen]msg = msg & vbCrLf & "Changing " & preVal & " to " & Target.Value[/COLOR]
        retval = MsgBox(msg, vbYesNo, "CAUTION!!!")

 

[Andrew B]

Thank you very much, Tweedle. It works great! I really appreciate your time, expertise and effort.

If you're willing to consider it, I have another question about this code: Is there any way to get the code to generate the warning message in the case of multiple-cell changes or deletions and still not generate a warning in the case of changes to empty cells?

After, getting it to work (thanks to you) I copied and renamed my workbook so that I could tinker with it. I was curious to see if it could be made to work in cases where more than one cell has been modified or deleted. I changed

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    preVal = ""
If Target.Cells.Count = 1 Then preVal = Target.Value
End Sub

to this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    preVal = ""
If Target.Cells.Count < 13 Then preVal = Target.Value
End Sub

and then I changed

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Cells.Count > 1 Then Exit Sub
     If Target.Column < 13 And preVal <> "" Then     ...etc.

to this

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Cells.Count > 12 Then Exit Sub
     If Target.Column < 13 Then     ...etc.

I had to delete

And preVal <> ""

because I kept getting an error message (runtime error '13' Type mismatch). After that, the code worked in cases where multiple cells were being edited or deleted. The problem, of course, is that editing empty cells generated the same warning message that editing completed cells did.

In my workbook I have gone to Tools/Options/View and de-selected Row and Column Headers to make it less likely that and entire entry might accidently be deleted. However, It would still be nice for a warning to be generated if a user tries to delete multiple cells.

Thank you once again. You have been a great help to me.

Regards,
Andrew

 

[tweedle]

Andrew;

I can not reliably trap every action that could occur on a multi-cell range.
(Delete and Clear Contents seem particularly troublesome for me at this time). I think we've slipped beyond my knowledge at this point.

If it were my project, and the requirement said that every change needed traced, I would force the user interface to Userforms and disallow access to the workbooks altogether; using them simply as a database.

Maybe an MVP has an offering, but I don't have a solution for controlling multi-cell edits/messaging if selected cells have data and are acted upon in some way.

G/L

 

[Andrew B]

No problem, Tweedle. You have already been exceedingly helpful to me and generous with your time and for that I am very grateful.

I feel a little embarrassed that I pressed you for more after you have already given so much. I think my ignorance is to blame. I don't know much at all about VBA scripting and so I naively assumed that changing the code to handle multi-cell edits would be a simple thing.

I really do have much to learn about Excel, about VBA, and about online forums!

Anyway, Thank you so much. You really did help me in a big way!

Sincerely,
Andrew