How to conditional format a cell in a form

Creating and using forms
Post Reply
GJM
Posts: 56
Joined: Thu Feb 21, 2008 6:19 pm

How to conditional format a cell in a form

Post by GJM »

Is there a way to have a cell change color in a form when a certain value is inserted into it? ie. if there is a value greater than 1 the cell changes color to green.

Thanks
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to conditional format a cell in a form

Post by Villeroy »

I don't think you can do this with a cell in a table grid. It is possible to format a single control (text/check/list/combo box etc.) by macro.
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
GJM
Posts: 56
Joined: Thu Feb 21, 2008 6:19 pm

Re: How to conditional format a cell in a form

Post by GJM »

I was hoping to conditionally format certain cells in a form I was going to create for "entering" data.

It is not important for the particular cell in the "table grid" to change at all as I wasn't planning to look at the table that held the data directly very often.

I will do some looking around for how to do it with a macro (I haven't learned to use them yet).

If you could point me at good place to start reading up on how to use them, that would be great.

Thanks
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: How to conditional format a cell in a form

Post by DrewJensen »

Hi,

First Villeroy is quite correct - you can not do this with a table grid control, even on a form.

You can do this with single controls.

As an example perhaps this will help.

Code: Select all

const cPale_Yellow = 16777161
const cNo_Highlight = -1

Sub onLoadDataForm( oEvt as Object )
   ' this is just an entry point for the event
   ' when loading of a top level data form
   PrettyForm( oEvt.Source, cPale_Yellow, TRUE )
end sub

sub PrettyForm( oForm as object, OPTIONAL iHighlightRequired as double, OPTIONAL bSetDisplaySize as boolean   )
   ' for each colum on the form
   ' copy the description from the bound column to the control
   ' help text proeprty
   dim enControls as Object
   dim curColumn as Object
   dim CurControl as Object   
   dim setDisplaySize
   dim HighlightRequired
   
   
   if isMissing( iHighlightRequired ) then
   	HighlightRequired = -1
   else
   	HighlightRequired = iHighlightRequired
   end if
      
   if isMissing( bSetDisplaySize ) then
   	setdisplaySize = FALSE
   else
   	setDisplaySize = bSetDisplaySize
   end if
   
   enControls = oForm.CreateEnumeration
   
	while enControls.hasmoreelements
		CurControl = enControls.NextElement
		prettyControl( CurControl, HighlightRequired, SetDisplaySize )
		if CurControl.ServiceName = "stardiv.one.form.component.Grid" then
			enGrid = CurControl.createEnumeration
			while enGrid.hasmoreelements
				prettyControl( enGrid.NextElement, -1, SetDisplaySize )
			wend
		end if
		if CurControl.ServiceName = "stardiv.one.form.component.Form" then
			PrettyForm( CurControl, HighlightRequired, SetDisplaySize )
		endif
	wend   
end sub 


sub PrettyControl( aControl as variant, aReqColor as double, setDisplaySize as boolean )
	if 	aControl.ServiceName = "stardiv.one.form.component.Edit" _
		or	aControl.ServiceName = "stardiv.one.form.component.TimeField" _
		or	aControl.ServiceName = "stardiv.one.form.component.DateField" _
		THEN
		if aControl.BoundField.isNullable = 0  and aReqColor <> -1 then
			aControl.BackgroundColor = aReqColor
		end if
		aControl.ReadOnly = aControl.BoundField.IsAutoIncrement
		if aControl.BoundField.TypeName = "VARCHAR"_
			OR aControl.BoundField.TypeName = "CHAR"_
			THEN
			if setDisplaySize then
				aControl.MaxTextLen = aControl.BoundField.DisplaySize
			end if
		end if
		if aControl.HelpText = "" then
			aControl.HelpText = aControl.BoundField.Helptext
		end if
	end if
end sub
To use the above macros:

From any OpenOffice.org window do
  1. Tools > macros > Organize dialogs
    Select the tab Libaries
    Click on New
    name the new library PrettyForm
    With the new library "PrettyForm" highlighted click on Edit
    Copy the above macros and paste into the basic editor
    Click save
Now open any form you want to use it with in design edit mode.
  1. Select any control on the Main form
    right click and select "form"
    switch to the Events tab
    click on the button to the far right of the event "When Loading"
    Browse to your PrettyForm library and select the sub procedure onLoadDataForm
    Save your form and close it.
Now open your form in data entry mode.

-------------------------------------------------------

Just in case you have any troubles with the above here is the whole thing as a couple of files.


Here is basic library as an extension
Save this to disk
From any OpenOffice.org window select Tools > Extensions
Select Add
Browse to the prettyform.zip file - it will install the library
prettyform.zip
pretty form basic library
(1.97 KiB) Downloaded 353 times
Here is a database that uses the library
prettyform.odb
example database
(13.86 KiB) Downloaded 370 times
Open the form and notice the three required fields are highlighted
Also move the mouse around and notice that tool tip help has been setup
To see where the help text comes from, look at the patient table definition.

HTH
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
GJM
Posts: 56
Joined: Thu Feb 21, 2008 6:19 pm

Re: How to conditional format a cell in a form

Post by GJM »

To DrewJensen - Thankyou for your response.

After your response, I wish you could have seen my "Deer in the headlights look" :shock: :lol:

I have never played with code directly, but I'll give it a go :D Gotta start somewhere sometime :lol:

I'll give it a try in the next couple of days and let you know how I make out with it again.

Again, THANK YOU for your time and your examples 8-) for me to play with.

Cheers,

GJM
GJM
Posts: 56
Joined: Thu Feb 21, 2008 6:19 pm

Re: How to conditional format a cell in a form

Post by GJM »

I am not sure if I am doing something wrong but the color change that I see is constant (it doesn't vary with a value that I imput).

I think I am accomplishing the same thing by just selecting the field properties and changing background color for that field.

I tried the samples that you gave me and it seems to do the same thing (whether I try it on my form or the one I downloaded)

Should I be doing something else?

Sorry for the bother.

Cheers,

GJM
kyrie
Posts: 1
Joined: Sun May 24, 2015 2:52 pm

Re: How to conditional format a cell in a form

Post by kyrie »

Hello, I am trying to do something similar, I know nothing about macros but this was very helpful. I tried to change it t fit what need but was unsuccessful.
What I need:
On the form i need a pull down menu that when Props is selected the background color blue, and when costumes is selected it is green.

What I tried:
I copied and pasted the code into the library, changed color to blue and value to prop. Then in the form made combo box then went to events and added the macro to execute action. but nothing changes. even when I did not change the copied information.

please help me understand what I am doing wrong. thank you
openoffice 4.1 on mac
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to conditional format a cell in a form

Post by Villeroy »

The formatted field provides some conditional formatting by means of number format codes for numeric values, dates, booleans etc. as documented in the Calc help on number format codes.
GJM wrote:Is there a way to have a cell change color in a form when a certain value is inserted into it? ie. if there is a value greater than 1 the cell changes color to green.

Thanks
With thousands separator and 2 decimal digits and green font color if >1:
[GREEN][>1]##0.00;[BLACK]##0.00
 Edit: played around with conditional number format codes and it seems that you can have 2 conditions in 3 sections ( [1]first;[2]second;else ). See attachment. 
Attachments
conditionally_formatted_fields.odb
(26.79 KiB) Downloaded 214 times
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
Post Reply