2007 won't run the following vb works in 2003

  • Thread starter Thread starter Walt Moeller
  • Start date Start date
W

Walt Moeller

I'm using the following in 2003 excel but it will not run in 2007!
Any help would be great.


Private Sub Worksheet_Change(ByVal Target As Range)
v = Range("c2").Value

If v > 500 And v < 1001 Then
Range("C3").Value = v
End If

If v > 1000 And v < 25001 Then
Range("C4").Value = v
End If

If v > 25000 And v < 100001 Then
Range("C5").Value = v
End If

If v > 100000 And v < 500001 Then
Range("C6").Value = v
End If

If v > 500000 And v < 500000001 Then
Range("C7").Value = v
End If

End Sub
 
Hi Walt,

1. In 2007 you need to have your file as Macro Enabled - its name must be
XLSM.

2. As written I'm not sure why you macro worked well in 2003 because the
event that triggers the macro causes the code to write a value to a range
which then triggers the macro to run again, since writing to a range is a
Change event. And this will continue... So to prevent that you would a. use
the EnableEvents=False line, or b. you would define the Target range so it
would Not include the range C3:C6. And then you would test the intersection
using something of the form

Set isect = Application.Intersect(Range("C3:C6"), Target)
If isect Is Nothing Then
your code here
....
End if

3. When Excel 2007 opens up it displays a Security Warning bar above the
Formula Bar, you need to click Options and then choose Enable this content.

4. Here is slightly shorter code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
V = Range("c2").Value
Select Case V
Case 500 To 1000
[C3] = V
Case 1001 To 25000
[C4] = V
Case 25001 To 100000
[C5] = V
Case 100001 To 500000
[C6] = V
End Select
Application.EnableEvents = True
End Sub

5. If you want to use IF you should rewrite the code to something more like

Private Sub Worksheet_Change(ByVal Target As Range)
v = Range("c2").Value

If v > 500 And v < 1001 Then
Range("C3").Value = v

ElseIf v > 1000 And v < 25001 Then
Range("C4").Value = v

ElseIf v > 25000 And v < 100001 Then
Range("C5").Value = v

ElseIf v > 100000 And v < 500001 Then
Range("C6").Value = v

ElseIf v > 500000 And v < 500000001 Then
Range("C7").Value = v
End If

End Sub

However, this won't allow the macro to run, its just better code. You still
need to consider my earlier suggestions.
 
Back
Top