By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Formatting multiple controls on report based on value of othercontrols

P: n/a
Hello All and thanks in advance for your help.

The code below works just fine in the Print event of the report Detail
section. Is it possible to condense it using a loop or something?
Can you use variables when setting attributes?

Thanks again. I learn a lot from reading answers to other people's
posts, but haven't seen anything that simplifies this issue.

Tricia
If [01Units] < [01UnitsBdgt] Then
[01Units].ForeColor = vbRed
Else
[01Units].ForeColor = vbBlack
End If

If [02Units] < [02UnitsBdgt] Then
[02Units].ForeColor = vbRed
Else
[02Units].ForeColor = vbBlack
End If

If [03Units] < [03UnitsBdgt] Then
[03Units].ForeColor = vbRed
Else
[03Units].ForeColor = vbBlack
End If

If [04Units] < [04UnitsBdgt] Then
[04Units].ForeColor = vbRed
Else
[04Units].ForeColor = vbBlack
End If

If [05Units] < [05UnitsBdgt] Then
[05Units].ForeColor = vbRed
Else
[05Units].ForeColor = vbBlack
End If

If [06Units] < [06UnitsBdgt] Then
[06Units].ForeColor = vbRed
Else
[06Units].ForeColor = vbBlack
End If

If [07Units] < [07UnitsBdgt] Then
[07Units].ForeColor = vbRed
Else
[07Units].ForeColor = vbBlack
End If

If [08Units] < [08UnitsBdgt] Then
[08Units].ForeColor = vbRed
Else
[08Units].ForeColor = vbBlack
End If

If [09Units] < [09UnitsBdgt] Then
[09Units].ForeColor = vbRed
Else
[09Units].ForeColor = vbBlack
End If

If [10Units] < [10UnitsBdgt] Then
[10Units].ForeColor = vbRed
Else
[10Units].ForeColor = vbBlack
End If

If [11Units] < [11UnitsBdgt] Then
[11Units].ForeColor = vbRed
Else
[11Units].ForeColor = vbBlack
End If

If [12Units] < [12UnitsBdgt] Then
[12Units].ForeColor = vbRed
Else
[12Units].ForeColor = vbBlack
End If
Sep 30 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
the only other thing i can think of is to use conditional formatting instead
of code.

if you're using A2000 or newer, try setting each control's ForeColore to the
Else value, in form Design view. then set Conditional Formatting (Format
menu) for each control, choosing Expression Is from the droplist in the
dialog box.

hth
"Trish" <ph*******@mdanderson.orgwrote in message
news:99**********************************@34g2000h sh.googlegroups.com...
Hello All and thanks in advance for your help.

The code below works just fine in the Print event of the report Detail
section. Is it possible to condense it using a loop or something?
Can you use variables when setting attributes?

Thanks again. I learn a lot from reading answers to other people's
posts, but haven't seen anything that simplifies this issue.

Tricia
If [01Units] < [01UnitsBdgt] Then
[01Units].ForeColor = vbRed
Else
[01Units].ForeColor = vbBlack
End If

If [02Units] < [02UnitsBdgt] Then
[02Units].ForeColor = vbRed
Else
[02Units].ForeColor = vbBlack
End If

If [03Units] < [03UnitsBdgt] Then
[03Units].ForeColor = vbRed
Else
[03Units].ForeColor = vbBlack
End If

If [04Units] < [04UnitsBdgt] Then
[04Units].ForeColor = vbRed
Else
[04Units].ForeColor = vbBlack
End If

If [05Units] < [05UnitsBdgt] Then
[05Units].ForeColor = vbRed
Else
[05Units].ForeColor = vbBlack
End If

If [06Units] < [06UnitsBdgt] Then
[06Units].ForeColor = vbRed
Else
[06Units].ForeColor = vbBlack
End If

If [07Units] < [07UnitsBdgt] Then
[07Units].ForeColor = vbRed
Else
[07Units].ForeColor = vbBlack
End If

If [08Units] < [08UnitsBdgt] Then
[08Units].ForeColor = vbRed
Else
[08Units].ForeColor = vbBlack
End If

If [09Units] < [09UnitsBdgt] Then
[09Units].ForeColor = vbRed
Else
[09Units].ForeColor = vbBlack
End If

If [10Units] < [10UnitsBdgt] Then
[10Units].ForeColor = vbRed
Else
[10Units].ForeColor = vbBlack
End If

If [11Units] < [11UnitsBdgt] Then
[11Units].ForeColor = vbRed
Else
[11Units].ForeColor = vbBlack
End If

If [12Units] < [12UnitsBdgt] Then
[12Units].ForeColor = vbRed
Else
[12Units].ForeColor = vbBlack
End If

Sep 30 '08 #2

P: n/a
well, okay, i'd recall my previous post if i could. try the following code,
instead, as

Dim i As Integer, str1 As String, str2 As String

For i = 1 To 12
If i < 10 Then
str1 = "0" & i & "Units"
str2 = "0" & i & "UnitsBdgt"
Else
str1 = i & "Units"
str2 = i & "UnitsBdgt"
End If

If str1 < str2 Then
Reports(str1).ForeColor = vbRed
Else
Reports(str1).ForeColor = vbBlack
End If
Next

hth
"Trish" <ph*******@mdanderson.orgwrote in message
news:99**********************************@34g2000h sh.googlegroups.com...
Hello All and thanks in advance for your help.

The code below works just fine in the Print event of the report Detail
section. Is it possible to condense it using a loop or something?
Can you use variables when setting attributes?

Thanks again. I learn a lot from reading answers to other people's
posts, but haven't seen anything that simplifies this issue.

Tricia
If [01Units] < [01UnitsBdgt] Then
[01Units].ForeColor = vbRed
Else
[01Units].ForeColor = vbBlack
End If

If [02Units] < [02UnitsBdgt] Then
[02Units].ForeColor = vbRed
Else
[02Units].ForeColor = vbBlack
End If

If [03Units] < [03UnitsBdgt] Then
[03Units].ForeColor = vbRed
Else
[03Units].ForeColor = vbBlack
End If

If [04Units] < [04UnitsBdgt] Then
[04Units].ForeColor = vbRed
Else
[04Units].ForeColor = vbBlack
End If

If [05Units] < [05UnitsBdgt] Then
[05Units].ForeColor = vbRed
Else
[05Units].ForeColor = vbBlack
End If

If [06Units] < [06UnitsBdgt] Then
[06Units].ForeColor = vbRed
Else
[06Units].ForeColor = vbBlack
End If

If [07Units] < [07UnitsBdgt] Then
[07Units].ForeColor = vbRed
Else
[07Units].ForeColor = vbBlack
End If

If [08Units] < [08UnitsBdgt] Then
[08Units].ForeColor = vbRed
Else
[08Units].ForeColor = vbBlack
End If

If [09Units] < [09UnitsBdgt] Then
[09Units].ForeColor = vbRed
Else
[09Units].ForeColor = vbBlack
End If

If [10Units] < [10UnitsBdgt] Then
[10Units].ForeColor = vbRed
Else
[10Units].ForeColor = vbBlack
End If

If [11Units] < [11UnitsBdgt] Then
[11Units].ForeColor = vbRed
Else
[11Units].ForeColor = vbBlack
End If

If [12Units] < [12UnitsBdgt] Then
[12Units].ForeColor = vbRed
Else
[12Units].ForeColor = vbBlack
End If

Sep 30 '08 #3

P: n/a
On Sep 30, 8:59*am, "tina" <nos...@address.comwrote:
well, okay, i'd recall my previous post if i could. try the following code,
instead, as

* * Dim i As Integer, str1 As String, str2 As String

* * For i = 1 To 12
* * * * If i < 10 Then
* * * * * * str1 = "0" & i & "Units"
* * * * * * str2 = "0" & i & "UnitsBdgt"
* * * * Else
* * * * * * str1 = i & "Units"
* * * * * * str2 = i & "UnitsBdgt"
* * * * End If

* * * * If str1 < str2 Then
* * * * * * Reports(str1).ForeColor = vbRed
* * * * Else
* * * * * * Reports(str1).ForeColor = vbBlack
* * * * End If
* * Next

hth

"Trish" <phchap...@mdanderson.orgwrote in message

news:99**********************************@34g2000h sh.googlegroups.com...
Hello All and thanks in advance for your help.
The code below works just fine in the Print event of the report Detail
section. *Is it possible to condense it using a loop or something?
Can you use variables when setting attributes?
Thanks again. *I learn a lot from reading answers to other people's
posts, but haven't seen anything that simplifies this issue.
Tricia
* * If [01Units] < [01UnitsBdgt] Then
* * * * [01Units].ForeColor = vbRed
* * Else
* * * * [01Units].ForeColor = vbBlack
* * End If
* * If [02Units] < [02UnitsBdgt] Then
* * * * [02Units].ForeColor = vbRed
* * Else
* * * * [02Units].ForeColor = vbBlack
* * End If
* * If [03Units] < [03UnitsBdgt] Then
* * * * [03Units].ForeColor = vbRed
* * Else
* * * * [03Units].ForeColor = vbBlack
* * End If
* * If [04Units] < [04UnitsBdgt] Then
* * * * [04Units].ForeColor = vbRed
* * Else
* * * * [04Units].ForeColor = vbBlack
* * End If
* * If [05Units] < [05UnitsBdgt] Then
* * * * [05Units].ForeColor = vbRed
* * Else
* * * * [05Units].ForeColor = vbBlack
* * End If
* * If [06Units] < [06UnitsBdgt] Then
* * * * [06Units].ForeColor = vbRed
* * Else
* * * * [06Units].ForeColor = vbBlack
* * End If
* * If [07Units] < [07UnitsBdgt] Then
* * * * [07Units].ForeColor = vbRed
* * Else
* * * * [07Units].ForeColor = vbBlack
* * End If
* * If [08Units] < [08UnitsBdgt] Then
* * * * [08Units].ForeColor = vbRed
* * Else
* * * * [08Units].ForeColor = vbBlack
* * End If
* * If [09Units] < [09UnitsBdgt] Then
* * * * [09Units].ForeColor = vbRed
* * Else
* * * * [09Units].ForeColor = vbBlack
* * End If
* * If [10Units] < [10UnitsBdgt] Then
* * * * [10Units].ForeColor = vbRed
* * Else
* * * * [10Units].ForeColor = vbBlack
* * End If
* * If [11Units] < [11UnitsBdgt] Then
* * * * [11Units].ForeColor = vbRed
* * Else
* * * * [11Units].ForeColor = vbBlack
* * End If
* * If [12Units] < [12UnitsBdgt] Then
* * * * [12Units].ForeColor = vbRed
* * Else
* * * * [12Units].ForeColor = vbBlack
* * End If- Hide quoted text -

