Apache OpenOffice Community Forum

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

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...

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 :/

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. |

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

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.

Seems like we ran into a rather fundamental shortcoming of calc . Thanks much for the suggestions and workarounds guys - very much appreciated indeed! , will be experimenting today and let you know what popped

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.