Retry/Cancel MsgBox using VBA Code

Hi

When we do validations using data->validation , we get Retry/cancel MsgBox if we enter invalid data. And it wont allow the user to move to the next cell until he/She enters valid data into that cell.

The same I want to do using VBa Code using Ok/Cancel MsgBox.
The way I am trying to do this looks like ...

In WorkSheet_change Function

If Target.Address = "$D$10" Then
no = Sheets.Item(1).Range("D" & 10).Value
If no = 1 Then
x = no

Else
resp = MsgBox("Pls enter valid data", vbOKCancel, "validation")
If resp = 1 Then
Sheets.Item(1).Range("D" & 10).Select
ActiveCell.Interior.ColorIndex = 5
ElseIf resp = 2 Then

Call1

End If
End If
End If

And the Call1 function is :


Private Sub Call1()
Sheets.Item(1).Range("D" & 10).Value = x
End Sub

Private Sub Worksheet_Activate()

x = Sheets.Item(1).Range("D" & 10).Value
End Sub

It is working fine for Cancel button. Means it gets the previous valid data when we click the Cancel button. BUt when we click the Ok button and moved to the cell it is not displaying any error message. Actually there is no code written for this Message to display..

Anyone please help me .......

Thanks in advance.....

I got the solution..........

I'm not sure if I understand correctly, but you can use code along these lines to prevent the user from selecting a new cell if some condition isn't met - in this case, the condition is that the old cell must contain "abc":
Dim oldCell As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (oldCell Is Nothing) Then
If Not (oldCell.Value = "abc") Then
oldCell.Select
Else
Set oldCell = Target
End If
Else
Set oldCell = Target
End If
End Sub

It breaks when ranges are selected, so you'd have to refine it a bit.

Does that help?