[Solved] Chg (cell) backgrd color with macro from a formula

[dmyers7518]

(Can't do it) I want to change a cell's background color by calling a macro (function) from a formula.

The macro works fine if I run it from the macro menu or if I run it from the execute event on a button control. When I run the macro from a formula, I can hide/show a row or column, etc. but I can not update a value or change the background color. I have a message box display in the macro to verify that it runs. I have had the same results when I used a default spreadsheet and made no changes other than enter the formula "If(A2=2;CHG();"")" in A1 and then entered a 2 in A2.

I have gone through all the on-line documentation I can find which is where this macro came from. All say the same thing which is that calling a macro as a function from a formula and changing the background color is supposed to work.

Yes, I know how to apply a STYLE from conditional formatting but I am trying to avoid having to setup a STYLE for each data type and color combination I am using. If I could setup a STYLE that changed only the background color, that would work but I don't believe that can be done.

I would greatly appreciate it if someone could tell me

1) what I am doing wrong in the macro or
2) it can't be done or
3) it is a bug in OpenOffice 3 or
4) how to setup a style that only changes the background color

The macro I am using is in Basic. I don't care if the solution needs to be in UNO format, Python, BeanShell, or Java script.

Thanks in advance for any help I can get. Here is the macro:

Code: Select all

REM  *****  BASIC  *****

Option Explicit

Sub Main

End Sub

function CHG()

Dim oDocument As Object, oSheet As Object, oCell As Object
oDocument=ThisComponent
oSheet=oDocument.Sheets(0)
oCell=oSheet.getCellRangeByName("D2")
oCell.setValue(23658)
'oCell.NumberFormat=2 '23658.00
'oCell.SetString("oops")
'oCell.setFormula("=FUNCTION()")
oCell.IsCellBackgroundTransparent = false
oCell.CellBackColor = RGB(255,141,56)

MsgBox "out"

end function

[FJCC]

I seem to remember that functions cannot change cell attributes. I could be wrong, but I did duplicate the failure of a function to change the cell background. I'm puzzled by your this part of your post

Yes, I know how to apply a STYLE from conditional formatting but I am trying to avoid having to setup a STYLE for each data type and color combination I am using. If I could setup a STYLE that changed only the background color, that would work but I don't believe that can be done.

Creating a style that only changes the background color is done by bringing up the styles list (F11), right clicking on the cell style currently used in the cell, selecting New, giving the new style a name and setting the background to the desired color. This new style will have all the attributes of the original style except for the background. I don't see how that gets you out of defining a style for each data type and color combination. On the other hand, it is quicker to create many styles than to write and debug a robust macro.
It might help if you explained your overall goal. There might be a different approach.

[kingfisher]

I would be interested in reading the documentation you mention.

My understanding is that a Function returns a value. Thus, the final line of code should be along the lines CHG() = {value}.

You can create a style which differs only or additionally in the background colour.

[acknak]

I remember that someone (Villeroy, IIRC) posted some code to create a set of styles from a set of properties (like background colors). Maybe something like that could work?

You can also make a lookup table for the styles. Although that's not quite a perfect solution, it is a clean way to deal with a lot of different styles.

[B Marcelly]

I have gone through all the on-line documentation I can find which is where this macro came from. All say the same thing which is that calling a macro as a function from a formula and changing the background color is supposed to work.

Where did you find this stupidity ?
No, it does not work. It has never worked.
Functions called by a formula are only supposed to return a string or a double. That's all.
See Bug 31627, classified as CLOSED WONTFIX.

[dmyers7518]

Thank you to the people who responded to my post. Replys in order:

FJCC: What you are describing isn't a STYLE that changes only the background color, it is a STYLE that changes a cell to all of the settings of the original cell that the STYLE was created from. The effect of changing only the background color is only true if the only difference between the original cell and the new cell is the background color. You are correct that creating a large number of STYLES is quicker than debugging a macro, but only if it applies to one spreadsheet. But it doesn't help me learn the limits of CALC or macros or learn how to build and debug macros. As far as my goals, I am building a time keeping system for myself in CALC and using that as a tool to learn more about CALC, macros, and what their limits are. I am using background color to visually indicate fields that need to have data entered based on the type of work, fields that are out of balance, type of work, etc. As each piece of data is entered, I am using conditional formatting and the STYLE function to do a table search for the STYLE that meets the condition and applying it to the cell.

KINGFISHER: Do a (search engine) search for CALC and macros. Between the documentation project of OpenOffice, Wikipedia, book authors, and various blogs and sites, you will find lots of documentation. Much of it is duplication. As far as the returning value of a function, that was my thought also. But, if that was the problem, then (I think) the macro shouldn't have worked when run from the macro menu or a control's execute event.

ACKNAK: Thanks, that is the method that I started with. I started looking for another solution when I realized that I would need a separate style for each color/data type combination.

B MARCELLY: I bow before your obvious supiority. But, I humbly regret to say that, for us without your infinite knowledge, when we don't already know the answer, we have to ask. Take your attitude and shove it. The only dumb question is the one that doesn't get asked.

[dmyers7518]

Oh, and by the way, B MARCELLY, functions can return values, formats, strings, and formulas according to some of the documentation I found, Andrew Pitonyak being one source and the macro written by Sasa Kelecevic. It was logical to ask if formats could be changed, why not background color. It was also logical to note that Sasa had background color commented out in his/her (my apology to Sasa) macro. Therefore, when setting the background color didn't work, it was logical to ask what actually does work.

[Villeroy]

STYLE is the built-in function which able to modify the formatting attributes of a formula cell.
Then there is conditional formatting and the combination of conditional formatting with function STYLE.

[Keith Wild]

If you are referring to the macro written by Sasa Kelecevic in section 6.3 of Useful Macro Information by Andrew Pitonyak then this is a sub, not a function.

[kingfisher]

After posting earlier, I remembered that I have for years used a function to set a particular border format. (I'll change that to apply a new cell style instead, now that I've remembered it). However, I apply that function by using a macro. For what it may be worth, this is the line that applies the format which (in my ignorance) I created instead of a cell style:

Code: Select all

If iDay = 6 Then : oTarget.TableBorder = FridayBorder

EDIT: On second thoughts, I cannot use a single cell style because the target is a row containing numbers in a variety of formats.

[dmyers7518]

To Keith Wild, you are correct. I ran it as originally written (as a sub) then modified it and ran it as a function. I got the same results.