I have a calendar menu implemented in my timesheet spreadsheet.
Up until Libreoffice 4.1.0.4 everything worked fine, with all versions after that and even the latest version I’m getting a "BASIC runtime error"Libreoffice Error.jpg
Anyone have ideas how I can fix this?
why don’t you simply copy and paste your code instead of these silly screenshots ??
the Date -property has changed to a Struct
change this part to:
if oCell.Value = 0 then
oControl.Date.Year = year( now() )
oControl.Date.Month = month( now() )
oControl.Date.Day = day( now() )hi karolus
sorry did not even think to paste the code, makes much more sense!
i did paste in your new code, but still gives me the same error?
Im on libreoffice 4.3.0.4
REM ***** BASIC *****
Option Explicit
REM ##############################
REM These routines allow calc users to use calendar date form widgets to
REM enter dates into spreadsheet cells. Only cells which have a cell style
REM name that begins with "Calendar" (e.g. Calendar, CalendarLong, Calendar02,...)
REM will be enabled for calendar widget input
REM
REM To enable a calc document to use this tool, tie the "EnableCalendarCellFormats"
REM subroutine to the Open Document event (see menu Tools -> Customize -> Events)
REM ##############################
Sub EnableCalendarCellFormats
REM exit if this is not a calc spreadsheet
If NOT ThisComponent.supportsService( "com.sun.star.sheet.SpreadsheetDocument" ) Then Exit Sub
REM create a mouse-click handler to intercept when a user clicks on a spreadsheet cell
Dim xMouseClickHandler
xMouseClickHandler = createUnoListener( "cbMouseClick_", "com.sun.star.awt.XEnhancedMouseClickHandler" )
ThisComponent.CurrentController.addEnhancedMouseClickHandler( xMouseClickHandler )
REM clean up any left-over calendar fields if the user unwittingly saved some in the file
Dim i As Integer
For i = 0 To ThisComponent.Sheets.Count - 1
sbRemoveDateFields( ThisComponent.Sheets.getByIndex( i ).DrawPage )
Next
End Sub
REM ###############
Function cbMouseClick_mousePressed( aEvent ) As Boolean
REM when the user clicks on a spreadsheet cell, add a calendar widget on top
REM of the cell, sized to the cell's height and width
Dim oCell As Object
REM consume the mouse-click
cbMouseClick_mousePressed = TRUE
oCell = aEvent.Target
If NOT oCell.supportsService( "com.sun.star.table.Cell" ) Then Exit Function
REM remove any existing DateField calendars
sbRemoveDateFields( oCell.Spreadsheet.DrawPage )
REM if not Calendar cell style, bail out
If Left( oCell.CellStyle, 8 ) <> "Calendar" Then Exit Function
REM create a control shape of the right size and position
Dim oControlShape As Object
oControlShape = ThisComponent.createInstance( "com.sun.star.drawing.ControlShape" )
oControlShape.setPosition( oCell.Position )
oControlShape.setSize( oCell.Size )
REM create a control com.sun.star.form.component.DateField
Dim oControl As Object
oControl = ThisComponent.createInstance( "com.sun.star.form.component.DateField" )
oControl.Name = "CalendarFormatter123456"
oControl.Tag = oCell.AbsoluteName
oControl.DropDown = TRUE
oControl.DateFormat = 11 'YYYY-MM-DD
if oCell.Value = 0 then
oControl.Date.Year = year( now() )
oControl.Date.Month = month( now() )
oControl.Date.Day = day( now() )
Else
oControl.Date = cDateToIso( oCell.Value )
End If
REM insert the calendar field on top of the cell
oControlShape.setControl( oControl )
oCell.Spreadsheet.DrawPage.add( oControlShape )
REM add a property change listener on the control's date property
Dim xPropChangeListener As Variant
xPropChangeListener = createUnoListener( "cbDateChange_", _
"com.sun.star.beans.XPropertyChangeListener" )
oControl.addPropertyChangeListener( "Date", xPropChangeListener )
cbMouseClick_mousePressed = FALSE
End Function
REM ###############
Sub cbDateChange_propertyChange( aEvent )
REM whenever the calendar widget's date property updates, update the cell value
Dim oCell As Object
If NOT isEmpty( aEvent.NewValue ) Then
oCell = ThisComponent.CurrentController.ActiveSheet.getCellRangeByName( aEvent.Source.Tag )
oCell.Value = cDateFromIso( aEvent.NewValue )
End If
End Sub
REM ###############
REM unused listener call-backs
Sub cbDateChange_disposing()
End Sub
Function cbMouseClick_mouseReleased( aEvent ) As Boolean
cbMouseClick_mouseReleased = TRUE
End Function
Function cbMouseClick_disposing() As Boolean
End Function
REM ###############
REM utility subroutine to delete any calendar widgets inserted by these routines
REM ###############
Sub sbRemoveDateFields( oDrawPage )
Dim i As Integer, oShape As Object
For i = 0 To oDrawPage.Count - 1
oShape = oDrawPage.getByIndex( i )
If oShape.supportsService( "com.sun.star.drawing.ControlShape" ) Then
If Left( oShape.Control.Name, Len( "CalendarFormatter123456" ) ) _
= "CalendarFormatter123456" Then
oDrawPage.remove( oShape )
End If 'was erroneously Next
End If 'was erroneously Next
Next
End Sub
[edit Karolus: please start every code-line with minimal 4 spaces -- Karolus ]
Write below else the similar pattern to the pattern above else
The DateField.Date object member changed from a long value to a ::com::sun:
:util::Date Struct value at 4.1.1. Therefore, you need to use CDateToUnoDate/CDateFromUnoDate functions to transfer values rather than cDateToIso/cDateFromIso functions with post-LO-4.1 macros.
To save a great deal of writing, download & look at the macros in the ODS in Q60894.