| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trigger Macro by Worksheet_Change
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trigger Macro by Worksheet_Change
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trigger Macro by Worksheet_Change
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trigger Macro by Worksheet_Change
Are you sure that you copy it in the correct sheet module
See http://www.rondebruin.nl/code.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trigger Macro by Worksheet_Change
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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trigger Macro by Worksheet_Change
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JSnow" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using date as trigger for macro | Excel Discussion (Misc queries) | |||
| Worksheet_Change | Excel Discussion (Misc queries) | |||
| macro trigger | Excel Discussion (Misc queries) | |||
| trigger help | Excel Worksheet Functions | |||
| Excel 2003 has wrong timing using the worksheet_change macro | Excel Worksheet Functions | |||
Linear Mode
