Excel 2007 problem with macros

[ugoveri]

All my excel 2003 files that contain macros (user defined or subroutine) get
erros if converted to excel 2007. I think this is a bug. Anybody can help?

[RB Smissaert]

Show a macro and show the error.

RBS

"ugoveri" <[email protected]> wrote in message
news:[email protected]...
> All my excel 2003 files that contain macros (user defined or subroutine)
> get
> erros if converted to excel 2007. I think this is a bug. Anybody can help?

[ugoveri]

The error is:

This workbook is corrupted. This may be the result of program failure, or it
may have been intentionally corrupted by a malicious source. the Open and
Repair feature may be able to recover the contents of this document. Do not
use Open and Repair on this file if the file is not from a trusted source. Do
you want to use Open and Repair on this file?

If I choose Yes it starts repairing and once starting to calculate it get's
stuck...

The macros well... Here they go:

Sub Apaga()
Application.GoTo Reference:="Insere"
Selection.ClearContents
Selection.ClearComments
Range("I10").Activate
End Sub
Sub DaNome()
a = Range("g2")
For i = 2 To Sheets.Count
If Sheets(i).Name = a Then
Range("i10").Select
End
End If
Next i
ActiveSheet.Name = a
Range("i10").Activate
End Sub
Sub Protege()
For i = 1 To Sheets.Count
Sheets(i).Activate
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True
ActiveSheet.EnableSelection = xlNoRestrictions
Next i
i = 1
Sheets(i).Activate
Range("i10").Activate
End Sub
Sub Desprotege()
For i = 1 To Sheets.Count
Sheets(i).Unprotect
Next i
i = 1
Sheets(i).Select
Range("i10").Select
End Sub

Sub NovaFolha()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim array_aux() As Variant
ActiveSheet.Activate
a = ActiveSheet.Name
B = Range("f4")
Sheets(a).Copy After:=Sheets(a)
C = ActiveSheet.Name
Sheets(C).Activate
On Error GoTo Fim
Range("F3") = Range("I4")
Sheets(C).Name = B
C = ActiveSheet.Name
GoTo Fim
Fim:
Apaga
array_aux = Sheets(a).Range("AM11:AM34").Value
Sheets(C).Range("AM11:AM34").Value = array_aux
array_aux = Sheets(a).Range("AN10:AN34").Value
Sheets(C).Range("AO10:AO34").Value = array_aux
array_aux = Sheets(a).Range("AP10:AP34").Value
Sheets(C).Range("AP10:AP34").Value = array_aux
array_aux = Sheets(a).Range("AQ10:AQ34").Value
Sheets(C).Range("AR10:AR34").Value = array_aux
Sheets(a).Activate
Range("I10").Activate
Sheets(C).Activate
Range("I10").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Function num_mov(in_out_cont As String, asterix As String, descricao As
String) As Variant
Dim valor As String
If in_out_cont = "in" Then
If LCase(descricao) Like "*poupança*" Or LCase(descricao) Like
"*ordem*" Then
If asterix = "*" Then valor = "2a" Else valor = "2"
Else
If asterix = "*" Then valor = "1a" Else valor = 1
End If
ElseIf in_out_cont = "out" Then
If LCase(descricao) Like "*poupança*" Or LCase(descricao) Like
"*ordem*" Then
If asterix = "*" Then valor = "2a" Else valor = "2"
ElseIf LCase(descricao) Like "*formação*" Or LCase(descricao) Like
"*livros*" Or _
LCase(descricao) Like "*mestrado*" Then
If asterix = "*" Then valor = "3a" Else valor = "3"
Else
If asterix = "*" Then valor = "1a" Else valor = "1"
End If
ElseIf in_out_cont = "cont" Then
If LCase(descricao) Like "*poupança*" Then
valor = "1"
ElseIf LCase(descricao) Like "*ordem*" Then
valor = "2"
Else
valor = "3"
End If
End If
num_mov = valor
End Function

And I also have a combobox in each sheet:

Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
a = ActiveSheet.Name
B = Range("f2")
On Error GoTo Fim
Sheets(a).Name = B
GoTo Fim
Fim:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Anyway, if I do not convert the file to 2007 format it works just fine... I
have not tried recording any macro in 2007 and reopening it, but with the
ones already existing I always get this same error... I thought it was only
with udf, but as shown with the macros listed before, with subroutines it
happened also...

Hope this information can help...

Thanks in advance,

[ugoveri]

I was somewhat a little wrong...

Opening Excel 2003 version in 2007 opens fine, but if I try to apply the
macro, apparently I get errors...

I had a line object with a macro assigned that copied a sheet to another
copying simultaneously some values from the previous sheet to the new one...
It also checks for existing names, and if the new name exist it stops,
otherwise it renames with then new sequential name...

I also had the combo box that had a macro attached that renamed the sheet
manually... Basically this to make my accountant monthly work...

When copying the data to the new sheet it copies the sheet to a new one...
It doesn't copy the combo box, and the line objects are copied out of it's
previous place or not copied at all...

After this if click the macro to copy again to a new sheet it shows the
following error:

Run-time error '-2147417848 (80010108)':

Automation error. The object invoked has disconnected from its clients.

If I click debug it points me:

Sub NovaFolha()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim array_aux() As Variant
ActiveSheet.Activate
a = ActiveSheet.Name
B = Range("f4")
=> Sheets(a).Copy After:=Sheets(a) --- This is the line selected by
debug...