Solved: Event Macro to add text to cell

[nplouffe]

Hi,

I'm trying to make an event macro to be triggered when a cell in a range is changed or deleted.

For example, if the target range is set to B1:B10 and cell b5 is changed, then c5 = "text". If cell b9 is empty or deleted, then c9 = "".

Any ideas/help?

Let me know if you need more detail.
Nick

[CatDaddy]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B10")) Then
If Target.Text <> "" Then
Target.Offset(0, 1).Value = "Text"
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub

[nplouffe]

Thanks for quick reply CatDaddy,

I get the following error when I run your code: Object variable or With block variable not set. I added the following to your code to prevent it from coming up with that error but the "text" in column c is not replaced with "" when text in column b is deleted.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Intersect(Target, Range("B1:B10")) Then
If Target.Text <> "" Then
Target.Offset(0, 1).Value = "Text"
Else
Target.Offset(0, 1).Value = ""
End If
End If
Application.EnableEvents = True
End Sub


Did it work for you?

[snb]

first run this macro:

Sub tst()
Application.EnableEvents = True
End Sub


Then use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then Target.Offset(, 1) = IIf(Target.Value = "", "", "text")
End Sub

[nplouffe]

Works perfect snb thanks a bunch!

Nick

[Paul_Hossler]

Instead of


Target.Offset(0, 1).Value = ""


I'd suggest


Target.Offset(0, 1).ClearContents


since "" is still 0 length string, and not an empty cell

Paul

[snb]

@Paul

Can you elaborate ?

I ran this macro.

Sub snb()
Cells(2, 6).Value = ""
Cells(4, 6).Value = vbNullString
Cells(6, 6).ClearContents
Columns(6).SpecialCells(4).Select
End Sub


VBA doesn't discriminate between all empty cells ( no matter how they have been (not) created).

[Paul_Hossler]

snb --

I do believe you are correct sir :blush :blush :blush

I was most likely thinking of the old 1-2-3 trick of using <space> to clear a cell.

My habit is to use .ClearContents, and I guess I forgot why

http://www.aivosto.com/vbtips/stringopt.html#whyslow

Color me embarrassed

Paul

[snb]

Hi Paul,

I was only intrigued by your remarks, so I became curious what you knew what I didn't.
Thank you for your reply.
It cools me down again.

Cheers.