[Solved] Create "Flashing Text and realtime countdown"

Discuss the spreadsheet application
Post Reply
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

[Solved] Create "Flashing Text and realtime countdown"

Post by RayBir »

Hello everyone, thanks for the help i have received before - awesome.
My question:
I wish to create a "reatime" countdown - for ex:
60 minutes remaining (from the computer clock of course) from a time/day on a cell reference....
Thanks to your help before, i can of course have a day countdown from a given date.
However, i wish it to be "Realtime" - in other words, i want to see the hours/minutes/seconds ticking away?
Is that possible please.
Obviously i could just keep refreshing the page....
Also i wish the time/clock cell to flash or blink?

Hope you understand?

Many Many thanks to this forum and all the contributors!!
Last edited by RayBir on Wed Jun 05, 2019 7:06 pm, edited 2 times in total.
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Create "Flashing Text and realtime countdown"

Post by RoryOF »

Code for a countdown timer is given in
viewtopic.php?f=21&t=83370

It may be suitable as a starting point for your macro.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: Create "Flashing Text and realtime countdown"

Post by RayBir »

RoryOF wrote:Code for a countdown timer is given in
viewtopic.php?f=21&t=83370

It may be suitable as a starting point for your macro.
Many thanks indeed.
I shall save that link and study it further. As you say, it is a start and a good one!
The flashing bit is just to emphasise the result. The important bit is the real-time countdown.
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Create "Flashing Text and realtime countdown"

Post by RoryOF »

You should also refer to Andrew Pitonyak's book on Macro Programming, which can be downloaded from his site http://www.pitonyak.org
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: Create "Flashing Text and realtime countdown"

Post by RayBir »

RoryOF wrote:You should also refer to Andrew Pitonyak's book on Macro Programming, which can be downloaded from his site http://www.pitonyak.org
Once again, thank you :) :)
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Create "Flashing Text and realtime countdown"

Post by Villeroy »

Code: Select all

Sub doc_onOpen()
cell = ThisComponent.NamedRanges.getByName("CountDown").getReferredCells.getCellByPosition(0,0)
while cell.getValue() > 0
  ThisComponent.calculate()
  wait 1000
wend
End Sub
Embed the macro in document.
Assign it to the document's open event (Tools>Customize>Events...)
Create a named cell "CountDown" with a formula counting down to 0, some time diff like =some_other_cell - NOW()

The macro will be triggered by the doc open event and recalculates the document every 1000 milliseconds as long as the named cell is >0
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: Create "Flashing Text and realtime countdown"

Post by RayBir »

Villeroy wrote:

Code: Select all

Sub doc_onOpen()
cell = ThisComponent.NamedRanges.getByName("CountDown").getReferredCells.getCellByPosition(0,0)
while cell.getValue() > 0
  ThisComponent.calculate()
  wait 1000
wend
End Sub
Embed the macro in document.
Assign it to the document's open event (Tools>Customize>Events...)
Create a named cell "CountDown" with a formula counting down to 0, some time diff like =some_other_cell - NOW()

The macro will be triggered by the doc open event and recalculates the document every 1000 milliseconds as long as the named cell is >0
OK, this is getting more complex by every post - lol.
I shall try all of the above of course and didn't expect such wonderfull replies!!

Many thanks!
:super:
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Create "Flashing Text and realtime countdown"

Post by Villeroy »

If you think this is "complex", macro programming is not for you. The above code should work as is. Instead of a named cell you may choose some other method to access a single cell. Flashing text can be implemented without macro by means of conditional formatting, for instance: ISEVEN(SECOND(NOW()))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: Create "Flashing Text and realtime countdown"

Post by RayBir »

Villeroy wrote:If you think this is "complex", macro programming is not for you. The above code should work as is. Instead of a named cell you may choose some other method to access a single cell. Flashing text can be implemented without macro by means of conditional formatting, for instance: ISEVEN(SECOND(NOW()))
I know my limitations and that's why i am here!
Yes, it is "complex" for a noob like me - please accept that we exist - just like you did.... long time ago - remember? 8-)
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [solved]-Create "Flashing Text and realtime countdown"

Post by Villeroy »

I remember so well. It was Excel '95 in that year and the first thing I did was buying a book on Excel VBA from my local book store. At that time reading books on complex stuff was a matter of course. Every discounter had a book shelf with "Word for Dummies" or "Excel in 2 Weeks" so everybody had a chance to get familiar with these tools. Book stores offered hundreds of books on programming.
After 6 weeks or so I had spent enough free time reading and working through about half of that book. I was able to write my first useful non-trivial code and I could undertand other people's code. Self-teaching with random snippets of other people's code will lead you to nowhere. Not to mention that programming OpenOffice is way more difficult than it is with MS Excel and VBA.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply