[Solved] Control string-element color

[AndresSolar]

In a spreadsheet cell we can easily assign different colors to each character of a string.

In the example i changed the color of the first word (out of two) in cell B7 from black to red.
The macro recorder suggests:

Code: Select all

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$7"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Color"
args2(0).Value = 16711680
dispatcher.executeDispatch(document, ".uno:Color", "", 0, args2())
end sub

I don't want to use the dispatcher...
Within a Function i have a loop to construct the resulting String

Function = Function & characterN

I'd like to control the color of each characterN

Is this possible within a macro? Thx in advance

[AndresSolar]

...assuming that the length of the string and the color pattern never change.

Function returns a string of 24 characters to the calling cell.

The first 6 characters (Left(Function, 6)) and the last 6 characters (Left(Function, 6)) use a darker color tone than the characters (7-17).

I'd like to apply the colors to the string prior to writing back to the cell.

something like:

Left(Function, 6).FontColor = ...
Right(Function, 6).FontColor = ...

[FJCC]

Here is a procedure that places 24 characters in Sheet1.A1 and colors the 6 initial and 6 final characters.

Code: Select all

Sub TextReturn
  oSheets = ThisComponent.getSheets()
  oSheet1 = oSheets.getByName("Sheet1")
  oCell = oSheet1.getCellRangeByName("A1")
  oCell.String = "ABCDEFGHIJKLMNOPQRSTUVWX"
  oCur = oCell.createTextCursor()
  oCur.gotoStart(FALSE)
  oCur.goRight(6, TRUE)
  oCur.CharColor = RGB(200,200,200)	
  oCur.goRight(12, FALSE)
  oCur.goRight(6, TRUE)
  oCur.CharColor = RGB(200,200,200)		
End sub

To do this in a function, you would have to know which cell called the function. I do not know how to do that and I do not have time to look into it at the moment. I hope someone else can help you with that part, if you really do need a function.

[AndresSolar]

FJCC:

Many thanks!!! - I got the idea.

I'll call the function from my macro instead from the cell (else i'd format the function call itself) and after all strings are written to their cells iterate over the target cells.

[Lupp]

Just for clarification (to future visitors to this thread):
You cannot format parts of a string returned by a formula to a cell as its result without removing the formula.
In other words: only constant text content of cells can be formatted in portions.

[AndresSolar]

Lupp:

I'll post the scripts once i finished testing.

What i'll do in my loop:

- Change the function to a sub and pass the input range (of the former function) to that sub.
- write the result-string to the former source cell (where the function was called before)
- apply the character-color pattern to the former source cell

[AndresSolar]

FJCC:

your example code only changes the color of the first 6 characters.

Code: Select all

oCur.gotoStart(FALSE) : oCur.goRight(6, TRUE) : oCur.CharColor = RGB(200,200,200)

the following commands are carried out without noise - but as well without color change:

Code: Select all

  oCur.goRight(12, FALSE) : oCur.goRight(6, TRUE) : oCur.CharColor = RGB(200,200,200)

Did i understand that right(?)

[AndresSolar]

...the second command fails - but that doesn't matter in that case.
I'll first set the Cell-Font Color and carry out one char color change for the characters 7 to 17.

Many thanks again!!

[AndresSolar]

Remark:

Although the method works surprisingly stable, it doubles the cycle time in my case (update of 40 elements a' 24 Chars) and LO (6.2.3.1) changes (reduces) the window resolution (?).

I'll use it if the sparkBars are not time critical.