What calendar options are there in Calc?

[AlexKemp]

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

LO 5.0.0.3

Debian 6.0

[AlexKemp]

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

[rautamiekka]

Thanking yourself for asking a question …

[AlexKemp]

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

[karolus]

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

[rautamiekka]

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

[krath]

@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 5.0.6.3)