[Solved] Having fetched cell values in a row display in bold

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
permethium
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

[Solved] Having fetched cell values in a row display in bold

Post by permethium »

Hi there guys ,

I'm struggling to find a solution for this in the form of a macro: I want to display values that are entered into cells from different sheets in a spreadsheet to be displayed in bold; for instance:

="The employee of the month is "&Sheet1.A1&". He works in the "&Sheet2.A1&" department."

So that it looks like this: The employee of the month is Peter. He works in the Admin department.

Is there any way achieve this? Any help would be greatly appreciated :ouch:
Last edited by MrProgrammer on Tue Nov 17, 2020 1:58 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Having fetched cell values in a row display in bold

Post by Zizi64 »

Do you want to format a part of the result string by macro?
It is not a simple task... if at all, this can be achieved...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
permethium
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

Re: Having fetched cell values in a row display in bold

Post by permethium »

Yeah ,that's precisely what I want it to do . I want only whatever was entered into Sheet.A1 and Sheet2.A1 to be displayed in bold in the line. I've done some digging around and I have only found examples where people already have set values that should be in bold so those macros look for those values and change them to bold , but in my case the values will constantly change according to whatever is entered in those 2 cells. I unfortunately not proficient enough at coding to make adjustments to their macros to work for what I want to achieve here :/
OpenOffice 3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Having fetched cell values in a row display in bold

Post by Zizi64 »

A workaround tip:
Use 5 adjacent cells for this task. In this case you can format the cells ifferently.

| The employee of the month is | Peter |. He works in the | Admin | department. |
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
permethium
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

Re: Having fetched cell values in a row display in bold

Post by permethium »

yeah, I'm doing it like that at the moment but depending on the length of the string entered in the cells it refers to the result sometimes looks horrible because of the open spaces between the bold stylized cell and the "normal" text that follows it :P
OpenOffice 3.1 on Windows Vista
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Having fetched cell values in a row display in bold

Post by MrProgrammer »

permethium wrote:="The employee of the month is "&Sheet1.A1&". He works in the "&Sheet2.A1&" department."
So that it looks like this: The employee of the month is Peter. He works in the Admin department.
Is there any way achieve this?
I know of no practical way to achieve that in a spreadsheet cell. A cell has its formatting set by a style and that style applies to every character in the cell. However, you can use Open Office to create a poster for the employee.
• Create a new sheet "Both" with the name and department in the same row. Save the spreadsheet. *
• Create a new database using the spreadsheet as its data source.
• Create a new text document containing: The employee of the month is . He works in the  department.
• You'll be inserting two fields next: Insert → Fields → Other → Mail merge fields → {YourDatabase} → Both → {YourColumn}.
• Before the first "." in the text document insert a mail merge field to receive the name.
• Before the word "department" insert a mail merge field to receive the department.
• Make the fields bold, say with Format → Styles and Formatting → Character styles → Strong emphasis.
• Save the document as a template; close it.
• Open the template and use View → Data sources.
• Locate your database and the Both table in the data sources.
• Select the record and use Data to Fields in the toolbar.

Read about Mail Merge and using a spreadsheet as a database's data source in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum or Writer Forum. This may seem like a lot of work, but once the template is created it will take just a few seconds to select the record from the database and pull it into the text document.

* You may be able to do this without creating a new sheet and select the records from the existing sheets. I haven't worked with that configuation myself.

If you want to use a spreadsheet use characters, not formatting, to highlight the words.
="The employee of the month is ••"&Sheet1.A1&"••. He works in the ••"&Sheet2.A1&"•• department."

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
permethium
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

Re: Having fetched cell values in a row display in bold

Post by permethium »

Seems like we ran into a rather fundamental shortcoming of calc :P . Thanks much for the suggestions and workarounds guys - very much appreciated indeed! , will be experimenting today and let you know what popped :D
OpenOffice 3.1 on Windows Vista
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Having fetched cell values in a row display in bold

Post by JeJe »

I don't know much about Calc but I presume you can use a listener for cell change events and search through the cell to find what you want to bold. Perhaps this page may help

viewtopic.php?f=45&t=80498

There's a bold style function here:

viewtopic.php?f=9&t=56601

If you can't bold what's changing in your cell then one idea might be to set things up the reverse way - set the style for the whole cell as bold - but then change the character style of everything else - the bits that aren't changing - to normal. But like I say I don't know Calc.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply