Hi,
I am new to macro's. I am writing a macro that copies data from a sheet that contains conditional formatting.
In the target sheet, I delete all the data before I paste the copied data.
I even deleted columns after selecting all cells but the existing conditional formatting will not go away. (These gets accumulated each time the macro runs).
Please show me a way to remove all conditional formatting in a given sheet.
Really appreciate your support.
Thanks in advance.
Sulochana
How to remove conditional formatting?
How to remove conditional formatting?
OpenOffice 3.1 on Windows 7
Re: How to remove conditional formatting?
Is that macro RECORDED?I am writing a macro that copies data from a sheet that contains conditional formatting.
You can WRITE (or RECORD) macros to copy data only, but not the format.
If you record the macro:
Use the "Paste special" - "unformatted text" option (from menu, when you record the macro).
If you write the macro:
Use the API functions to get 'value' or 'formula' from the desired cell(s).
http://forum.openoffice.org/en/forum/vi ... f=5&t=7915
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How to remove conditional formatting?
Thanks for the suggestion.
The macro is a combination of recording and writing.
I can paste values and formulas and ensure that the conditional formatting is not copied to the new sheet.
But I put in a conditional formatting to the new sheet through my macro. Therefore, each time I run the macro, the conditional formatting gets accumulated.
Please show me a way to remove all conditional formatting in a sheet.
Thank you
The macro is a combination of recording and writing.
I can paste values and formulas and ensure that the conditional formatting is not copied to the new sheet.
But I put in a conditional formatting to the new sheet through my macro. Therefore, each time I run the macro, the conditional formatting gets accumulated.
Please show me a way to remove all conditional formatting in a sheet.
Thank you
OpenOffice 3.1 on Windows 7
Re: How to remove conditional formatting?
You can find the answer to this question and many thousands of similar ones all by your own with a little help from http://extensions.services.openoffice.o ... roject/MRI
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to remove conditional formatting?
I tried but could not find out how to use the suggested method.
Any help is greatly appreciated.
Thanks,
Sulochana
Any help is greatly appreciated.
Thanks,
Sulochana
OpenOffice 3.1 on Windows 7
Re: How to remove conditional formatting?
The MRI extension is the tool which lets me find all properties and methods for every office object. If you can not use that tool you should give up all macro programming.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 56
- Joined: Wed Aug 12, 2009 1:14 pm
- Location: London, UK
Re: How to remove conditional formatting?
Villeroy is correct in that the MRI tool is very useful, but I think there are other things you need to do as well.
1. Get hold of the Basic Programmers Guide and read it, particularly the chapter about spreadsheets.You can find a link to it at the top of the Macros and UNO API forum.
2. Get hold of the MRI extension and install. You can then start it off in a Calc spreadsheet from the menu by Tools, Add-Ons, MRI. Read how to use it, again starting in a Calc spreadsheet, by Help, Open Office.org help, Open Office.org Basic (in the drop down menu at the top left of the dialog box), Contents, MRI.
3. Look at this documentation of the API (http://www.openoffice.org/api/docs/comm ... le-ix.html). It documents services, interfaces, methods and properties. In particular use the Index (near the top). I looked for Conditional Format and then followed the link to it in Sheet Cell Range, and then clicked on the link there to XSheetConditionalEntries. Eventually I found the method "clear", which looked like a good bet.
4. When you have a clue as to what you are looking for then fire up MRI and try to find it. We are looking for a method "clear" , which when applied to a Conditional Format in Sheet CellRange hopefully will clear the CF's. (In fact using MRI it looks like we can actually apply it to a sheet, not just a Sheet Cell Range).
5. I also looked at the Developer's Guide from the link in 3. This gives a clue that we don't just need to "clear", but also reset the CF property, or as the developers guide puts it "After a conditional format is changed, it has to be reinserted into the property set of the cell or cell range."
6. Finally, I also searched this forum for Conditional Format, which provided a few useful links.
The result of all this is (from MRI with the macro name amended):
In order to test it I wrote the following (based on something I found from my search) so I had something to clear:
Let us know if it works for you.
1. Get hold of the Basic Programmers Guide and read it, particularly the chapter about spreadsheets.You can find a link to it at the top of the Macros and UNO API forum.
2. Get hold of the MRI extension and install. You can then start it off in a Calc spreadsheet from the menu by Tools, Add-Ons, MRI. Read how to use it, again starting in a Calc spreadsheet, by Help, Open Office.org help, Open Office.org Basic (in the drop down menu at the top left of the dialog box), Contents, MRI.
3. Look at this documentation of the API (http://www.openoffice.org/api/docs/comm ... le-ix.html). It documents services, interfaces, methods and properties. In particular use the Index (near the top). I looked for Conditional Format and then followed the link to it in Sheet Cell Range, and then clicked on the link there to XSheetConditionalEntries. Eventually I found the method "clear", which looked like a good bet.
4. When you have a clue as to what you are looking for then fire up MRI and try to find it. We are looking for a method "clear" , which when applied to a Conditional Format in Sheet CellRange hopefully will clear the CF's. (In fact using MRI it looks like we can actually apply it to a sheet, not just a Sheet Cell Range).
5. I also looked at the Developer's Guide from the link in 3. This gives a clue that we don't just need to "clear", but also reset the CF property, or as the developers guide puts it "After a conditional format is changed, it has to be reinserted into the property set of the cell or cell range."
6. Finally, I also searched this forum for Conditional Format, which provided a few useful links.
The result of all this is (from MRI with the macro name amended):
Code: Select all
Sub CFClear
Dim oSheets As Variant
Dim oObj1 As Variant
Dim oConditionalFormat As Variant
oSheets = ThisComponent.getSheets()
oObj1 = oSheets.getByIndex(0)
oConditionalFormat = oObj1.ConditionalFormat
oConditionalFormat.clear()
oObj1.setPropertyValue("ConditionalFormat", oConditionalFormat)
End Sub
Code: Select all
Sub CFAdd
Dim oSheets As Variant
Dim oObj1 As Variant
Dim oCellRangeByName As Variant
Dim oConditionalFormat As Variant
Dim oNewCondition(3) as new com.sun.star.beans.PropertyValue ' Create New Property Value
'Set range and CF
oSheets = ThisComponent.getSheets()
oObj1 = oSheets.getByIndex(0)
oRange = oObj1.getCellRangeByName("A1:Z100")
oConditionalFormat = oRange.ConditionalFormat
'Set new CF condition
oNewCondition(0).Name = "Operator"
oNewCondition(0).Value = com.sun.star.sheet.ConditionOperator.BETWEEN
oNewCondition(1).Name = "Formula1"
oNewCondition(1).Value = "1"
oNewCondition(2).Name = "Formula2"
oNewCondition(2).Value = "5"
oNewCondition(3).Name = "StyleName"
oNewCondition(3).Value = "Heading" 'just use one of the inbuilt styles; can define your own
' Add New Conditional Format to CF
oConditionalFormat.addNew(oNewCondition())
oRange.setPropertyValue("ConditionalFormat", oConditionalFormat)
End Sub
AOO 4.1.10
macOS Big Sur version 11.6.2
macOS Big Sur version 11.6.2