| View previous topic :: View next topic |
| Author |
Message |
jcurrie33
General User
Joined: 04 Nov 2006
Posts: 6
|
 Posted: Sat Nov 04, 2006 12:35 pm Post subject: Macro : Applying Styles Based on Cell Contents |
 |
|
Hello again,
My desire is to create a macro that does the following:
| Code: |
For all cells in range B3:H98 (
If current cell contents = "Text1"
Set style for current cell = "Style1";
If current cell contents = "Text2"
Set style for current cell = "Style2";
// Same, for a total of maybe 12 style/text combinations
)
|
The syntax for doing this escapes me. I don't even know where a good resource for the available function calls would be.
My thoughts are something like the following (I know this isn't right at all, but just the thinking behind it - I know I need to use the Basic):
| Code: |
for (int i=3; i < 99; i++)
{
for (int j=2; j < 8; j++)
{
if (getcell( j, i ).value == "Text1")
getcell( j, i ).style = "Style1";
// ..........
}
}
|
Anyone know the OpenOffice Basic syntax for doing something like this? Or a resource where I can find it? Thank you SO much! |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Sat Nov 04, 2006 3:54 pm Post subject: |
|
|
This does not require a macro. It's exactly what conditional formatting does (Format>Conditional...). The STYLE() function is even more flexible.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
jcurrie33
General User
Joined: 04 Nov 2006
Posts: 6
|
| Posted: Sun Nov 05, 2006 7:46 am Post subject: |
|
|
| Actually, I would like to have a macro to do this. Either way, how is it possible to set the cell's style based on the cell's own contents? All other examples I've seen are for setting a cell's style based on the contents of another cell. Thanks. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Sun Nov 05, 2006 7:57 am Post subject: |
|
|
Select the range to be formatted conditionally.
Menu:Format>Conditional...
Criterion1.Value: something like 0, <0, >=0, "abcde", PI()
If you use formulas, referring to other cells, relative references refer to the currently active cell.
If you apply two or three conditions, they are processed top down until one condition evaluates to True.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
jcurrie33
General User
Joined: 04 Nov 2006
Posts: 6
|
| Posted: Sun Nov 05, 2006 4:17 pm Post subject: |
|
|
Thanks, but maybe I can clarify further...
I need to have multiple (more than 3) conditional formatting "conditions," and therefore cannot use the conditional formatting menu. I need to directly apply the style using the STYLE() function, but in a macro that updates all cells in a range each time the contents of a cell in the range is updated.
There are about 12 different values that each cell in the range can have, and therefore 12 different styles that I need to be able to apply. 12 conditions. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Sun Nov 05, 2006 5:10 pm Post subject: |
|
|
Check this out:
=RAND()+STYLE(VLOOKUP(CURRENT();lookupStyles;2;1))
where lookupStyles is a named range:
0 RGB_FF0080
0,1 RGB_FF00FF
0,2 RGB_00FFFF
0,3 RGB_0000FF
0,4 RGB_00807F
0,5 RGB_807F00
0,6 RGB_FFFF00
0,7 RGB_00FF80
0,8 RGB_00FF00
0,9 RGB_FF7F80
1 RGB_FF0000
and RGB_XXXXXX are colored styles.
Most recent on function STYLE(): http://www.oooforum.org/forum/viewtopic.phtml?t=47053
Another hint: Function STYLE() works with formula-cells, conditional formatting with all cells. The limitation to max. 3 conditions is in the GUI only ("Excel-compatibility"?). A macro can add more. I did conditional formatting with 15 conditions, but can't find the doc now.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
acknak
Moderator
Joined: 13 Aug 2004
Posts: 4312
Location: ~ 40°N,75°W
|
| Posted: Wed Nov 08, 2006 11:34 am Post subject: |
|
|
| Villeroy wrote: | | This does not require a macro. It's exactly what conditional formatting does (Format>Conditional...). The STYLE() function is even more flexible. ... The limitation to max. 3 conditions is in the GUI only ("Excel-compatibility"?). A macro can add more. I did conditional formatting with 15 conditions, but can't find the doc now. |
What the OP wants has to be done using conditional formatting, but the limit of three different styles is not enough.
I would like to propose an enhancement to the conditional formatting capability, so that the formula entered in the Format > Conditional Formatting dialog can return a string which is used as the style name. This would allow conditional formatting with no limit on how many styles can be used.
I think the UI for this could be as simple as adding one more entry to the "Cell Style" drop down, which would be "Use Formula Result".
Does this sound reasonable? If so, I will file it as an enhancement request. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Wed Nov 08, 2006 11:45 am Post subject: |
|
|
Yeah, this sounds reasonable indeed. I started writing a package with an enhanced dialog. I hope I find some time around Xmas. Rainbow-Colors, depending on input-values are really cool 
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Wed Nov 08, 2006 7:50 pm Post subject: |
|
|
Your macro could be along these lines:
| Code: | Sub applyStyles
Dim oSelect as Object, lCols as Long, lRows as Long, c as Long, r as Long, _
oCell as Object, sCell as String
oSelect = ThisComponent.CurrentSelection
lCols = oSelect.Columns.Count - 1
lRows = oSelect.Rows.Count - 1
For c = 0 to lCols
For r = 0 to lRows
oCell = oSelect.getCellByPosition( c, r )
sCell = oCell.getString
Select case sCell
' *** substitute texts for "alpha" etc and correct the style names; retain double quotes.
Case "alpha"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Blue" ) Then
oCell.CellStyle = "_Blue" : End If
Case "bravo"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Green" ) Then
oCell.CellStyle = "_Green" : End If
Case "Charlie"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "delta"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "echo"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "foxtrot"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "golf"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "hotel"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "India"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "Juliet"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "kilo"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
Case "Lima"
If ThisComponent.StyleFamilies.CellStyles.hasByName( "_Titles" ) Then
oCell.CellStyle = "_Titles" : End If
End select
next r
next c
End Sub |
I have not used that, of course; it's a mere nestling at this stage but capable of flight. If you are confident that all your named styles will exist, you could leave out those conditions.
See this and the following post for some sources of information about StarBasic.
You should ask about macros in the Macros and API forum.
_________________
search forum by month |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Thu Nov 09, 2006 11:29 am Post subject: |
|
|
OK, I found my old draft and created a simple self-contained example in Basic.
Run this:
| Code: |
REM ***** BASIC *****
Option explicit
Sub MakeRainbowExample()
'calls: makeMyRGB_Styles, getRGB_Name, getConditionalEntry
Dim oSheet,aColors() as Long,aConditionalEntries(),oStyleFamilies,oConditionalFormat,i%,x%
Dim aCell as new com.sun.star.table.CellAddress
'new spreadsheet
thisComponent = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,Array())
'colors
aColors() = Array(8388736,255,3394662,2359075,15132236,16776960, _
16750950,15425853,16711680,16711935)
'---------------------------------------
makeMyRGB_Styles(thisComponent,aColors())
oSheet = thisComponent.getCurrentController.getActiveSheet
oSheet.setName("rainbow")
oStyleFamilies = thisComponent.StyleFamilies.getByName("CellStyles")
oConditionalFormat = oSheet.ConditionalFormat
for i = 0 to uBound(aColors())
aConditionalEntries() = getConditionalEntry(cStr(i +1), "", _
com.sun.star.sheet.ConditionOperator.LESS, aCell)
oConditionalFormat.addNew(aConditionalEntries())
oConditionalFormat.getByIndex(i).setStyleName(getRGB_Name(aColors(i))
next
oSheet.ConditionalFormat = oConditionalFormat
with oSheet.getCellRangeByPosition(0,0,99,999)
.setArrayFormula("RAND()*10")
' .setDataArray(.getDataArray())
end with
Msgbox "Entire sheet has 10 conditional formats. A1:CV100 has an array. Hit F9"
End Sub
Sub makeMyRGB_Styles(oDoc,aColors())
'calls: getRGB_Name,addReplaceStyle
Dim sN$,i%
Dim aP(0) as new com.sun.star.beans.PropertyValue
aP(0).Name = "CellBackColor"
for i = 0 to uBound(aColors())
aP(0).Value = aColors(i)
sN = getRGB_Name(aColors(i))
addReplaceStyle(oDoc,"CellStyles",sN,aP())
next
End Sub
Function getRGB_Name(l&)
Dim sHex$
sHex = hex(l)
getRGB_Name = "RGB_"& string(6 -len(sHex),"0") & sHex
end function
Function getConditionalEntry(sF1$,sF2$,lOp&,oSrc)
Dim aProps(3) as new com.sun.star.beans.PropertyValue
aProps(0).Name = "Formula1"
aProps(0).Value = sF1
aProps(1).Name = "Formula2"
aProps(1).Value = sF2
aProps(2).Name = "Operator"
aProps(2).Value = lOp
aProps(3).Name = "SourcePosition"
aProps(3).Value = oSrc
getConditionalEntry = aProps()
End Function
Function addReplaceStyle(oDoc,sFamily$,sName$,aProps()) as Boolean
'on error goto errFalse
Dim oStyleFamilies,oStyle,i%
addReplaceStyle = False
oStyleFamilies = oDoc.StyleFamilies.getByName(sFamily)
if oStyleFamilies.hasByName(sName) then oStyleFamilies.removeByName(sName)
oStyle = thisComponent.createInstance("com.sun.star.style.CellStyle")
oStyleFamilies.insertByName(sName,oStyle)
for i = 0 to uBound(aProps())
oStyle.setPropertyValue(aProps(i).Name,aProps(i).Value)
next
addReplaceStyle = True
exit function
errFalse:
End function
|
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
acknak
Moderator
Joined: 13 Aug 2004
Posts: 4312
Location: ~ 40°N,75°W
|
| Posted: Thu Nov 09, 2006 7:06 pm Post subject: |
|
|
| Quote: | | Does this sound reasonable? If so, I will file it as an enhancement request. |
Filed as Issue 71392: Provide unlimited conditional formatting styles. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
