worksheet change event only if cell was blank
 

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

worksheet change event only if 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

worksheet change event only if cell was blank
 

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.

worksheet change event only if 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.

worksheet change event only if 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.