We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

What calendar options are there in Calc? [closed]

asked 2015-11-15 23:34:21 +0200

Alex Kemp gravatar image

updated 2015-11-18 01:47:34 +0200

If I want to enter a date in a cell in Calc, are there any methods to use a calendar to enter that date?

Debian 6.0

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-27 15:01:44.722895

1 Answer

Sort by » oldest newest most voted

answered 2015-11-15 23:50:28 +0200

Alex Kemp gravatar image

updated 2015-11-16 14:06:45 +0200

calendar.ods (contains macros)

I'm glad that you asked that question, Alex, as I've just finished fixing an ODS containing a Calendar widget originally supplied at this site by @JohnSUN (Q23917).

Here it is in action:
image description

The macros within the ODS file supplied within Q23917 worked fine at the time that it was supplied. A little later LO was updated to 4.1.1. Immediately, a great many of all existing macros for LO/AOO threw an error message. In this particular case it occurred as soon as a calendar cell was clicked on:-

BASIC runtime error, Object variable not set

(the edit page for the embedded macros comes up with a line within Function cbMouseClick_mousePressed() highlighted):-

  If oCell.Value = 0 Then
    oControl.Date = cDateToIso( now() )
    oCell.Value = cDateFromIso( oControl.Date )
    oControl.Date = cDateToIso( oCell.Value ) ' << highlighted line
  End If

Bug Reason:

Pre LO-4.1.1:-

oControl.Date is a Date member of a UnoControlDateField object.

  • long
  • The date must be specified in the format YYYYMMDD
  • Convert from cell to UnoControl: oControl.Date = cDateToIso( oCell.Value )
  • Convert from UnoControl to cell: oCell.Value = cDateFromIso( oControl.Date )

LO >= 4.1.1
large-scale changes in UNO APIs; in particular for these macros:-

  • service com.sun.star.awt.UnoControlDateFieldModel: properties Date, DateMin and DateMax changed to type com.sun.star.util.Date instead of long (integer)

oControl.Date is a Date property of a UnoControlDateFieldModel Service.

  • Date Struct:
    unsigned short Day
    unsigned short Month
    short Year
  • Convert from cell to UnoControl: oControl.Date = CDateToUnoDate( oCell.Value )
  • Convert from UnoControl to cell: oCell.Value = CDateFromUnoDate( oControl.Date )

I initially found the reason for the bug at an Openoffice forum post and, after much more research & practice, was able to fix the macro. I've fixed it in such a way that it should be equally usable in either post- or pre-4.1.1 versions of either LO or AOO (although only tested in 5.0.0). Hooray!

One very interesting feature of this file+macros is that the widget is embedded within LO as supplied (no extension needed). However, I do not know of any other method to access it other than via a macro.

All I need to do now is to discover how to cause a background-colour (like the blue-box above) be transparent, so that the grid-lines continue to show through.

If this helps then please show you like it with an uptick (∧)

edit flag offensive delete link more


Thanking yourself for asking a question ...

rautamiekka gravatar imagerautamiekka ( 2015-11-16 00:17:51 +0200 )edit

Yes, I've noticed your acute sense of humour during the time that I've been working here, rautamiekka. Thanks for your comment.

Alex Kemp gravatar imageAlex Kemp ( 2015-11-16 00:21:02 +0200 )edit

@Alex Kemp: whats the questions? btw. It seems you need one more Internet sock puppet!?

karolus gravatar imagekarolus ( 2015-11-16 00:24:49 +0200 )edit

As if everything can be put to humor's tab.

rautamiekka gravatar imagerautamiekka ( 2015-11-16 00:27:26 +0200 )edit

@Alex Kemp: Macro works fine in my spreadsheet exept if choosing "None" in the Pop-up the cell always returns the last chosen date. It's impossible to go back to an empty cell. (LO

krath gravatar imagekrath ( 2016-06-01 16:31:48 +0200 )edit

Question Tools

1 follower


Asked: 2015-11-15 23:34:21 +0200

Seen: 1,301 times

Last updated: Nov 16 '15