Cancel the Private sb worksheet_change if cell selected empty

[steve2000]

Hi !

I use this formula to ask employees to confirme the data they enter in a cell and after the data is lock.
It's working very good but i have a little probleme:

When we play around and click on a empty cells, the cell become activated en then we click outside of the same cell ..the vba code send the msgbox automaticly (Do you confirm this information?) even if there is no text or data entered.

Is there a little peace of code we can add to this so it can only activate the msgbox when data is entered and we click out of the cells.. ?

Thanks !!
Steve


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Not Intersect(Target, Range("a:im")) Is Nothing Then
confirm = MsgBox("CONFIRMEZ-VOUS L'INFORMATION?" _
& vbCrLf & "Si oui, elle sera permanente et ineffaçable.", vbOKCancel, "CONFIRMER L'INFORMATION")
Select Case confirm
Case Is = vbYes
Dim Cell As Range
With ActiveSheet
.Unprotect Password:="secret"
.Cells.Locked = False
For Each Cell In ActiveSheet.UsedRange
If Cell.Value = "" Then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
.Protect Password:="secret"
End With
Case Is = vbNo
End Select
End If
Application.EnableEvents = True

End Sub

 

[mumps]

When I tried your macro and clicked on an empty cell, I did not get the message. The Worksheet_Change event is triggered only if there is a change in your target range. If you click in a cell and don't change it, the macro should not run. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[V_Malkoti]

I think steve actually double clicked on the cell, which does trigger Worksheet_Change event.

A basic simple solution would be to check if the value of Target parameter is blank to exit the subroutine.

However, this may create problem when user is trying to clear a populated cell. I'm not sure if there is a way to retrieve old value of a cell to compare (before Change) and see if it was already blank to avoid that problem.