- Show quoted text -
Thanks so much Tina. It only needed a little tweaking, and now works
perfectly. For any who might have the same question, here is the
slightly modified code.

Trish

Dim i As Integer, str1 As String, str2 As String
For i = 1 To 12
If i < 10 Then
str1 = "0" & i & "Units"
str2 = "0" & i & "UnitsBdgt"
Else
str1 = i & "Units"
str2 = i & "UnitsBdgt"
End If
If Report(str1) < Report(str2) Then
Report(str1).ForeColor = vbRed
Else
Report(str1).ForeColor = vbBlack
End If
Next
Sep 30 '08 #4

P: n/a
oops! yes, you fixed it, good job. that's what happens when i post untested
code! ;)
"Trish" <ph*******@mdanderson.orgwrote in message
news:64**********************************@z66g2000 hsc.googlegroups.com...
On Sep 30, 8:59 am, "tina" <nos...@address.comwrote:
well, okay, i'd recall my previous post if i could. try the following
code,
instead, as

Dim i As Integer, str1 As String, str2 As String

For i = 1 To 12
If i < 10 Then
str1 = "0" & i & "Units"
str2 = "0" & i & "UnitsBdgt"
Else
str1 = i & "Units"
str2 = i & "UnitsBdgt"
End If

If str1 < str2 Then
Reports(str1).ForeColor = vbRed
Else
Reports(str1).ForeColor = vbBlack
End If
Next

hth

"Trish" <phchap...@mdanderson.orgwrote in message

news:99**********************************@34g2000h sh.googlegroups.com...
Hello All and thanks in advance for your help.
The code below works just fine in the Print event of the report Detail
section. Is it possible to condense it using a loop or something?
Can you use variables when setting attributes?
Thanks again. I learn a lot from reading answers to other people's
posts, but haven't seen anything that simplifies this issue.
Tricia
If [01Units] < [01UnitsBdgt] Then
[01Units].ForeColor = vbRed
Else
[01Units].ForeColor = vbBlack
End If
If [02Units] < [02UnitsBdgt] Then
[02Units].ForeColor = vbRed
Else
[02Units].ForeColor = vbBlack
End If
If [03Units] < [03UnitsBdgt] Then
[03Units].ForeColor = vbRed
Else
[03Units].ForeColor = vbBlack
End If
If [04Units] < [04UnitsBdgt] Then
[04Units].ForeColor = vbRed
Else
[04Units].ForeColor = vbBlack
End If
If [05Units] < [05UnitsBdgt] Then
[05Units].ForeColor = vbRed
Else
[05Units].ForeColor = vbBlack
End If
If [06Units] < [06UnitsBdgt] Then
[06Units].ForeColor = vbRed
Else
[06Units].ForeColor = vbBlack
End If
If [07Units] < [07UnitsBdgt] Then
[07Units].ForeColor = vbRed
Else
[07Units].ForeColor = vbBlack
End If
If [08Units] < [08UnitsBdgt] Then
[08Units].ForeColor = vbRed
Else
[08Units].ForeColor = vbBlack
End If
If [09Units] < [09UnitsBdgt] Then
[09Units].ForeColor = vbRed
Else
[09Units].ForeColor = vbBlack
End If
If [10Units] < [10UnitsBdgt] Then
[10Units].ForeColor = vbRed
Else
[10Units].ForeColor = vbBlack
End If
If [11Units] < [11UnitsBdgt] Then
[11Units].ForeColor = vbRed
Else
[11Units].ForeColor = vbBlack
End If
If [12Units] < [12UnitsBdgt] Then
[12Units].ForeColor = vbRed
Else
[12Units].ForeColor = vbBlack
End If- Hide quoted text -

- Show quoted text -
Thanks so much Tina. It only needed a little tweaking, and now works
perfectly. For any who might have the same question, here is the
slightly modified code.

Trish

Dim i As Integer, str1 As String, str2 As String
For i = 1 To 12
If i < 10 Then
str1 = "0" & i & "Units"
str2 = "0" & i & "UnitsBdgt"
Else
str1 = i & "Units"
str2 = i & "UnitsBdgt"
End If
If Report(str1) < Report(str2) Then
Report(str1).ForeColor = vbRed
Else
Report(str1).ForeColor = vbBlack
End If
Next

Oct 1 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.