help w conditional formatting in VBA

[Gaby Sandoval]

I am trying to apply conditional formatting. i have to use code
since
i have 10 different conditions. basically, i want to change the
color
of the font color and backgroud color of a text box (txtRoleName)
based on the value.

I tried writing an If statement on the details part of the form, but
i
can't seem to get it to work. I cant find a value that works. I
tried using *ctrl *space after i type in my texbox anme, but cant
find
anything that seems like it would work from the drop down that
appears. Here is the logic i would like to follow:


If txtRoleName.Value = "Student" Then
txtRoleName.FontColor = vbRed
txtRoleName.ForeColor = vbGreen


ElseIF txtRoleName.Value = "Faculty" Then
txtRoleName.FontColor = vbGreen
txtRoleName.ForeColor = vbRed

 

[Jeff Boyce]

Gaby

Are you actually requiring the user to type in "Student" or "Faculty" or
....? If so, how do you handle typos from folks like me with big fat
fingers?

If there's a list of valid choices, it would be easier on your users (and on
you) to provide that list of valid choices in a combobox.

You mention a 'drop down' ... where does that come in?

You mention an If statement ... where does that come in?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Gaby Sandoval]

The drop down i was refering to was the properties/methods list that
appears after i type in "txtRoleName." and then hit Ctrl + Space.

The values coming in to the report are from a form that the user
selects there role, so there should be no typos or errors.

I figured an 'If' statement in the VBA code of the details section of
the report would be where i type in my conditional formatting
statement. I have more than 3 conditions so i cannot use the one in
the Format menu.
I was thinking my If statement would look like this, but that is why i
need help.
If txtRoleName.Value = "Student" Then
txtRoleName.FontColor = vbRed
txtRoleName.ForeColor = vbGreen
ElseIF txtRoleName.Value = "Faculty" Then
txtRoleName.FontColor = vbGreen
txtRoleName.ForeColor = vbRed

 

[Keven Denen]

I am trying to apply conditional formatting.  i have to use code
since
i have 10 different conditions.  basically, i want to change the
color
of the font color and backgroud color of a text box (txtRoleName)
based on the value.

I tried writing an If statement on the details part of the form, but
i
can't seem to get it to work.  I cant find a value that works.  I
tried using *ctrl *space after i type in my texbox anme, but cant
find
anything that seems like it would work from the drop down that
appears.  Here is the logic i would like to follow:

If txtRoleName.Value = "Student" Then
txtRoleName.FontColor = vbRed
txtRoleName.ForeColor = vbGreen

ElseIF txtRoleName.Value = "Faculty" Then
txtRoleName.FontColor = vbGreen
txtRoleName.ForeColor = vbRed

You want to be changing the forecolor and backcolor properties, not
fontcolor. Forecolor is your font color, back color is the background
color.

Your if statements should look something like:

If Me.txtRoleName = "Student" Then
Me.txtRoleName.ForeColor = vbRed
Me.txtRoleName.BackColor = vbGreen
ElseIf Me.txtRoleName = "Teacher" Then
Me.txtRoleName.ForeColor = vbBlue
Me.txtRoleName.BackColor = vbYellow
Else
Me.txtRoleName.ForeColor = vbBlack
Me.txtRoleName.BackColor = vbWhite
End If

Put this on the form_current event and the after update event of the
text box.

Keven

 

[Gaby Sandoval]

You want to be changing the forecolor and backcolor properties, not
fontcolor. Forecolor is your font color, back color is the background
color.

Your if statements should look something like:

    If Me.txtRoleName = "Student" Then
        Me.txtRoleName.ForeColor = vbRed
        Me.txtRoleName.BackColor = vbGreen
    ElseIf Me.txtRoleName = "Teacher" Then
        Me.txtRoleName.ForeColor = vbBlue
        Me.txtRoleName.BackColor = vbYellow
    Else
        Me.txtRoleName.ForeColor = vbBlack
        Me.txtRoleName.BackColor = vbWhite
    End If

Put this on the form_current event and the after update event of the
text box.

Keven- Hide quoted text -

- Show quoted text -

Thanks for the reply. I am trying to format the text box in a Report,
not a form. There is no afterUpdate event. I am not sure what event
to use on the VBA code on the Report.
I tried using code similar to what you posted but
Me.txtRoleName.ForeColor is not valid for some reason.

 

[Keven Denen]

Thanks for the reply.  I am trying to format the text box in a Report,
not a form.   There is no afterUpdate event.  I am not sure what event
to use on the VBA code on the Report.
I tried using code similar to what you posted but
Me.txtRoleName.ForeColor is not valid for some reason.- Hide quoted text -

- Show quoted text -

You may have wanted to specify that in your first message. Regardless,
you can use the same If statement on the Format event of the Details
section.

Keven

 

[Keven Denen]

You may have wanted to specify that in your first message. Regardless,
you can use the same If statement on the Format event of the Details
section.

Keven- Hide quoted text -

- Show quoted text -

One additional note, you'll need to make sure the BackStyle property
is set to Normal, not transparent if you want the background color to
show. You can do this programatically by adding: BackStyle = 1 to the
if statement. making the whole statement:

If Me.txtRoleName = "Student" Then
Me.txtRoleName.ForeColor = vbRed
Me.txtRoleName.BackColor = vbGreen
Me.txtRoleName.BackStyle = 1
ElseIf Me.txtRoleName = "Teacher" Then
Me.txtRoleName.ForeColor = vbBlue
Me.txtRoleName.BackColor = vbYellow
Me.txtRoleName.BackStyle = 1
Else
Me.txtRoleName.ForeColor = vbBlack
Me.txtRoleName.BackColor = vbWhite
Me.txtRoleName.BackStyle = 0
End If

 

[Jeff Boyce]

You mention a textbox and values therein (i.e. "Student", "Faculty"...). Is
that textbox control in your form or in your report?

If, as you describe, the user "selects" a role on a form, that sounds like a
combobox, not a textbox. What is the source of the data displayed in that
combobox? If it is a list of roles, is there a table somewhere with roles?
Does that table have a primary key? Is the key returned in the combobox's
query?

It could be that the reason the report isn't "seeing" "Student" or "Faculty"
is because the combobox is actually set to the primary key value, not the
displayed-on-form value.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.