Ask Your Question

What calendar options are there in Calc?

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 close merge delete

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 properties Date, DateMin and DateMax changed to type 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
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 941 times

Last updated: Nov 16 '15