VBA macro no longer works after Excel 2003->2007 upgrade

[SterlingP]

Our company just upgraded from Excel 2003 to Excel 2007, and now one of my macros no longer works. It is a simple macro to change text in some text boxes to red if the value in a specified cell is a negative number. There are no error messages or anything, it just doesn't work. Any ideas?

Sub RedIfNegative()

Dim fnt As Font

Set fnt = Worksheets("Results").Shapes("Text Box 1").TextFrame.Characters.Font
If (Worksheets("Results").Range("F5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 2").TextFrame.Characters.Font
If (Worksheets("Results").Range("L5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 3").TextFrame.Characters.Font
If (Worksheets("Results").Range("O5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 4").TextFrame.Characters.Font
If (Worksheets("Results").Range("I5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 5").TextFrame.Characters.Font
If (Worksheets("Results").Range("R5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 6").TextFrame.Characters.Font
If (Worksheets("Results").Range("U5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Nothing

End Sub

 

[tusharm]

A more fundamental question is why use code at all?

You can link the contents of a text box to a cell. Select the text border (not the contents) then click in the formula bar and type = Then click in a cell that you want linked to the text box.

In that cell enter the formula =IF(F5<0,"{My message}","")

Now, if F5 < 0 the linked cell will contain your message and so will the text box.

Of course, permanently set the font of the text box to red.

Voila! Same effect. No code.

Of course, if you can do with just the cell and don't need any property specific to a text box, you can even do away with the text boxes. {grin}

Our company just upgraded from Excel 2003 to Excel 2007, and now one of my macros no longer works. It is a simple macro to change text in some text boxes to red if the value in a specified cell is a negative number. There are no error messages or anything, it just doesn't work. Any ideas?

Sub RedIfNegative()

Dim fnt As Font

Set fnt = Worksheets("Results").Shapes("Text Box 1").TextFrame.Characters.Font
If (Worksheets("Results").Range("F5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 2").TextFrame.Characters.Font
If (Worksheets("Results").Range("L5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 3").TextFrame.Characters.Font
If (Worksheets("Results").Range("O5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 4").TextFrame.Characters.Font
If (Worksheets("Results").Range("I5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 5").TextFrame.Characters.Font
If (Worksheets("Results").Range("R5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Worksheets("Results").Shapes("Text Box 6").TextFrame.Characters.Font
If (Worksheets("Results").Range("U5").Value < 0) Then
  fnt.Color = RGB(255, 0, 0)
Else
  fnt.Color = RGB(255, 255, 255)
End If

Set fnt = Nothing

End Sub

 

[SterlingP]

A more fundamental question is why use code at all?

You can link the contents of a text box to a cell. Select the text border (not the contents) then click in the formula bar and type = Then click in a cell that you want linked to the text box.

In that cell enter the formula =IF(F5<0,"{My message}","")

Now, if F5 < 0 the linked cell will contain your message and so will the text box.

Of course, permanently set the font of the text box to red.

Voila! Same effect. No code.

Of course, if you can do with just the cell and don't need any property specific to a text box, you can even do away with the text boxes. {grin}

I think I get what you are saying, but I don't just want the value to display in the text box if it's negative - I want it to display in the text box regardless of whether positive or negative, but for the text to be in red if the value is negative. Kind of like a conditional format for a text box. I've tried giving the cells with the values that display in the text boxes conditional formats and then linking the text boxes to those cells, and they pick up the values and number formats just fine, but not the conditional format that changes the color.

Also, I tried as you suggested earlier and both CreateBasketTrees and RedIfNegative are definitely executing. It is the strangest thing - I can run the RedIfNegative manually and it works perfectly, but it never works if run from inside CreateBasketTrees, even if I take out the call to RedIfNegative and replace it with the exact code from the RedIfNegative macro.