I would like to reformat the numbers in a range of cells on a calc sheet based on the value of a cell on a different sheet. I can access the number on the different sheet but I cannot figure out the code to reformat the range of cells on new sheet as it is activated. If you can help me with the code to format the range of cells as “#0.0000” then I can deal with the rest.
With thanks for your help.
OldJack
Create one or more cell style(s) (manually) with the desired properties, and apply it by your macro code to the target cell range.
1 Like
Please upload your ODF type sample file here.
Using the Conditional Format feature is not enough for you? The CF uses the Cell styles too.
This is what I have so far. The FontWeight.BOLD works fine but the Number Formats do not work AT all.
Sub Format_Scores
Dim oCell as Object
Dim oLocalSettings as New com.sun.star.lang.Locale
Dim oNumberFormats as Object
Dim lKey as long
oCell = ThisComponent.Sheets.getByName("Play_Schedule").getCellRangeByName("N21:N36")
oCell.CharWeight = com.sun.star.at.FontWeight.BOLD
oLocalSettings.language = "fr"
oLocalSettings.country = "fr"
oNumberFormats = ThisComponent.NumberFormats
lKey = oNumberFormats.queryKey("#0.0000",oLocalSettings,true)
If lKey = -1 then
lKey = oNumberFormats.addNew("#0.0000",oLocalSettings)
End If
oCell.NumberFormat = lKey
End Sub
Your oCell object is a Cell Range object really. A Range has different properties and methods than a Cell object has.
And here is a macro what will apply an existing (predegined cell Style for a Cell Range:
REM ***** BASIC *****
option explicit
Sub Format_Scores
Dim oRange as Object
oRange = ThisComponent.Sheets.getByName("Play_Schedule").getCellRangeByName("N21:N36")
xray oRange
oRange.CellStyle = "MyCellStyle"
End Sub
.
The line of the code started "xray " is a checking for the properties of the programming object. (You can to REM or delete that code line after the checking, or if you have not XRAY TOOL yet).
I suggest you to install one of the excellent Object Inspection Tools: XRAY TOOL or MRI. Then you will able to examine the esisting properties methods and others of the programming objects.
.
And here is a sample file with the embedded macro code and the predefined Cell Style named “MyCellStyle” :
ApplyCellStylesBymacro.ods (9.5 KB)
(You must delete all of the manual (direct) formatting properties of the target range before… or you can do it by a macro.)
It works. 
Enter the number 123456 in cell N21. It appears as 12.3456.
If you wanted to display 4 characters after the fractional separator, then use comma ("0,0000").
By the way, you have a typo: com.sun.star.awt.FontWeight.BOLD
Thank you for that. But I don’t directly enter a number into the cell but rather the cell is formatted as a result of a value on another sheet (oFormat) and the value is derived from a formula that creates a number from entries in 4 other cells on the this (the active sheet) sheet. So although I am getting the result that I want, it displays with a comma instead of a decimal point. Can I make it display as a decimal rather than a comma?
If oFormat.getValue > 4 then
lKey = oNumberFormats.queryKey(“0,0000”, oLocalSettings, true)
If lKey = -1 then
lKey = oNumberFormats.addNew(“0,0000”, oLocalSettings)
End If
End If
oCell.NumberFormat = lKey
In this particular program I can live with this but I’d like to know if I can display it as a decimal.
Thanks again for your help.
Jack
You specify the localization fr-FR (France), respectively, the fractional separator is selected from the national French settings (comma).
If you need a dot separator, then indicate the appropriate localization, for example en-CA.
It shows me a dot as a separator:
Sub Format_Scores
Dim oCell as Object
Dim oLocalSettings as New com.sun.star.lang.Locale
Dim oNumberFormats as Object
Dim lKey as long
oCell = ThisComponent.Sheets.getByName("Play_Schedule").getCellRangeByName("N21:N36")
oCell.CharWeight = com.sun.star.awt.FontWeight.BOLD
oLocalSettings.language = "en"
oLocalSettings.country = "CA"
oNumberFormats = ThisComponent.NumberFormats
'mri oNumberFormats
lKey = oNumberFormats.queryKey("#0.0000",oLocalSettings,true)
If lKey = -1 then
lKey = oNumberFormats.addNew("#0.0000",oLocalSettings)
End If
oCell.NumberFormat = lKey
End Sub
As colleague @Zizi64 already noted, it is better to change the name of the oCell variable to oRange.
Thank you so much again. I had thought that it probably had something to do with the language and country thing but had and still don’t have any idea where to find these kind of codes. As for the variable, I had intended to do that when I got it all sorted out but was working on the solution by referring only to a single cell. It’s really great for and old novice like me to have guys like you out thre to help. There seems to be very few sources available that guys like me can find the answers to what should be relatively simple problems.
Thanks again and take care,
OldJack
PS: How did you know I’m in Canada? And where are you?
Your profile has as location Vancouver Island. Search engine tells this is in Canada.
Click on username - if user has entered a location then all will be revealed.