Excel 2007 Issue

[TheUnderTow]

Hi, I have the following code in an Excel 2003 workbook. It simply checks if one cell is populated and clears out another and vice versa. When I send the workbook to someone with Excel 2007 it does not work and produces an error message - anybody got any ideas why?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrCode

' if Trust combo is selected clear Other etc
If Not IsEmpty(ActiveCell.Value) And ActiveCell.Column = 3 Then
If ActiveCell.Row = 2 Then
Cells(3, 3) = ""
ElseIf ActiveCell.Row = 3 Then
Cells(2, 3) = ""
End If
End If

ExitCode:
Exit Sub
ErrCode:
MsgBox "Error No." & Err.Number & vbCrLf & Err.Description
Resume ExitCode
End Sub

Cheers

[ACtheTROLL]

rather than pasting in the excel help

easiest explanation is to open excel 2007
fire up help and search for 2003 macro

there's tons of information ...

[Bushwhacker]

The code looks fine, and if it works OK in 2003 then the problem must be to do with the conversion from 2003 to 2007.

Could you not just send the recipient the code separately, get them to open up the workbook, delete out the existing code then copy and paste the separate code into the VB module in 2007 and then try it again.

Just an idea.

[ACtheTROLL]

I doubt that there is anything wrong with the code ... it's the way excel handles macros ... in 2003 its levels of trust
in 2007 its the spreadsheet file type

in both versions it's a pig ....
the help for 2007 gives good detail

http://office.microsoft.com/en-us/excel/HP1009 69191033.aspx

[TheUnderTow]

I have also researched this on msdn forums and what seems to be the issue is that I need to declare the ActiveSheet object when referencing cells. Previous versions have assumed that the sheet you are working on is the sheet you wish to perform the action on unless otherwise stated - 2007 does not do this.

Something quite straightforward then but a pain in the a$$ if you've got lots of macros that now need changing!!

[ACtheTROLL]

try saving the 2007 sheet as an XLSM type (the "M" isfor macro!)

I should have said that earlier