Trigger Macro by Worksheet_Change

[JSnow]

I would like to have a bit of VBA code that triggers a macro when a cell is
no longer empty. For example: when cell C6 is not longer empty (C6<>"") then
macro whatEver.

The macro, just so you know, will copy the contents from C6 and special
paste the value only into D6. If this can be done all with VBA then that's
even better. Betterier.

I'm bowing faithfully at my cubical to you Excel Gods. Thanks again.

 

[Ron de Bruin]

Hi JSnow

You can use this event in the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("C6"), Target) Is Nothing Then
If Target.Value <> "" Then
Range("D6").Value = Target.Value
End If
End If
End Sub

 

[JSnow]

Ron, I pasted your code into the workskeet and it didn't work. It looks like
it should work, but nothing happens when target C6 fills with a value. I
expected the value to be duplicated in D6.

 

[Ron de Bruin]

Are you sure that you copy it in the correct sheet module

See
http://www.rondebruin.nl/code.htm

 

[JSnow]

I'm wondering if your code doesn't work because of this line: If Not
Application.Intersect(Range("C6"), Target) Is Nothing Then...

Cell C6 contains a formual and will remain blank ("") until certain
conditions are met. Does "Nothing" in VBA include or exclude formulas? If
"Nothing" means a blank cell with no formula then your code will always think
that something is already in the C6.

 

[Ron de Bruin]

If C6 is a formula then change it to this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("C6"), rng) Is Nothing Then
If Target.Value <> "" Then
Range("D6").Value = Target.Value
End If
End If
End If
EndMacro:
End Sub