[Solved] Date and Time Stamp when Other Cell is Modified

Creating a macro - Writing a Script - Using the API

[Solved] Date and Time Stamp when Other Cell is Modified

Postby openoffice44 » Tue Jan 30, 2018 3:22 pm

I am trying unsuccessfully to convert my Microsoft Excel Macro to work with the Open Office Spreadsheet, I have looked up similar questions but have not found help yet. What I currently have is a spreadsheet that has 12 columns of 400 rows that data will be entered into. In the columns further to the right I have, for each column of data, a column with a date stamp and a column with a time stamp. As soon as one of the data columns is modified, the date and time columns automatically record the date (MM/DD/YYYY) and time (HH/MM/SS). I need this to function in the same way in Open Office but I am not sure what the code should be. I am attaching screenshots of the code and the output in Excel. For the code, the part shown is repeated for each column of data. Thank you for any help or suggestions you have!
Attachments
Scanner Picture.jpg
Code Picture.jpg
Last edited by openoffice44 on Tue Jan 30, 2018 8:18 pm, edited 1 time in total.
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
openoffice44
 
Posts: 4
Joined: Tue Jan 30, 2018 2:24 pm

Re: Date and Time Stamp when Other Cell is Modified

Postby Villeroy » Tue Jan 30, 2018 5:36 pm

timestamps.ods
(13.2 KiB) Downloaded 175 times
(free of macros but with iterative calculation mode)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date and Time Stamp when Other Cell is Modified

Postby openoffice44 » Tue Jan 30, 2018 8:18 pm

Villeroy, that worked perfectly, thank you!
OpenOffice 3.1 on Windows Vista / NeoOffice 2.2.3 with MacOS 10.4 / OpenOffice 2.4 on Ubuntu 9.04
openoffice44
 
Posts: 4
Joined: Tue Jan 30, 2018 2:24 pm

Re: [Solved] Date and Time Stamp when Other Cell is Modified

Postby Lupp » Tue Jan 30, 2018 11:41 pm

Hmmm.
How should the last modification update the timestamp in a sheet using the formulae in the above attached demo?

I modified an old example slightly to also get timestamps for modifications. See attachment.

As already said more than once I judge methods using conditionally suspended circular references for such a task very fragile and wouldn't recommend them.

Basically spreadsheets aren't history aware. If someone urgently needs to implement timestamps I would advise to resort to user code activated by the sheet event 'Content Changed'. There's to regard a lot again, however, If a reliable solution is needed.
Attachments
TimeStampsAgain2.ods
(17.97 KiB) Downloaded 94 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2312
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Date and Time Stamp when Other Cell is Modified

Postby Villeroy » Tue Jan 30, 2018 11:43 pm

Lupp wrote:How should the last modification update the timestamp in a sheet using the formulae in the above attached demo?

Clear the cell (backspace key) before you enter a new value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests