2010/8/29 Andy Chaplin <perfect-engl...@gmx.de>:
> On 08/28/2010 09:49 PM, Johnny Rosenberg wrote:
>>
>> 2010/8/28 Andy Chaplin<perfect-engl...@gmx.de>:
>>
>>>
>>> On 08/28/2010 05:45 PM, Johnny Rosenberg wrote:
>>>
>>>>
>>>> 2010/8/28 Jean-Baptiste Faure<jbf.fa...@laposte.net>:
>>>>
>>>>
>>>>>
>>>>> Le 28/08/2010 16:44, Andy Chaplin a écrit :
>>>>>
>>>>>
>>>>>>
>>>>>> Hi all
>>>>>>
>>>>>> As far as I can see it's only possible to have 3 levels of conditional
>>>>>> formatting. Does anyone know of a trick or workaround to get more
>>>>>> conditions to apply. Ideally i would like six and any suggestions
>>>>>> would be gratefully appreciated.
>>>>>>
>>>>>>
>>>>>
>>>>> You can use formula with nested IF() and STYLE() functions.
>>>>>
>>>>> JBF
>>>>>
>>>>>
>>>>>
>>>>
>>>> Absolutely.
>>>> And if you want different formats for different text strings, such as
>>>> names if people or something, you can create a style with the same
>>>> name for each person and just add the style directly… For example:
>>>> =Sheet1.A1 + STYLE(A1)
>>>> Except that if Sheet1.A1 is empty, 0 will be displayed rather than an
>>>> empty cell.
>>>>
>>>> The following might work:
>>>> =IF(Sheet1.A1="";"";STYLE(A1))
>>>>
>>>> But I don't know if this is doable in Andy’s situation.
>>>>
>>>>
>>>> Regards
>>>>
>>>> Johnny Rosenberg
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: users-unsubscr...@openoffice.org
>>>> For additional commands, e-mail: users-h...@openoffice.org
>>>>
>>>>
>>>>
>>>>
>>>
>>> Thanks to you both for your prompt replies (which actually solve another
>>> problem I was working on!)
>>>
>>> In this case, however, it doesn't seem to do what I want (which I didn't
>>> explain in my original query).
>>>
>>> What I have is a table with parts listed vertically and dates
>>> horizontally.
>>> Each part can be in 1 of 6 different states on any given day. What I'm
>>> trying to do is to set up a sheet whereby if you put, for example, A in a
>>> cell the backgound is red, B is blue, C is green, D is yellow and so on.
>>>
>>> ... actually, as I've been typing this, I've worked out that this could
>>> be
>>> done using your proposal by having a data entry sheet and a separate
>>> sheet
>>> for display and nested IFs, but is there a way of doing this without the
>>> intermediate sheet?
>>>
>>
>> You don't need any IFs if you just name your styles properly, but I
>> understand what you mean. Unfortunately I didn't find a solution to
>> this problem when I experimented with this a few years ago, but if I
>> recall correctly I filed a feature request about this, I think.
>>
>> If you are familiar with OpenOffice.org Basic programming you could
>> write a macro that solves your problem. Since you only need six
>> different states you could write a macro that does the following:
>> — Open a dialogue box with six radio buttons marked A, B, C, D, E and F.
>> — When you selected one of them by either click it or hit a key on
>> your keyboard, for example A, and you then hit Enter or click OK, the
>> letter will automatically be filled in the currently selected cell and
>> the background colour will automatically be adjusted.
>> — You could associate the macro with a keyboard combination, such as
>> Ctrl+Alt+a.
>>
>> Another suggestion is to write six macros, one for each state. One
>> fills in an A and the right colour for A, one does the same for B. No
>> dialogue bos is then required and you associate the macros to six
>> different keyboard combinations. I found that these six combinations
>> are not taken, at least not on my system:
>> Alt+a
>> Alt+b
>> Alt+c
>> Alt+d
>> Alt+e
>> Alt+f
>>
>> Also, these combinations are not taken:
>> Ctrl+Alt+a
>> Ctrl+Alt+b
>> Ctrl+Alt+c
>> Ctrl+Alt+d
>> Ctrl+Alt+e
>> Ctrl+Alt+f
>>
>>
>> Isn't that very convenient…?
>>
>> Here are the macros required, I have tested them. They also require
>> that you create six styles named A, B, C and so on. That makes it more
>> flexible, so you can change colours of backgrounds, characters etc,
>> without having to change the code.
>>
>> REM ***** BASIC *****
>>
>> Option Explicit
>>
>> Sub A
>> setCellProperties("A")
>> End Sub
>>
>> Sub B
>> setCellProperties("B")
>> End Sub
>>
>> Sub C
>> setCellProperties("C")
>> End Sub
>>
>> Sub D
>> setCellProperties("D")
>> End Sub
>>
>> Sub E
>> setCellProperties("E")
>> End Sub
>>
>> Sub F
>> setCellProperties("F")
>> End Sub
>>
>> Sub setCellProperties(State As String)
>> Dim oCell As Object
>>
>> oCell=ThisComponent.getCurrentController().getSelection()
>> If oCell.SupportsService("com.sun.star.sheet.SheetCell") Then '
>> Selection is a single cell
>> oCell.setString(State)
>> oCell.setPropertyValue("CellStyle",State)
>> Else
>> MsgBox "Please select one cell only!"
>> EndIf
>> End Sub
>>
>>
>>
>> So what happens, when you associate Alt+A with the macro A and so on,
>> is that when you hit Alt+A, style A is applied to the selected cell
>> and an ”A” is also entered in the cell automatically. So the extra
>> work for you when you set everything up, is that you have to hold the
>> Alt key when you enter your letter. Maybe that is an acceptable
>> compromise?
>>
>>
>>
>> Kind regards
>>
>> Johnny Rosenberg
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscr...@openoffice.org
>> For additional commands, e-mail: users-h...@openoffice.org
>>
>>
>>
>
> WOW!
>
> Thank you so much for that. I've got a lot on my plate for today, but I'll
> try it out either this evening or tomorrow and get back to you.
>
I am actually working on another solution right now with listeners.
That would be very convenient if it works. There seems to be some
known difficulties though, so I guess there need to be some ”ugly”
code involved.
The data you want to colour, where is it located? Is all of it in the
same row or the same column? In that case, which one? The problem is
that you can assign a listener to a specific cell, but then you need
one listener per cell. You can also assign one listener to a specific
cell range, but then you don't know which cell was changed, only that
one (or more) in that range was changed, so I somehow need to compare
”before change” and ”after change” which could be time consuming. On
the other hand, if the range isn't too big, the macro could just go
through every cell in the range and set the colours accordingly.
Regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.org
For additional commands, e-mail: users-h...@openoffice.org
