[Solved] Double click to add date and time to Calc cell

[ajardine]

I wrote some Excel VBA some time ago so that anytime I double clicked in a specific named range, the date and time would be entered automatically into the cell. How would I do that in Calc? I'm using OpenOffice 3.1.1 on Windows 7. Here's the VBA code.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range
Set isect = Intersect(Target, Range("Date"))
If Not isect Is Nothing Then
Target = Now()
End If
End Sub

Thanks in advance for any help...

Andrew

[Zizi64]

If I know exactly, the double click in a cell causes only the "local editing" (for example: when you want to put multi line text in a cell by Ctrl-Enter). (Maybe it possible to change the assigned function)
But you can create an OOBasic SUB, to put the Datetime value into the selected cell(s), and you can format the cell if necessary as desired DateTime format, automatically.
Then you can to assign that macro SUB to a new menu item.

Code: Select all

Sub MyDateTime

oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet= oView.getActiveSheet()
oSel = oDoc.getCurrentSelection()
oAddr = oSel.getRangeAddress()

nSCol = oAddr.StartColumn
nSRow = oAddr.StartRow
nECol = oAddr.EndColumn
nERow = oAddr.EndRow

'print nSCol, nECol, nSRow, nERow


if (nERow-nSRow)>65536 or (nECol-nSCol)>1024   then
   Print "Error too much cells selected!"
   Exit sub
end if



'Put datetime into the cellrange 
for j = nSCol to nECol
   for i=nSRow to nERow
      oCell = oSheet.getCellByPosition(j,i)
      oCell.Value = Now
      DateTimeFormat
   next 'i   
next 'j

End Sub


sub DateTimeFormat
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "NumberFormatValue"
args1(0).Value = 51

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args1())

end sub

The code (51) is an DateTime format code in my OpenOffice: "YYYY-MM-DD HH:MM:SS"
(The "DateTimeFormat" sub was recorded by MacroRecorder)

[ajardine]

Can't I set up a Listener (I'm not completely sure what they are) to recognize a double click inside a cell range and run the code from that event?

Andrew

[Charlie Young]

Can't I set up a Listener (I'm not completely sure what they are) to recognize a double click inside a cell range and run the code from that event?

Andrew

Yes you can. The code in the attached document owes a lot to Turtle47, who posted something using a MouseClickHandler a while back.

The handler (a form of listener, I suppose) starts on File > Open, and it can be stopped with "StopXMouseClickHandler." The named range is called "DateRange," and is set up as Sheet1.A2:D20. You might change it to see what happens.

[leeand00]

How did you assign the StartXMouseClickHandler to the cell mouse click event?

(I know how to do stuff like this on forms but I have no idea how its done on documents like spreadsheets and writer documents.)

[Charlie Young]

How did you assign the StartXMouseClickHandler to the cell mouse click event?

(I know how to do stuff like this on forms but I have no idea how its done on documents like spreadsheets and writer documents.)

I missed this query somehow. StartXMouseClickHandler is set to run on File > Open. The mouseclick dates any cell in the range named DateRange (Sheet1.A1:A20 in the example). As written, it will only handle a single rectangular range, but it could be adapted for more complex situations.

[ajardine]

Thanks to everyone who helped with this, especially Charlie - I finally got the chance to adapt this code, and it does exactly what I needed. Can't claim my OpenOffice Basic is up to understanding exactly how it all works, but I did manage to get the code into its own module and the event handler hooked up to the right events. I remarked out the bit where the code dictated how the current date/time was recorded (prefer to let the cell formating in the worksheet determine that).

Again, thank for the help - It is great when I can get my OpenOffice macros to do the same things my Excel macros can do.

Andrew

[oonk]

The handler (a form of listener, I suppose) starts on File > Open, and it can be stopped with "StopXMouseClickHandler".
The named range is called "DateRange," and is set up as Sheet1.A2:D20.
You might change it to see what happens.

1. Based on Charlie Young's sample file, how can I replace DateRange(A2:D20) created manually with SheetA and Row 2 to 20 by code ?
2. From my attached file, how can I move all codes of Module1 in Standard Library to Module1 in Library1 Library of this Calc file ?

P.S. I just need the code for double click event on a row.

[Zizi64]

2. From my attached file, how can I move all codes of Module1 in Standard Library to Module1 in Library1 Library of this Calc file ?

Just create a library with name Library1, and then copy/move the code into it.


Note:
Only the library "Standard" will be loaded automatically (at opening the file) into the memory.

[oonk]

Just create a library with name Library1, and then copy/move the code into it.

After moving, double click event does not work.

Note:
Only the library "Standard" will be loaded automatically (at opening the file) into the memory.

Is this the reason ?

[Zizi64]

Note:
Only the library "Standard" will be loaded automatically (at opening the file) into the memory.

Is this the reason ?

Maybe. Try it.

But you can load othor libraries by a macro routine launched from the Standard library:

Code: Select all

Sub LoadLibrary(LibraryName as string)

	If (Not GlobalScope.BasicLibraries.isLibraryLoaded(LibraryName)) Then
		GlobalScope.BasicLibraries.LoadLibrary(LibraryName)
	End If
end sub

Sub LoadLibrary1
 Sub LoadLibrary("Library1")
end sub