i would like to add the current date (say cell B5) when the value in
cell B4 is changed from sheets "Report"

Can you help?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

you can use the change event.

right click on the Report sheet tab, select view code and insert code like
this

Private Sub Worksheet_Change( _
ByVal Target As Range)

if Target.Address = "$B$4" then
range("B5").Value = Date
range("B5").NumberFormat:="mm/dd/yyyy"
End if

End Sub

--
Regards,
Tom Ogilvy



"ceemo" wrote:


i would like to add the current date (say cell B5) when the value in
cell B4 is changed from sheets "Report"

Can you help?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

lovely thank yo

--
ceem
-----------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065
View this thread: http://www.excelforum.com/showthread.php?threadid=57182

this doesnt actually work

has anyone got any ideas on how to fix this?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

You sure you put the code in the correct spot--under the worksheet?

See Tom's instructions one more time.

But make one minor adjustment:

change this:
Range("B5").NumberFormat:="mm/dd/yyyy"
to
Range("B5").NumberFormat = "mm/dd/yyyy"

(drop the colon near the equal sign)

ceemo wrote:

this doesnt actually work

has anyone got any ideas on how to fix this?

--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

--

Dave Peterson

i tried this but it didnt work. there were no errors and the code wa
accepted ok but it didnt populate the cell with the dat

--
ceem
-----------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065
View this thread: http://www.excelforum.com/showthread.php?threadid=57182

It worked for me.

Are you sure you put the code in the correct location.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste it in that code window.

Then back to excel and change B4 of that sheet.

ceemo wrote:

i tried this but it didnt work. there were no errors and the code was
accepted ok but it didnt populate the cell with the date

--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

--

Dave Peterson

ps.

Did you enable macros to run when you opened the workbook?

Tools|macro|security|Security level tab

Choose medium to be prompted (and answer yes)
or
choose low to always allow (MS doesn't recommend this, though)

ceemo wrote:

i tried this but it didnt work. there were no errors and the code was
accepted ok but it didnt populate the cell with the date

--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=571820

--

Dave Peterson

yeo got it wrking thank

--
ceem
-----------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065
View this thread: http://www.excelforum.com/showthread.php?threadid=57182