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
How to conditional format a cell in a form
Re: How to conditional format a cell in a form
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to conditional format a cell in a form
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
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
- 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
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.
To use the above macros:
From any OpenOffice.org window do
-------------------------------------------------------
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 Here is a database that uses the library 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
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
From any OpenOffice.org window do
- 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
- 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.
-------------------------------------------------------
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 Here is a database that uses the library 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
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: How to conditional format a cell in a form
To DrewJensen - Thankyou for your response.
After your response, I wish you could have seen my "Deer in the headlights look"
I have never played with code directly, but I'll give it a go Gotta start somewhere sometime
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 for me to play with.
Cheers,
GJM
After your response, I wish you could have seen my "Deer in the headlights look"
I have never played with code directly, but I'll give it a go Gotta start somewhere sometime
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 for me to play with.
Cheers,
GJM
Re: How to conditional format a cell in a form
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
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
Re: How to conditional format a cell in a form
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
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
Re: How to conditional format a cell in a form
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.
[GREEN][>1]##0.00;[BLACK]##0.00
With thousands separator and 2 decimal digits and green font color if >1: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
[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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice