Cell to equal combo box date

[Neil-B]

Hi All,
I have a Calc form which has a start and end date entries which I have to manually enter.
I know on the form tool bar I can add a text box and other things and make them a input for a cell value using the control properties
I have added a couple of combo boxes with date fields (combo box 1 and 2) and these work well for my needs and look good but I cannot tie them to a cell, say A1 and B1 for instance.

Is there any way to tie them to cell?

I have A very simple “Hello world” macro that triggers when the value changes in the boxes so I thought is there a way to extract the value of the combo boxes and insert the value into a cell, something like…

sheet1.getcellRangebyName (“A1”).setvalue (combo box1)

Or if it is posible any other way I am open to suggestions

Anyone?

Neil

[librebel]

Hello @Neil-B,

To link the selected value of your Combobox to a particular cell such as A1, open the Properties dialog of your Combobox, select the tab Data, and type the value “A1” in the field called Linked Cell.

HTH, lib

EDIT 2017-01-21

Only after your latest comment did i realise that you meant a Date Field Control ( Calendar Control ), rather than a regular Combobox.
The DateField control in Calc lacks a “Linked Cell” property, as you already indicated above, so you need a macro to do this:

Sub on_Calendar_TextModified( oEvent )
REM Connect this Method to the "Text Modified" event of your Calendar Control (Date Field Control).
REM Then the chosen value will be copied to the cell whose address is specified below:

	REM Cell address to copy the chosen value to:
	Const lSheetIndex As Long	= 0					REM 0,0,0 = "Sheet1.A1"
	Const lColumnIndex As Long	= 0
	Const lRowIndex As Long		= 0
	REM NB. Set the NumberFormat for this cell to Category = “Date”.
	
	Dim oDoc As Object	  : oDoc = ThisComponent
	If Not oDoc.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then Stop
	Dim oSheets As Object : oSheets = oDoc.getSheets()
	Dim oCell As Object   : oCell   = oSheets.getCellByPosition( lColumnIndex, lRowIndex, lSheetIndex )
	oCell.setValue( CDateFromUnoDate( oEvent.Source.getDate() ) )
End Sub

EDIT 2:

As requested by @mariosv, a test document showing 1) the Combobox with Linked Cell A1, and 2) also showing the Datefield Control with the macro to copy the value to cell A1.

Untitled 3.ods

With Regards, lib

[mariosv]

@librebell I think it is not possible for data Combobox

[librebel]

Thanks for your comment @mariosv,

i managed to get this working for a Combobox in Calc, albeit with the list content specified through the Source cell range property, instead of via the 'List content` property… Please try if that works

[Neil-B]

Hi,
As @Iibrel has done, I can use a combo box and use it as a list box with “Source cell range” but when I use the replace with “replace with” drop down to “Date field” there is no “Source cell range” option which is a shame because for a date field having the drop down calendar view is a neat way of selecting a date.
Also if I add a date field from the “more controls” that doesn’t have a “Source cell range” option either.

Neil

[mariosv]

Please @librebel can you attach a sample file, to see how it is implemented.

[librebel]

@Neil-B, i edited my original answer ( EDIT 1 ), please see if that works now.

@mariosv, i attached a sample file under EDIT 2 in the original answer.

[mariosv]

Ok @librebel, maybe crossing names, you have already explained the situation.
There is an extension with calendars Calendar for calc.

[librebel]

I installed this extension, but unfortunately it has some serious issues:

1. Clicking on “Today” does not enter the current date into the selected cell;
2. Clicking on “Today” after doubleclicking on a day number causes a wrong date to be displayed in the dialog. Only after clicking “Today” again, does it show the correct date in the dialog.
3. Resizing the dialog causes the Month listbox to disappear upwards.
4. Columns can be Sorted Ascending or Descending, resulting in a nonsensical calendar

[Neil-B]

Hi @librebel I have just tried the attached calc file and it works perfectly thank you.
The only other way I could think of doing this (after a lot trawling around on google ) was to use the Dialog feature in Libre office, a good site was debugpoint.com
Again thank you for the above your answer.
Neil