# Excel 2007 Macro causing VB Error 400



## chappers (Jan 16, 2009)

I have been using the folloing macro to clear conditional formatting in 2003, just been upgraded to 2007 and when I run the same macro I get a VB error 400 messge pop up, any ideas:

Function ActiveCondition(rng As Range) As Integer
'Chip Pearson http://www.cpearson.com/excel/CFColors.htm
Dim ndx As Long
Dim FC As FormatCondition
Dim p As String
Dim v1, v2 As Double

If rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For ndx = 1 To rng.FormatConditions.Count
Set FC = rng.FormatConditions(ndx)
Select Case FC.Type
Case xlCellValue
p = FC.Formula1
If (Left(p, 1) = "=") Then
v1 = Application.Evaluate(FC.Formula1)
Else
v1 = CDbl(p)
End If
Select Case FC.Operator
Case xlBetween
p = FC.Formula2
If (Left(p, 1) = "=") Then
v2 = Application.Evaluate(FC.Formula2)
Else
v2 = CDbl(p)
End If
If CDbl(rng.Value) >= CDbl(v1) And _
CDbl(rng.Value) <= CDbl(v2) Then
ActiveCondition = ndx
Exit Function
End If
Case xlGreater
If CDbl(rng.Value) > CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlEqual
If CDbl(rng.Value) = CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlGreaterEqual
If CDbl(rng.Value) >= CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlLess
If CDbl(rng.Value) < CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlLessEqual
If CDbl(rng.Value) <= CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlNotEqual
If CDbl(rng.Value) <> CDbl(v1) Then
ActiveCondition = ndx
Exit Function
End If
Case xlNotBetween
p = FC.Formula2
If (Left(p, 1) = "=") Then
v2 = Application.Evaluate(FC.Formula2)
Else
v2 = CDbl(p)
End If
If CDbl(rng.Value) <= CDbl(v1) Or _
CDbl(rng.Value) >= CDbl(v2) Then
ActiveCondition = ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN OPERATOR"
End Select
Case xlExpression
If Application.Evaluate(FC.Formula1) Then
ActiveCondition = ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN TYPE"
End Select
Next ndx
End If
ActiveCondition = 0
End Function


----------



## GordonReilly (Sep 17, 2009)

Hi, did you ever get a reply to your post?

I have a staff expense claim spreadsheet that works on most computers, but for some reason won't work on a few. This has all started since the company "upgraded" to 2007.

I have run out of ideas as to why the problem is so inconsistent.


----------



## mitasol (Jan 17, 2009)

from VBA help

"Form already displayed; can't show modally (Error 400)

You can't use the Show method to display a visible form as modal. This error has the following cause and solution:

· You tried to use Show, with the style argument set to 1 – vbModal, on an already visible form.

Use either the Unload statement or the Hide method on the form before trying to show it as a modal form. "


----------

