Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date when changing a cell
Hi, I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas. I also have a date which should be updated to todays date when any of the cell on the worksheet is changed. Is it possible to do this and how should the code look like? Thanks in advance for helping me! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=561410 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date when changing a cell
Hi Leo,
Paste the following code in your worksheet's code (right-click on tab, select View code) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Date Application.EnableEvents = True End Sub HTH -- AP "leonidas" a écrit dans le message de news: ... Hi, I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas. I also have a date which should be updated to todays date when any of the cell on the worksheet is changed. Is it possible to do this and how should the code look like? Thanks in advance for helping me! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=561410 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date when changing a cell
Put this behind the worksheet code of the sheet you want it on.
(and change "A1" to the cell you want the date in) Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range("A1") Then Exit Sub Range("A1").Value = Now() Range("A1").Copy Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub Duncan leonidas wrote: Hi, I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas. I also have a date which should be updated to todays date when any of the cell on the worksheet is changed. Is it possible to do this and how should the code look like? Thanks in advance for helping me! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=561410 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date when changing a cell
Leo,
Forget my first post, this is a little better on the eyes. I typed too fast last time and didnt set the target back to the cell you typed into originally (which would be annoying when trying to input) and also forgot to remove the cutcopymode. Paste this in instead Private Sub Worksheet_Change(ByVal Target As Range) Dim OLDT As Range If Target = Range("A1") Then Exit Sub Set OLDT = Target Range("A1").Value = Now() Range("A1").Copy Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False OLDT.Select End Sub btw: Ardus: wouldnt your sub loop indefinately? Duncan Ardus Petus wrote: Hi Leo, Paste the following code in your worksheet's code (right-click on tab, select View code) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Date Application.EnableEvents = True End Sub HTH -- AP "leonidas" a écrit dans le message de news: ... Hi, I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas. I also have a date which should be updated to todays date when any of the cell on the worksheet is changed. Is it possible to do this and how should the code look like? Thanks in advance for helping me! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=561410 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date when changing a cell
Ardus:
Sorry, your post is much easier than mine, I got carried away and made it complicated. It obviously doesnt go into an indefinate loop because you are not selecting, I selected as from experiance when i put now() in it updated so I had to paste as value, but in this case is not needed. maybe mine is a little too complicated....... Duncan Ardus Petus wrote: Hi Leo, Paste the following code in your worksheet's code (right-click on tab, select View code) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Date Application.EnableEvents = True End Sub HTH -- AP "leonidas" a écrit dans le message de news: ... Hi, I have a worksheet in excel with a lot of data on it. The data exists of text, numbers and formulas. I also have a date which should be updated to todays date when any of the cell on the worksheet is changed. Is it possible to do this and how should the code look like? Thanks in advance for helping me! -- leonidas ------------------------------------------------------------------------ leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375 View this thread: http://www.excelforum.com/showthread...hreadid=561410 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
input a date or update it based on date in another cell | New Users to Excel | |||
Cell contents changing to date and not date | Excel Worksheet Functions | |||
Populating Last Saved Date in Cell AND also update that same cell in Header | Excel Discussion (Misc queries) | |||
How to update a cell if a specific date is included in a date rang | Setting up and Configuration of Excel |