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

 

[rconverse]

Are your 2007 macros enabled? I know that Excel files in 2007 with macros have to be saved as xlsm files.

 

[SterlingP]

I forgot to mention - the macro is being called as a function from inside another sub. It runs fine if I set it up as a sub itself and run it as a separate macro, but when changed to a function and called from another sub using "Call RedIfNegative", it does not work.

 

[taigovinda]

What do you mean, when changed to a function? You can call it from another sub, but your code will never be a function. Make sure that the heading says sub, not function - and try putting that and the calling sub into the same module.

 

[SterlingP]

What do you mean, when changed to a function? You can call it from another sub, but your code will never be a function. Make sure that the heading says sub, not function - and try putting that and the calling sub into the same module.

Sorry, I think I was unclear.

The original code I had was a function, i.e. Function RedIFNegative() <code> End Function. It was called from a Sub called CreateBasketTrees. Withough putting all the other code in, it looked like this:

Sub CreateBasketTrees()
   Call RedIfNegative
End Sub

Function RedIfNegative()
  <code>
End Function()

Both CreateBasketTrees and RedIfNegative are in the same module.

Upon learning that RedIfNegative was no longer working after the upgrade, I changed the Function RedIfNegative to a Sub to see if it worked correctly when I ran in independently, which it did. It does not work if called as a function as shown above, or even when putting the RedIfNegative code inline in the CreateBasketTrees sub.

 

[taigovinda]

What I was initially suggesting is that you make the call the same way you indicated in post # 5, but that you change "Function RedIfNegative" to "Sub RedIfNegative."

I must be missing the point here, though... what is RedIfNegative supposed to do?

It looks like it declares a variable, sets it and resets it a few times, then sets it to nothing - but never passes anything back out.

 

[tusharm]

How is CreateBasketTrees executed?

Sorry, I think I was unclear.

The original code I had was a function, i.e. Function RedIFNegative() <code> End Function. It was called from a Sub called CreateBasketTrees. Withough putting all the other code in, it looked like this:

Sub CreateBasketTrees()
   Call RedIfNegative
End Sub

Function RedIfNegative()
  <code>
End Function()

Both CreateBasketTrees and RedIfNegative are in the same module.

Upon learning that RedIfNegative was no longer working after the upgrade, I changed the Function RedIfNegative to a Sub to see if it worked correctly when I ran in independently, which it did. It does not work if called as a function as shown above, or even when putting the RedIfNegative code inline in the CreateBasketTrees sub.

 

[SterlingP]

How is CreateBasketTrees executed?

I just execute it manually from the Macros menu.

 

[SterlingP]

What I was initially suggesting is that you make the call the same way you indicated in post # 5, but that you change "Function RedIfNegative" to "Sub RedIfNegative."

I must be missing the point here, though... what is RedIfNegative supposed to do?

It looks like it declares a variable, sets it and resets it a few times, then sets it to nothing - but never passes anything back out.

You are right, there is no particular reason for it to be a function since it does not return a a value. I can't recall why I originally coded it that way, but the behavior is the same when RedIfNegative is a sub - when called from the CreateBasketTrees sub, it doesn't work, when executed manually from the Macro menu, it does.

 

[tusharm]

This should not matter but what happens if you replace

   Call RedIfNegative

with

   RedIfNegative

Also, put a

Debug.Print "In CreateBasketTrees"

in CreateBasketTrees and make sure it is actually executed. Put a similar statement in RedIfNegative and check if it is even called.

I just execute it manually from the Macros menu.