Lotus Formula Input + Disable Capitalization

[Villeroy]

The anti-capitalization is obsolete since version 2.3
The Lotus input is integrated since version 2.4

Edit 2006-02-28:
Enhanced version added to this thread
2006-03-02: Further enhancement: replaced Sub StartFormula(sFmlStart$)
2007-06-28: Added another module, which disables capitalization of letters a-z

In the calc-forum there have been many complaints about missing "Lotus"- formula input starting with "+" or "-"
There is a patch availlable at http://www.openoffice.org/issues/show_bug.cgi?id=20496
but who wants to recompile this monster?
The following basic-code is a lame duck because
1) It does not initialize formula input mode when you start input with "+" or "-". It just changes a string to "=.*" or "=-.*" respectivly after it has been entered into a cell. You can't point to a referred cell(-range) by selection as in real formula input mode.
2) It applies to the currently focussed cell(aka "active cell") only. A multiple input (Alt+Enter) does not convert the other cells in selection.

So it is best used for quick calculation of constants, which raises some questions about separation of data and processing. But, hey it's a spreadsheet and as such it is used in many ways.
BUG: removeModifyListener does not stop the Lotus-mode.
This feature is not saved with the document, so there is no need to remove it on closing
---------WARNING----------
This is not thoroughly tested. For testing call OOo_DocOpen_DocCreate() with a spreadsheet-doc as currently active document rather than assigning any Open/Creation events.
---------------INSTALL----------------------------
Put the entire code to some Module of library "Standard"
in container "My Macros"(user-profile) or "OOoMacros"(install-path)
Assign OOo's events "Doc Creation" and/or "Doc Open" to OOo_DocOpen_DocCreate.
"Creation" applies to new docs, "Open" to existing ones
If you like to use this feature with some documents:
Assign the "Open"-Event of the document to OOo_DocOpen_DocCreate
For a template, use the "Creation"-event as well.

[SergeM]

Villeroy, thank you for this code.
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide

[Duster929]

Villeroy, thanks for this. I followed the instructions from the other thread and it worked well. I thought I'd post this here so as not to completely change the topic of that other thread.

This works well for entering formulas as straight values, sort of calculator-style. It also works if I am going to type in a formula using the keyboard, for example +c5+c6.

However, it doesn't work if I want to select another cell in the context of the formula, either using the arrow keys or clicking on a cell. For example, to enter +2*c5, I'd have to type it all out, I can't hit "+" then "2" then "*", then click on c5 with the mouse.

It also only works if I save it in OOo format. If I save it in xls, then the macro doesn't work any more. I suppose this has to do with formats, OOo Basic vs. whatever language MS uses for their Excel macros these days.

These are not killer problems - this is still a step in the right direction. But I'm hoping we can go a step further and have it implemented right in the Calc code, making + equivalent to =.

Thank you for the code. I'll keep using/testing it and let you know more of my thoughts on it.

--- D

[Villeroy]

As I stated before:

[Duster929]

Got it. Now I understand what you meant in your "lame duck" posting.

One day we will all be on OOo, and these MS compatibility issues will all be behind us.

--- D

[Villeroy]

ANOTHER EDIT 2006-03-02: Replaced Sub StartFormula(sFmlStart$) after ms777 found the final tweak
Credits:
To noranthon, who came up with the idea to start edit mode rather than edit the cell after editing.
To ms777, who spent some time and effort to make it perfect. He found a way how to start a formula while in insert-mode.
To uros who found a way to get the focussed cell within a range selection.
This version listens to the keys "+" and "-", puts a formula prefix ("=" or "=-") into the active cell starting edit mode.

INSTALLATION:
1. Copy this code into any library. If you save this in a document's library the feature is only availlable for documents having this code. Normally you would choose some library in container "My Macros"
2. Edit the three constants libContainer, libName and modName accordingly.
3. Assign a shortcut or button to "addLotusKeyEvents" in order to activate this.
3.1. If you like to use this with any spreadsheet, assign the events "Doc Open" and "Doc Creation" to "addLotusKeyEvents" (Menu:Tools>Customize>Tab:Events).
"removeLotusKeyEvents" turns the feature off.

[uros]

[Villeroy]

Uros,
Your little function is the best one I ever found on this site
It is essential whenever someone has to manipulate the active cell within a current range selection. Search this site for "getFocussedCell" and you get some recent matches where I used it with some simple macros. It is one of the most called functions in my lib "Standard". Before I found this I had to abort some routines with a msgbox like "Please select a single cell..."

[uros]

Hi Villeroy!
I'm glad you find it so usefull!
Uros

[paulmath]

Hi,

my wife needs this feature very badly. She uses the keypad very frequently and effectively for bookkeeping pruposes, and the need to type "=" all the time slows the speed down dramaticly! And since she is used to other sofware that allows her to use the keypad only it also annoys her a lot....

This script seems the solution, the only problem is that I will have to implement it and I am a total noob on the subject

It's a bit difficult for me to explain what I tried since my native is Dutch and we use a Dutch version, but I tried to add the code under "My Macro's|Standard|Module1" (would that be the correct place to add it?). Now when I activate the macro and try to enter a calculation I get an error message that says: "getScript: no script!"

I think the problem is that I don't understand this:

[noranthon]

The first and second constants are correct. The third is the name of the module into which you have placed the script. In your case the name is probably the equivalent of "Module1". Whatever its name, that is the detail you should enter as modName.
_________________
search forum by month

[Villeroy]

noranthon,
Thank you for answering this. I was a few days off.
Let me add another macro which enables the feature for each opened spreadsheet.

[paulmath]

Yes it works! Thank you both a lot!

(
Reinstalled the script in a Calc "folder" editted the libName to Calc and modName to module1 (will probably rename the module name later, have to install it on another PC anyway).
)

[Villeroy]

The following code disables auto-capitalization of chars a-z, when entered as first letter in a spreadsheet cell. Use and install exactly as the Lotus key handler above. Together with that one in one library or only this one together with the other code beginning at: