[Solved] Insert/remove text into a textbox (Calc)

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Inserting and removing text into a textbox on a SHEET in calc BASIC

Post by Lupp »

This thread grew extremely long now, and I won't read all the many lines a second time, but it still seems clear to me that dialogs and any tricky code to get content into graphical elements (TextShape objects) is useless overhead.
If I want to input something into a spreadsheet document I never use dialogs, but a next to unformatted cell range of a dedicated protected sheet. If I want to output some of the data then in a rich format using graphical decoration and the like, I design another dedicated sheet for the purpose and pull the data in by (mostly) simple formulas.

I already told so in my first post, and demonstrated the concept. The reply was:
msemafor wrote:Thanks Lupp, interesting idea, will consider it.
No. It's not just an interesting idea, but the solution.

Since I also have some fun with programming now and then, I took the opportunity to study in detail (though, of course, without any dialogs) what kind, structure, and amount of user code would be appropriate or needed to get a solution taking cell contents (or results) and putting them into the correct places inside a graphical structure. The structure and selction of means required some considerations, and the amount was >130 lines of Basic code written in my compact style. That's pretty much. Finally I had surely used more than 4 hours for that all. When things were about finished I made a mistake activating a problem with merged services which I had excperienced earlier, but not regarded in this case. To do the repairs I had the choice to get and edit 48 grouped shapes one by one - or to write additional code to also apply attributes to the mentioned shapes. I chose to write code, but, of course, the "project" is all but perfectly finished now.
The design of two versions of sheets for the task not needing any user code, but actually doing what's required (as far as I understood) took much much much less time and effort, and what I got is easily maintable, scalable, and mostly applicable to different similar tasks.
I attach what I got. Unfortunately I used some enhancements currently only available in recent versions of LibreOffice, and I wouldn't rework the respective parts again, to make the thing running under AOO. Interested users can easily try with a LibreOffice V7.0 or higher.
aoo108314TickesWithOrWithoutMacros.ods
(51.82 KiB) Downloaded 91 times
===Edit 1===
The mentioned features of recent LibreOffioce are:
1. The new function RANDBETWEEN.NV(). Youu can easily omit it in the formulas.
2. Support for the "For Each ... In ..." construct in Basic (and probably a related change of the API) where
formerly only IndexAccess was supported. (In some cases that's much handier, but the adaption of the code
for AOO isn't actually difficult.)

Also: The attached file wasn't a finally reworked version. It contained a few pieces of garbage (doing no harm) in the code.
===End Edit 1===
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
msemafor
Posts: 14
Joined: Fri Aug 19, 2022 8:24 pm
Location: Stargard, Poland

Re: Inserting and removing text into a textbox on a SHEET in calc BASIC

Post by msemafor »

Have managed to do it my way. Attached my project here to let others see my coding (although it's amateur level). All functionalities I wanted to be in it works fine. Names, REMs and labels are in polish.
Huge thanks to all for advices and hints - especially to @JeJe, @FJCC and @Lupp. I learnt a lot by this project and thanks to you.
Attachments
Etykiety_cenowe_v2.2.ods
(40.6 KiB) Downloaded 79 times
OpenOffice 3.1 on Windows 10
Post Reply