Form control background colour through vba

[Martin]

Hi,

I have a Control on a form that is text either YES/NO if we need to reorder
or not, if the value in the control is Yes, then i want the background colour
to be red, , if the value in the control is No then i want the colour to be
green... IS this possible through VBA?

Here is my code below if you can make more sense of that:

Private Sub QtyOnHand_AfterUpdate()

'Reorder levels --- If QtyOnhand is lower than 3 then reorder = Yes
'Reorder levels --- If QtyOnHand is higher than 3 then reorder = No

If (Me.QtyOnHand) < 3 Then
Me.Reorder = "Yes"

Else

If (Me.QtyOnHand) > 3 Then
Me.Reorder = "No"


End If
End If

End Sub

Thanks in advance

martin

 

[Nikos Yannacopoulos]

Martin,

Try this:

If Me.QtyOnHand < 3 Then
Me.Reorder = "Yes"
Me.Reorder.BackColor = vbRed
Else
If (Me.QtyOnHand) > 3 Then
Me.Reorder = "No"
Me.Reorder.BackColor = vbGreen
Else 'QtyOnHand =3
Me.Reorder.BackColor = vbWhite
End If
End If

I assumed you wanted white for QtyOnHand = 3, but you can modify as you
like.

HTH,
Nikos

 

[Ofer Cohen]

Hi Martin,
Why not use the conditional formatting instead of seting the colors using VBA?

Check this link on "Conditional Formatting and Continuous Forms"

http://www.databasedev.co.uk/conditional_formatting.html

 

[Seth Schwarm]

Thanks for that link.

I wanted to change the backcolor of the Detail section according to the
value of a checkbox field.

I accomplished this by adding an unbound text box the size of my Detail
section and Sent it to Back using a command under the Format menu. I set the
backcolor of my textbox, then locked it and disabled it.

Then I added conditional formatting to this text box by using Expression Is.
My expression was [Active] - the name of my checkbox. I set it to change
the backcolor of my unbound text box to the color of my Detail section.

It worked like a charm. As soon as I would check or uncheck the Active
checkbox field the backcolor of the unbound text box would toggle between red
and black.

Thanks a million.

Seth Schwarm

 

[Nikos Yannacopoulos]

Seth,

Kind of an overkill... the form detail is an object on its own right,
with properties that you can manipulate, so no need for an extra
control. This does the job just fine:

Private Sub Check6_AfterUpdate()
If Me.Check6 Then
Me.Detail.BackColor = vbBlue
Else
Me.Detail.BackColor = vbGreen
End If
End Sub

HTH,
Nikos