AutoInput more than one entry

[Frederico2]

So I love how LibreOffice Calc has a feature where if I already typed a word in a higher row, a highlighted suggestion pops up containing the previous entry.

Only problem is, I keep having to double click each cell and hit space to make them pop up. I’m translating a story, so there is a lot of words; my fingertips are beginning to hurt. Is there a way to make them autofill the same way, but all at once? Like, in a bulk operation that doesn’t require clicking on each cell and hitting space.

Thanks!

EDIT:

To be more clear, Calc AutoInput automatically fills in the English translation in the above example once the French-English pair has been entered. One only needs to paste the French word (chat) and hit space, and the English translation (cat) pops up (the highlighted word). (cont.)

[LeroyG]

Please, share a reduced sample file to test. Thanks.
Maybe VLOOKUP could help.

[Villeroy]

F2 does the same as double-clicking.
Apart from that, I don’t have the faintest idea what you are trying to do.

[Frederico2]

Ok, well I guess I’ll just keep clicking then!

Simple is better.

[eeigor]

NOTE: The solution is reposted.
There was a glitch on the Ask site while updating the solution, and earlier posts joined in.

[joshua4]

Here’s an example using @LeRoyG’s VLOOKUP.

Please note that from the perspective of an experienced user, this is just not the right way to go about the ultimate goal, but if the pattern is working, then perhaps it’s good enough.

SimulateAutoEntry.ods (31.2 KB)

[eeigor]

@Frederico2

Thanks! I tried it, but it keeps giving me weird java errors.

NOTE: There was a glitch on the Ask site while updating the solution, and earlier posts joined in.
Let’s deal with the error more thoroughly. I have no error.

[eeigor]

But this is AutoInput! We have to start entering…

[LeroyG]

@Frederico2

you have to actually double-click each cell

For me, it is easier to press F2.

@eeigor,
The AutoInput works if with a macro you edit a cell, then add a space and Enter?

[eeigor]

@Frederico2

The error appears when entering the definition of ‘et’ into B6.

Select cell B6 and translate the word from the left (“et”).
Move the cursor to the left (select cell A6).
Then run the code. But errors are handled.
I updated the file.

[LeroyG]

@Frederico2

F2 but it doesn’t seem to autofill

F2, Space. This is for AutoInput (menu Tools).

[eeigor]

File has been updated. Adding the selected word to the vocabulary by a hyperlink is implemented.

[eeigor]

@Frederico2

But what of the existing entries?

We need to decide what to do with the existing entries in the vocabulary. Update it by replacing the current translation with the new value? Or add a new meaning of the word via a semicolon?

[eeigor]

Where does the word translation come from? I also want it to translate itself.

[eeigor]

NOTE: The solution is reposted due to an Ask glitch.
Try this.
vocabulary.ods (27.1 KB)
Updated. Edit: See below for ‘vocabulary (2).ods’ & for ‘vocbulary (3).ods’ .

The regular expression has been changed to correctly split compound words containing a hyphen.
Regex Pattern: "[\W]+" to "[^\w||-]+"
OR: '"[\W---]", "[\W--[-]]", "[\W&&[^-]]"
To my surprise, Calc handles this expressions.

---

Important! Macros must be enabled. Menu: Options -LibreOffice - Security - Macro Security… - Security Level
Actions:

1. Clear range A3:B12.
2. Select cell A2. Double-click the cell. OR:
   Alt+F11: Select Standard.MainProcs.TranslateTextByWords procedure from existing macros of 'Vocabulary.ods’ and run it. Close the dialog.
3. Select cell B6 and translate the word from the left (“et”).
4. Move the cursor to the left (select cell A6).
5. Alt+F11: Select Standard.MainProcs.AddWordToVocabulary procedure from existing macros of 'Vocabulary.ods’ and run it. Close the dialog.

Notes:

1. The vocabulary allows the inclusion of duplicates (including characters of different case).
2. If you add a word that is already in the vocabulary, it will also be added. If necessary, you can change this behavior and implement an update of an existing word in the dictionary.
3. After adding a new word, the vocabulary will be sorted by the left column.
4. Perhaps another things should be implemented.
   • The rows under the words should be inserted (not added), shifting the existing data down.
   • The double-click action can be limited, for example, to the first column. — Edit: DONE.
   
   @Frederico2, I have no errors. Follow the instructions clearly. The code is written quite thoroughly, but there may be exceptions.

---

@Frederico2

And do you have to do alt-f11 each time?

vocabulary (2).ods (27.4 KB)

Edit 2:

@eeigor

@Frederico2

But what of the existing entries?

We need to decide what to do with the existing entries in the vocabulary. Update it by replacing the current translation with the new value? Or add a new meaning of the word via a semicolon?

vocabulary (3).ods (28.6 KB)

[eeigor]

@eeigor

The double-click action can be limited, for example, to the first column.

The attached file is updated. The double-click action is limited to the first column (Translate.A:A).

[eeigor]

@Frederico2

But with a lot of words it can get tiring doing all that clicking so that autofill completes the entry.

Still, why don’t you want to keep the word and the translation of the word in separate cells of the row? This is the right approach, and the solution is already given to you. And AutoInput is for individual value.

And you can add the source word to the translation.

[Villeroy]

And as always: This is a database job.

[Wanderer]

@Villeroy Not, if he insist to have both languages in the same cell/field…

[eeigor]

@Frederico2

How to split my column at the first space into two…

1. Select the column.
2. Do: Menu: Data - Text to Columns… (split by space).

@Frederico2

Also, it seems to automatically remove punctuation like commas and periods from B2’s auto-generated column. I add them manually now anyway because the command I use doesn’t put them on their own line.

Yes. That’s what @Lupp did in your topic that you linked to here somewhere.
This can be changed by editing the regular expression in the SplitText() function.
sPattern = "[^\w||-]+" << Regex pattern is set here
(OR: "[\W--[-]]", "[\W---]", "[\W&&[^-]]")
How would you like to handle punctuation marks?
They are now being completely removed.


Edit:
The attached file is updated. Look for ‘vocabulary (3).ods’.

The vocabulary does not allow the inclusion of duplicates (insensitive to case) now.
If you add a word that is already in the vocabulary, you will be prompted to edit the meaning(s) of the word yourself.