Select Conditional Formatting (already setup) to apply to subform from a Check Box

[DubaiDave]

Hi,

Have spent an hour on the web researching this and have got nowhere.

I would like to know how, or even if you can, turn on or off a conditional format setting already set up for a subform.

Scenario is I select a filter to a subform, apply it and at that time if a Check Box is true then Conditional Formating no1 (or 2 or 3) is also applied.

Any ideas?

Dubai Dave

 

[pbaldy]

In code? I've never tried to use it, but check out the FormatCondition Object in VBA help.

 

[Galaxiom]

The format conditions are referred to by their index.
The property is Enabled.
eg

Me!subformname!controlname.FormatConditions(0).Enabled = True

(The controlname is the "field" in datasheet view.)

Another way that avoids VBA is to include the checkbox state in the format condition.

 

[DubaiDave]

Thanks to pbaldy and Galaxiom !

Me!subformname!controlname.FormatConditions(0).Enabled = True worked.

This refers to a single control. Rather than using this ten times or so for all my controls, how can I set all controls in the Detail part of my subfrom at the same time?

I tried:

Dim cntrl As Control
For Each cntrl In Me!fmSafetyNoticeReportContForm.Form.Detail
cntrl.FormatConditions(1).Enabled = True
Next

But all variations I have tried so far throw up errors.

DubaiDave

 

[Galaxiom]

Dim cntrl As Control
     For Each cntrl In Me!fmSafetyNoticeReportContForm.Form.Detail
          [COLOR=blue]If cntrl.ControlType = acSubform Then[/COLOR]
               cntrl.FormatConditions(1).Enabled = True
          [COLOR=blue]End if[/COLOR]
      Next [COLOR=red]cntrl[/COLOR]

The if is to select a particular type of control. Those that don't have the FormatConditions(1) property would error out otherwise.

Other control types can be found here:
http://msdn.microsoft.com/en-us/library/aa224135(office.11).aspx

Or you can test for the existence of the property itself. Think this works.

If cntrl.FormatConditions(1) Then

The other way to select particular controls is to use their Tag property.
This is just a string you can add to the control and test against see if the code line should be applied.

If cntrl.Tag = "something" Then

I assume you are only needing to change the second condition. AFAIK all indexes start at (0).

 

[DubaiDave]

Thanks Galaxiom,

That helps, but I am not quite yet there.

My subfrom resides on a tabbed sheet on my main from (although according to Labans, this makes no difference to its referencing).

The code I am now using runs without error but does not invoke the Conditional Formatting.

Does anyone know why this does not work?

DubaiDave


Here it is:

Public Sub subConditionalFormat()
 
' [URL]http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx[/URL]
 
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngGreen As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim cntrl As Control
 
' Set up foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngGreen = RGB(0, 255, 0)
 
' Remove any existing format conditions.
Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Delete
 
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Closed")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Open")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "NoStatus")
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(0)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(1)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(2)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
Set objFrc = Nothing

 

[DubaiDave]

Thanks Galaxiom,

That helps, but I am not quite yet there.

My subfrom resides on a tabbed sheet on my main from (although according to Labans, this makes no difference to its referencing).

The code I am now using runs without error but does not invoke the Conditional Formatting.

Does anyone know why this does not work?

DubaiDave


Here it is:

Public Sub subConditionalFormat()
 
' [URL]http://msdn.microsoft.com/en-us/library/aa139965(office.10).aspx[/URL]
 
Dim objFrc As FormatCondition
Dim lngRed As Long
Dim lngGreen As Long
Dim lngWhite As Long
Dim lngBlack As Long
Dim cntrl As Control
 
' Set up foreground colors.
lngRed = RGB(255, 0, 0)
lngWhite = RGB(255, 255, 255)
lngBlack = RGB(0, 0, 0)
lngGreen = RGB(0, 255, 0)
 
' Remove any existing format conditions.
Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Delete
 
' Create three format objects and add them to the FormatConditions
' collection.
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Closed")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "Open")
Set objFrc = Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions.Add(acFieldValue, acEqual, "NoStatus")
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(0)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(1)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
With Me![fmSafetyNoticeReportContForm].Form![ClosedStatus].FormatConditions(2)
.Enabled = True
.BackColor = lngWhite
.ForeColor = lngRed
End With
 
Set objFrc = Nothing