Report Format Event

[Alex Motilal]

Dear Experts,

The following code on the Report's Format Event shows Runtime Error 438:

The Report is based on a Cross Tab Query which has to show different back colors for individual rows. I got this code from a You Tube tutorials.

Option Explicit

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control

Select Case True


Case Me.RowCounter Mod 2 = 0 'white

For Each ctl In Me.Detail.Controls
If ctl.Tag = "orange" Then
ctl.BackColor = RGB(255, 255, 255)
ElseIf ctl.Tag = "blue" Then
ctl.BackColor = RGB(245, 250, 250)
End If

Next ctl
Case Me.RowCounter Mod 4 = 1 'medium

For Each ctl In Me.Detail.Controls
If ctl.Tag = "orange" Then
ctl.BackColor = RGB(250, 200, 170)
ElseIf ctl.Tag = "blue" Then
ctl.BackColor = RGB(168, 216, 224)
End If

Next ctl
Case Else 'light

For Each ctl In Me.Detail.Controls
If ctl.Tag = "orange" Then
ctl.BackColor = RGB(255, 232, 218)
ElseIf ctl.Tag = "blue" Then
ctl.BackColor = RGB(200, 230, 230)
End If

Next ctl
End Select

For Each ctl In Me.Detail.Controls
If ctl.Value >= 10000 Then
ctl.ForeColor = RGB(255, 0, 0)
ctl.FontBold = True
Else
ctl.ForeColor = RGB(0, 0, 0)
ctl.FontBold = False
End If

Next ctl
End Sub

Please check what is wrong in the code.

Regards
Alex

[ranman256]

Your last FOR (outside the SELECT) overrides everything you did before...
"For Each ctl In Me.Detail.Controls"
" If ctl.Value >= 10000 Then


you should also indent

Select Case True
    Case Me.RowCounter Mod 2 = 0    'white
        For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
               ctl.BackColor = RGB(255, 255, 255)
            ElseIf ctl.Tag = "blue" Then
               ctl.BackColor = RGB(245, 250, 250)
            End If
        Next ctl
        
    Case Me.RowCounter Mod 4 = 1      'medium
        For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
                ctl.BackColor = RGB(250, 200, 170)
            ElseIf ctl.Tag = "blue" Then
                ctl.BackColor = RGB(168, 216, 224)
            End If
        Next ctl
    
    Case Else    'light
        For Each ctl In Me.Detail.Controls
            If ctl.Tag = "orange" Then
                ctl.BackColor = RGB(255, 232, 218)
            ElseIf ctl.Tag = "blue" Then
                ctl.BackColor = RGB(200, 230, 230)
            End If
        Next ctl
End Select
    
For Each ctl In Me.Detail.Controls
        If ctl.Value >= 10000 Then
            ctl.ForeColor = RGB(255, 0, 0)
            ctl.FontBold = True
        Else
            ctl.ForeColor = RGB(0, 0, 0)
            ctl.FontBold = False
        End If
Next ctl

[Alex Motilal]

Thank you ranman256,
Can you please re frame the code for me?
Alex

[June7]

Did you consider textbox Conditional Formatting instead of VBA code? Could probably use an UNBOUND textbox with Conditional Formatting behind other controls to allow for 'row' coloring. Set labels and other textboxes as transparent.

Which line throws the error? Step debug. Refer to link at bottom of my post for debugging guidelines.

Possibly the issue is the code looks at EVERY control - labels, textboxes, lines, subreport containers, etc. Some of these controls do not have Value property. So you need more conditional code in that last For structure that restricts to only those controls that have Value property. On a report, these would usually be just textboxes - no comboboxes or listboxes or checkboxes, etc.

Also, on second thought, some controls don't have backcolor - such as line.

Why are some controls tagged for orange and some for blue - what was the determining factor?

[Alex Motilal]

Thanks for the response.
I solved the problem by removing the last bloc and made conditional formatting.
Alex