What calendar options are there in Calc?

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

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:

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 (∧)

Thanking yourself for asking a question …

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

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

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

@AlexKemp: 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