PDA

View Full Version : Solved: Event Macro to add text to cell



nplouffe
09-06-2012, 02:19 PM
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
09-06-2012, 02:27 PM
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
09-06-2012, 03:10 PM
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
09-07-2012, 01:46 AM
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
09-07-2012, 07:11 AM
Works perfect snb thanks a bunch!

Nick

Paul_Hossler
09-07-2012, 07:24 AM
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
09-07-2012, 07:48 AM
@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
09-07-2012, 11:45 AM
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
09-07-2012, 12:56 PM
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.