How can you limit change event to run only if changed cell was blank.

On 22 Jun., 12:21, Sliman wrote:
How can you limit change event to run only if changed cell was blank.

Hi

Look at this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "" Then
'Do your stuff
End If
End If
End Sub

Regards,
Per

Hi,

This can also work on a range of cells and if your unsure of how to change
it to do that then post back.

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then
If Target.Address = "$A$1" Then
MsgBox "Do something"
End If
End If
End Sub

Mike

"Sliman" wrote:

How can you limit change event to run only if changed cell was blank.

Adapt something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a1 = Range("A1")
Set t = Target
If Intersect(a1, t) Is Nothing Then
If a1.Value = "" Then
wasblank = True
Else
wasblank = False
End If
Exit Sub
Else
If wasblank And a1.Value < "" Then
MsgBox ("changed from blank to nonblank")
wasblank = False
End If
If a1.Value = "" Then
wasblank = True
End If
End If
End Sub

This remembers what WAS in A1.
--
Gary''s Student - gsnu2007j


"Sliman" wrote:

How can you limit change event to run only if changed cell was blank.

This should do what you want. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant
Dim NewValue As Variant
If Target.Count 1 Then Exit Sub
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
Application.EnableEvents = True
If OldValue = "" Then
'Do your thing here
End If
End Sub
"Sliman" wrote in message
...
How can you limit change event to run only if changed cell was blank.