Page 1 of 1
[Solved] Double click to add date and time to Calc cell
Posted: Sat Sep 04, 2010 6:30 pm
by 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
Re: double click to add date and time to Calc cell
Posted: Sat Sep 04, 2010 7:07 pm
by 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)
Re: Double click to add date and time to Calc cell
Posted: Tue Sep 07, 2010 12:45 am
by 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
Re: Double click to add date and time to Calc cell
Posted: Tue Sep 07, 2010 2:12 am
by Charlie Young
ajardine wrote: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.
Re: Double click to add date and time to Calc cell
Posted: Wed Sep 15, 2010 4:38 pm
by 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.)
Re: Double click to add date and time to Calc cell
Posted: Sun Sep 19, 2010 10:56 pm
by Charlie Young
leeand00 wrote: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.
Re: Double click to add date and time to Calc cell
Posted: Sun Nov 14, 2010 5:19 am
by 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
Re: Double click to add date and time to Calc cell
Posted: Sun Dec 15, 2019 4:51 am
by oonk
Charlie Young wrote:ajardine wrote:
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.
Re: [Solved] Double click to add date and time to Calc cell
Posted: Sun Dec 15, 2019 9:36 am
by 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.
Re: [Solved] Double click to add date and time to Calc cell
Posted: Mon Dec 16, 2019 7:51 am
by oonk
Zizi64 wrote:
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 ?
Re: [Solved] Double click to add date and time to Calc cell
Posted: Mon Dec 16, 2019 11:06 am
by 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