OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Using blank cells in Calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
wpeter2002
OOo Enthusiast
OOo Enthusiast


Joined: 08 Apr 2007
Posts: 145
PostPosted: Thu Sep 06, 2007 2:32 am    Post subject: Using blank cells in Calc Reply with quote
I’m using the cell background colour to indicate the importance of data in a Calc application. Sometimes there is a blank cell so I want to indicate that comparison data is invalid so I do this by turning the cell background colours Magenta. I have no problem doing this when the blank cell would normally contain a value but I can’t get it to work when the cell would normally contain a string.

The code below works okay for a cell that would normally contain a value:
Code:
Sub ABC
   Dim oCell1 as Object
   Dim oCell2 as Object
   Dim oCell3 as Object
   Dim oCell4 as Object
   oCell1 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(4, 4)
   oCell2 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(5, 4)
   oCell3 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(3, 4)
   oCell4 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(6, 4)
If oCell1.Value = 0 then
   oCell1.CellBackColor = RGB(255, 0, 255)
   oCell3.CellBackColor = RGB(255, 0, 255)
   oCell2.CellBackColor = RGB(255, 0, 255)
   oCell4.CellBackColor = RGB(255, 0, 255)
   End If
   If oCell2.Value = 0 then
   oCell1.CellBackColor = RGB(255, 0, 255)
   oCell3.CellBackColor = RGB(255, 0, 255)
   oCell2.CellBackColor = RGB(255, 0, 255)
   oCell4.CellBackColor = RGB(255, 0, 255)
   End If
End Sub

I expected the code below to work for a cell that would normally contain a string:
Code:
Sub ABC
   Dim oCell1 as Object
   Dim oCell2 as Object
   Dim oCell3 as Object
   Dim oCell4 as Object
   oCell1 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(4, 4)
   oCell2 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(5, 4)
   oCell3 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(3, 4)
   oCell4 = thisComponent.Sheets.getByName("SheetC").getCellByPosition(6, 4)
If oCell1.String = “” then
   oCell1.CellBackColor = RGB(255, 0, 255)
   oCell3.CellBackColor = RGB(255, 0, 255)
   oCell2.CellBackColor = RGB(255, 0, 255)
   oCell4.CellBackColor = RGB(255, 0, 255)
   End If
   If oCell2.String = “” then
   oCell1.CellBackColor = RGB(255, 0, 255)
   oCell3.CellBackColor = RGB(255, 0, 255)
   oCell2.CellBackColor = RGB(255, 0, 255)
   oCell4.CellBackColor = RGB(255, 0, 255)
   End If
End Sub

If you can see what I’m doing wrong please post.

Best regards

Peter
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Thu Sep 06, 2007 7:23 am    Post subject: Reply with quote
Simply use the built-in conditional formatting.
Back to top
View user's profile Send private message
wpeter2002
OOo Enthusiast
OOo Enthusiast


Joined: 08 Apr 2007
Posts: 145
PostPosted: Thu Sep 06, 2007 9:33 am    Post subject: Reply with quote
Sorry, I don't understand your reply.

Peter
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Thu Sep 06, 2007 9:37 am    Post subject: Reply with quote
There is already a feature, which can do exactly what you want. It's called "conditional formatting".
Back to top
View user's profile Send private message
wpeter2002
OOo Enthusiast
OOo Enthusiast


Joined: 08 Apr 2007
Posts: 145
PostPosted: Thu Sep 06, 2007 11:48 pm    Post subject: Reply with quote
Villeroy,
Thank you for your reply. I wasn’t aware of the conditional formatting capability. It seems very useful for values and blanks but not for strings. Thank you for your help.

Peter
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10065
Location: Germany
PostPosted: Fri Sep 07, 2007 2:38 am    Post subject: Reply with quote
wpeter2002 wrote:
Villeroy,
Thank you for your reply. I wasn’t aware of the conditional formatting capability. It seems very useful for values and blanks but not for strings. Thank you for your help.

Peter

Condition 1:
Change "Value" to "Formula"
Condition: $SheetC.E5=""
Apply style: magenta

Relative references in a formula condition are relative to the currently active cell.
Back to top
View user's profile Send private message
wpeter2002
OOo Enthusiast
OOo Enthusiast


Joined: 08 Apr 2007
Posts: 145
PostPosted: Fri Sep 07, 2007 4:48 am    Post subject: Reply with quote
Villeroy,
Thank you for the addition information. A very useful facility.

Best regards

Peter
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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