Calc/API/Dialogs

For general information on creating dialogs see the Apache OpenOffice online help for create dialog.

A common task when presenting a dialog in a spreadsheet is to request a cell range. The following routines allow a user to select a cell range. Unfortunately, the user has to click a button to activate the routines (i.e. still to do is a routine to activate these routines when the user clicks outside the dialog).

The dialog has the following requirements:

• A button or buttons with Initiate event set to subEventShrink
• The above button(s) have the tag field set to the name of the corresponding text field
• For each button a corresponding text field for the range address

' The dialog is kept in a global variable so that other subroutines can access it (below).
Private oDialog As Object
 
' oDestField is kept in a global variable so that it is available to the range selection listener
' routine, and it knows which edit box is the destination for the selection string.
private oDestField as object
 
' bSelecting is the variable whose value gets changed so that a loop can be exited when selecting a range
' has finished.
private bSelecting as boolean
 
' This variable is so that the dialog can be endExecuted under program control, so that the range selection listener 
' will work under both Windows and Linux, and reactivated when required.
private bDialogFinished as boolean
 
Sub Main()
' Make sure this library, with its dialog is loaded.
DialogLibraries.LoadLibrary( "SignificantDigits" )
 
' Create the dialog object.
oDialog = createUnoDialog( DialogLibraries.GetByName( "SignificantDigits" ).GetByName( "SigDigitsDlg" ) )
 
'Other code for setting up the dialog goes here
 
' Display the dialog.
' This routine call does not return until the dialog is dismissed.
 
do
   bDialogFinished = true
   oDialog.Execute()
loop until bDialogFinished      
' Execution does not reach this point until the dialog is dismissed.
End Sub

The following routines for range selection were improved (actually made to work) with input from Jim Thompson and with concepts in a thread by Danad: https://web.archive.org/web/20080411235341/http://www.oooforum.org/forum/viewtopic.phtml?t=6160

 
sub subEventShrink (oEvent)
'Called when Rng button clicked
'Uses module variables: oDialog, oRangeSelectionListener, oDestRange
dim mRangeSelection, sField as string
 
sField =  oEvent.source.model.tag
oDestField = oDialog.getControl(sField)
subAddPropertyValue( mRangeSelection, "InitialValue", oDestField.text)
subAddPropertyValue( mRangeSelection, "Title", oDialog.title & " " & sField )
subAddPropertyValue( mRangeSelection, "CloseOnMouseRelease", true )
 
'The order of starting the range selection and hiding the dialog is important it must be as follows
oDocCtrl = thisComponent.getCurrentController()
 
oRangeSelectionListener = CreateUnoListener( "RangeSelectionListener_","com.sun.star.sheet.XRangeSelectionListener" )
oDocCtrl.addRangeSelectionListener( oRangeSelectionListener ) ' Register the listener
bSelecting = true
bDialogFinished = false
oDialog.endExecute
 
oDocCtrl.startRangeSelection(mRangeSelection )
while bSelecting
wend
oDocCtrl.removeRangeSelectionListener(oRangeSelectionListener)
end sub
 
Sub RangeSelectionListener_done(oRangeSelectionEvent as new com.sun.star.sheet.RangeSelectionEvent)
'Uses module variables: bSelecting, oRangeSelectionEvent, oDestField, oDialog
'Called when the range selection is done (clicking the icon at right end)
 
oDestField.text= oRangeSelectionEvent.RangeDescriptor
bSelecting = false
'oDialog.visible=true
'oDialog.enable = true
end sub
 
Sub RangeSelectionListener_aborted(oRangeSelectionEvent as new com.sun.star.sheet.RangeSelectionEvent)
'Uses module variables: bSelecting, oDialog
'Called when the range selection is cancelled (clicking X at top right)
bSelecting = false
'oDialog.visible=true
'oDialog.enable = true
end sub
 
Sub RangeSelectionListener_disposing()
'nothing to do
end sub