2010/8/28 Johnny Rosenberg <[email protected]>: > 2010/8/28 Andy Chaplin <[email protected]>: >> On 08/28/2010 05:45 PM, Johnny Rosenberg wrote: >>> >>> 2010/8/28 Jean-Baptiste Faure<[email protected]>: >>> >>>> >>>> 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: [email protected] >>> For additional commands, e-mail: [email protected] >>> >>> >>> >> >> 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 >
Actually I have another solution that is also a compromise and maybe not acceptable: Create a macro that goes through an entire row or column or sheet searching for cells containing either A, B C, D, E or F and apply the appropriate style to them. It's possible to start a macro everytime the sheet is saved, for example, but unfortunately not for each time a cell value is changed. It is, however, possible, I think, to work with ”listeners” in a macro. I guess that should mean that you have the macro started automatically when the spreadsheet is opened and running all the time, and every time a cell is changed the macro should know which one was changed and apply the appropriate style accordingly. Unfortunately I don't have a clue how to do this. One of the problems I can see is that when a macro is running I seem not to be able to work with the spreadsheet, but maybe that's minor problem, I don't know. Kind regards Johnny Rosenberg --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
