Show message box when cell changes

[Lloyd Frank]

Hi.
This my first post so I may not put in all th enecessary info to answer my question, but here goes.

I want to have a message box pop up if the value in a cell on the worksheet changes, but not on the first entry of that cell. I would like this to happen for every cell on the worksheet. I want the message box to ask if the cell is to be changed yes or no and respond according to the answer. If Yes then put in the new value, if NO then exit with no change. Is this possible.

Thank you

 

[Brianwarnock]

I can only give a partial yes but with extra coding it may e possible, the bit I haven't done is for the first time ignore but I guess you could test for that.

You need to use two Worksheet events as shown in the code below, but note that the Change event will be triggered after the update and thus when the answer is No you will loop round again as an update will have taken place to restore the value.

Maybe somebody will have abetter solution and obviously the code below is crude, you will want to expand the message boxes to give more information.

Brian

Public original As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iret As Integer

MsgBox Target.Value & " " & Target.Address & " " & original
iret = MsgBox("Continue?", vbYesNo)
If iret = vbNo Then Target = original
End Sub


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

End Sub

 

[Lloyd Frank]

When I try to check to see if this works I get Runtime error 13 Type mismatch.
Any idea why?

 

[Brianwarnock]

I defined original as a variant as it will handle most data types, if you know the data type you could define it as that type, however variant will not handle user defined types or cells with errors IE #N/A but I don't expect that applies in this case for the update, but if moving through the sheet the change selection event will be triggered and maybe you have cells that do have this, it can be tested for and handled see here

http://www.excelforum.com/excel-programming-vba-macros/483802-type-mismatch.html

I won't be around tomorrow

Brian

 

[Brianwarnock]

Any progress..?

 

[Lloyd Frank]

Sorry about the delay answering you, I have not had time to check. I will try to get to it tomorrow.



I have uploaded the page from the workbook that I am working with. When I use a blank worksheet and run the code the message box shows the new value the cell and the oriiginal value. When you click ok the next msg box shows, "Do you want to change this" with the yes or no option. If you click no it goes back to the original msg box and just keeps looping until "Yes" is clicked.
When I try it in the attached sheet, I get a type missmatched i the line

MsgBox Target.Value & " " & Target.Address & " " & original

Can't see why this is happening.

Thanks for you help.

 

[Lloyd Frank]

It appears that merged cells is the problem with the type mismatch error. Don't know if this can be corrected, but I hope so.

Lloyd

 

[Brianwarnock]

I Mentioned the looping and its reason earlier, it , and the avoidance of a message when an empty cell has data entered for the first time, can be programmed out with simple if statements
Eg if original = target exit sub
I said that t he code was crude, it was to illustrate that what you wanted could be done, not provide full production code where you would probably have just one more complete message such as
Do you want to replace "123" with "645" in cell A1

I never used merge cells, but I do remember that in my time on this forum they were the subject of a number of posts as they appear to cause problems for vba, I think the point is only the first cell is addressed in vba.
I do not know of a solution except to avoid them, I think that they are normally used for display purposes and the result can be achieved using centre across selection but 9 years retired might be confusing me.

Brian

 

[Brianwarnock]

Did a Google search and came up with this

http://www.mrexcel.com/forum/excel-questions/621177-problem-selection-column-merged-cells.html

When I put the code cells.mergecells= false as the first line in the selectionchange event all worked.

I will leave you to tweak your code as I have things to do but feel free to come back with any problems, also please let me know if all ok.

Brian

 

[Lloyd Frank]

Thank you for the help. It has been about 15 years since I have done any programming and I think I have forgotten everything that I had learned. The old brain cells just don't respond as good as they used to.LOL

Lloyd