Running a variable macro when any value is entered into a variable cell
 

Dear all

I have a simple problem and a difficult problem.

Simple:
I have two columns in a worksheet. I have written a code for a macro
that enters today's date in the second column, then converts it to a
value - i.e. not =today(). I want that code to be executed when any
value is entered into the first column.

e.g. If text is entered in A1 and A1 is clicked away from - B1 is
populated with =today(), then copied and pasted into a value (via the
macro I have already written)

Difficult:
This is an extension of the simple problem, but need help creating a
more complex macro to solve the below-
I have a worksheet that contains an unlimited number of rows. I need a
macro to be run to populate Column B with =today(), then copied and
pasted into a value, when a value has been added into Column A.
However, I need this macro to work for any row within Column A & B.

e.g. If text is entered in A1 and A1 is clicked away from - B1 is
populated with =today(), then copied and pasted into a value. And this
macro will work if text is entered in A100 and A100 is clicked away
from - B100 is populated with =today(), then copied and pasted into a
value.

Can anyone please help with either the above problems???

Kind Regards

Iain

Running a variable macro when any value is entered into a variable
 

Hi Iain,

Look at the worksheet_change event - you'll see that when you click away
from A1 this event fires and you can then use the event to populate B1.

Word of advice - don't use a real email address with newsgroups - you'll get
a ton of spam

This will work simply for the easy problem and with a bit of lateral
thinking it'll work for the difficult one.

Cheers,

John

--
www.alignment-systems.com


" wrote:

Dear all

I have a simple problem and a difficult problem.

Simple:
I have two columns in a worksheet. I have written a code for a macro
that enters today's date in the second column, then converts it to a
value - i.e. not =today(). I want that code to be executed when any
value is entered into the first column.

e.g. If text is entered in A1 and A1 is clicked away from - B1 is
populated with =today(), then copied and pasted into a value (via the
macro I have already written)

Difficult:
This is an extension of the simple problem, but need help creating a
more complex macro to solve the below-
I have a worksheet that contains an unlimited number of rows. I need a
macro to be run to populate Column B with =today(), then copied and
pasted into a value, when a value has been added into Column A.
However, I need this macro to work for any row within Column A & B.

e.g. If text is entered in A1 and A1 is clicked away from - B1 is
populated with =today(), then copied and pasted into a value. And this
macro will work if text is entered in A100 and A100 is clicked away
from - B100 is populated with =today(), then copied and pasted into a
value.

Can anyone please help with either the above problems???

Kind Regards

Iain

Running a variable macro when any value is entered into a variable
 

John

I have tried using the worksheet_change event as follow:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 3 Then DATA1

End Sub

Along with the Macro:

Sub DATA1()


Range("E9").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("E9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("F9").Select

End Sub

However, When I select C9 and click off it, nothing happens. Although
E9 should have todays date value in it. Can you see where I am going
wrong? I am using Excel 2003, and Column 3(C) is a dropdown list of
values.

Kind Regards

Running a variable macro when any value is entered into a variable
 

Hi Ian,

Your code works for me.

Perhaps events have been inadevertently turned off. To resolve this, in the
VBE immediate window type:

Application.EnableEvents = True

and hit the Enter key.

Incidentally, i think that your procedu

Sub DATA1()


Range("E9").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("E9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("F9").Select

End Sub

could be expressed as:

'=============
Sub DATA1()
With Range("E9")
.Value = Date
.Offset(0, 1).ClearContents
.Offset(0, 1).Select
End With
End Sub
'<<=============


---
Regards,
Norman



wrote in message
oups.com...
John

I have tried using the worksheet_change event as follow:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 3 Then DATA1

End Sub

Along with the Macro:

Sub DATA1()


Range("E9").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("E9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("F9").Select

End Sub

However, When I select C9 and click off it, nothing happens. Although
E9 should have todays date value in it. Can you see where I am going
wrong? I am using Excel 2003, and Column 3(C) is a dropdown list of
values.

Kind Regards