Having fetched cell values in a row display in bold

Creating a macro - Writing a Script - Using the API

Having fetched cell values in a row display in bold

Postby permethium » Thu Mar 23, 2017 9:37 am

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:
OpenOffice 3.1 on Windows Vista
permethium
 
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

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

Postby Zizi64 » Thu Mar 23, 2017 9:49 am

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; LibreOffice4.4.7 - Portable versions: LO3.3.0-LO5.3.2 and AOO4.1.3; on Win7x64Prof.
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 4983
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby permethium » Thu Mar 23, 2017 10:00 am

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
permethium
 
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

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

Postby Zizi64 » Thu Mar 23, 2017 1:13 pm

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; LibreOffice4.4.7 - Portable versions: LO3.3.0-LO5.3.2 and AOO4.1.3; on Win7x64Prof.
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 4983
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby permethium » Thu Mar 23, 2017 2:01 pm

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
permethium
 
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

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

Postby MrProgrammer » Thu Mar 23, 2017 6:47 pm

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 2891
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby permethium » Fri Mar 24, 2017 8:04 am

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
permethium
 
Posts: 6
Joined: Tue Sep 02, 2014 11:43 am

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

Postby JeJe » Fri Mar 24, 2017 10:47 am

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

https://forum.openoffice.org/en/forum/v ... 45&t=80498

There's a bold style function here:

https://forum.openoffice.org/en/forum/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.
Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 96
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests