DateField Form Control Question

tomraymondtom · Newbie Joined: 22 May 2005 Posts: 4

I've added a DateField Form Control to a Calc sheet with the DropDown property set to yes,
so a date can be selected.
I want to replace the cell reference D5 with the selected date from the Form Control in the following IF statement residing in a cell.

=IF(D5="";"";D5)
How do I reference the selected date from the Form Control?
Question

ms777 · Super User Joined: 07 Feb 2004 Posts: 1313

Unfortunately, there seems to be no easy way to transfer the value of forms, neither numerical input, text input, or date input forms to sheet cells.

The following is a workaround ...

1. Install the macro given below

2. In edit mode, right click on your control, goto Control/Data

3. Enter the cell you want to be updated into the field named Datafield. Do not use ' or ". Always give the sheet name. Enter e.g. Sheet1.A1

4. Goto Control/Events/Text modified, search and select the macro TransferFormValueToCell, whereever you have installed it

Now each change in the form should be reflected in the cell. Note that renaming of the sheet is not supported, after renaming you have to manually update the Data field.
Let me know when there are problems - this is not thoroughly tested.

Here the code:

tomraymondtom · Newbie Joined: 22 May 2005 Posts: 4

Thanks ms777.
I forgot to mention that I'am using Version 2.
Ifound that some of the form controls have a Linked Cell property option, others are missing this option. The datefield control is one that is missing this option.
I will try your code. Thanks again for the reply.

tomraymondtom · Newbie Joined: 22 May 2005 Posts: 4

Posts: 3

PostPosted: Thu Jun 23, 2005 12:47 pm Post subject: Thanks ms777 Reply with quote Edit/Delete this post Delete this post
Very Happy Hi ms777.
Installed Your code and it worked.
Thanks Again

stromer · Posted: Wed Apr 02, 2008 4:32 pm Post subject:

The posting is 3 years old but helped me a lot and I can go to bed soon...

The code works great,

But one important thing:

When filling in the date in the datefield by the calendar there comes an error.

I need the possibility to clear the field. You can check this option in the calendar of the datefield. By leaving it empty the error comes in the line

oCell.Value = CDateFromIso(oModel.Date)

Unfortunately no chance to understand the code.

Can one add the code so that it is possible to let the date be empty and to clear / set to zero the connected cell?

Would help much.

Villeroy · Posted: Thu Apr 03, 2008 12:59 am Post subject:

Read and understand the comments (REM )in the Basic code. There is a reason why the calendar control can not be linked to a cell.
http://www.oooforum.org/forum/viewtopic.phtml?t=63760
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

stromer · Posted: Thu Apr 03, 2008 12:52 pm Post subject:

The error also arises when just clearing the datefield.

The link between the datefield and a cell works fine.

As I understand the popup calendar is just a help to fill in the datefield comfortably.

Isn't it possible to have included in the routine a case where the field is empty and then to clear the cell.
Or another possibility: in case of an error deliver zero to the cell or clear the cell.

Or when clearing the datefield deliver a specific date (maybe 1.1.1900) to the cell.

I can then use this date further as a zero case...

Thanks for brainstorming and delivering code Very Happy

Villeroy · Posted: Thu Apr 03, 2008 1:03 pm Post subject:

stromer · Posted: Thu Apr 03, 2008 2:10 pm Post subject:

I think I understand.

But for my purposes the code above works perfectly as I tested it. I just need dates from 2007 to the future for business. And I need an empty datefield.

I see the error is something like: non-valid procedure call

and then "0: TransferFormValueToCell(event=)"

Villeroy · Posted: Thu Apr 03, 2008 2:31 pm Post subject:

I don't know why the code by ms777 does not work. This is quite unusual. Try my simple code with a named cell. If your control is named "DateField", select a cell anywhere in the document and type "DateField" into the name box left of formula bar. This will be the cell which gets the control's text as formula, just as if you type the control's text into the cell.
The cell's format could be anything. For the date control I would recommend format "YYYY-MM-DD". I think, this ISO format works as valid date input in any language. Contrary, "MM/DD/YYYY" would work with US English only.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

stromer · Posted: Fri Apr 04, 2008 7:23 am Post subject:

Sounds almost too good to be true. Without macro...

Tried each step carefully.

Nothing happens in the sheet cell...

Villeroy · Posted: Fri Apr 04, 2008 7:40 am Post subject:

It requires a macro. Follow the link in my first reply.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

stromer · Posted: Fri Apr 04, 2008 8:03 am Post subject:

Works great! Thanks a lot. Greetings from Munich to anywhere in Germany...

nalsur · General User Joined: 26 Feb 2007 Posts: 8 Location: mas

Great but, if the user select the date for "none" datefield is blank
but error come out, why and how to solve the error msg

and one more, how to trim the value (in date format) only DD i want to display at
other cell?

tq