Excel 2007 - Macro Won't Run

[mrsnrub]

Hi everyone,
We have a user who has recently upgraded to Office 2007.

He has a spreadsheet which contains macros. When the file is opened as XLS or XLSM the following message appears:

"Cannot run the macro 'Filename.xls!MacroName'. The macro may not be available in this workbook or all macros may be disabled".

The macro is within the file and the trust center macro settings are set to 'Always Enable'.

I'm running Excel 2007 myself, and I'm not experiencing any problems with this particular file.

I have also created a test file with a simple macro, and this runs fine on his installation.

I'm running out of ideas, any suggestions would be greatly appreciated.

Cheers.

 

[MakeItSo]

The macro code would help.
Is it a legacy code from Excel 2003?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[GlennUK]

Does this help:

http://www.rondebruin.nl/password2007.htm

?



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[mrsnrub]

Thanks for the replies.

Glenn, there's no protection or passwords set on the spreadsheet so unfortunately this isn't the answer.

What confuses me is I can't replicate it on my machine.

It is legacy code from 2003. The code looks to have been created straight from a recording and is very messy and long...

If it helps, here are some samples:

Sheets("Modified").Select
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 12.29
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Piv Wk #"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R2C27:R53C28,2)"
    Selection.AutoFill Destination:=Range("N2:N2980"), Type:=xlFillDefault
    Range("N2:N2980").Select
    ActiveWindow.ScrollRow = 1
    Range("N2").Select

Range("N2:N2980").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1
    Range("N2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("M1").Select
    Application.CutCopyMode = False

Sheets("Modified").Select
    Range("N3000").Select
    ActiveCell.FormulaR1C1 = "B"
    Range("N2999").Select
    ActiveCell.FormulaR1C1 = "13"
    Range("N2998").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("N2997").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("N2996").Select
    ActiveCell.FormulaR1C1 = "10"
    Range("N2995").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("N2994").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("N2993").Select
    ActiveCell.FormulaR1C1 = "7"

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "Modified!R1C1:R3096C19", TableDestination:="", TableName:="PivotTable1"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=" Group", _
        ColumnFields:="Piv Wk #"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Hrs")
        .Orientation = xlDataField
        .Name = "Sum of Hrs"
        .Function = xlSum
    End With
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "PivGroup"
    Range("G27").Select
    Sheets("Volume Forecast").Select
    Range("D2").Select

And it continues on in this fashion for many more lines. Anything in particular you're suggesting to look for in the code?

A rewrite looks like a great idea.

 

[MakeItSo]

A rewrite looks like a great idea

Definitely!!

If you let us in on what you're trying to achieve, I bet together we can short your code to about one tenth of its current length.
I would assume 75% if not all of your "Select", "Copy" etc. are completely superfluous. They make your code overly long and especially overly slow.

Thus far, the only line I can see possible causing trouble is this:

Application.CutCopyMode = False

I know there have been changes to the Application object in Office 2007, so my money is on that line.
Anyway, like I said: The vast majority of code lines look superfluous to me.
Time for a cleanup.


Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[mrsnrub]

Thanks MiS... please trust me when I say that isn't my code

From what I can gather the code just does some basic reformatting and summarising for a periodic report. It was the first I had seen of it when it got lumped on my desk a couple of days ago.

I wasn't aware of the changes to the Application object in 07, I'll review these changes and check for any other problem areas.

I can't get access to this guys PC for a few days, but I'll be sure to post back with the resolution.

Thanks again.
Steve