Error in Excel 2003, OK in Excel 97

[Jason]

The majority of machines in our company are still running Win2k Pro and
Office 97. However, they are now starting to phase in some new machines
equipped with WinXP Pro and Office 2003. Below is an excerpt of my code that
is encountering an error on the new machines, but not on the old:

Sub Update()
'
' Copies the data from the Production sheet, and is used in conjunction
' with one of the two macros below (AudProd or FieldAuditTrackingUpdate).
'
Application.ScreenUpdating = False
Application.Run "ProdLog"
Application.ScreenUpdating = False
Sheets("Production").Range("A1").Select
Sheets("Production").Copy Before:=Sheets("Reports")
Sheets("Production (2)").Select
Sheets("Production (2)").Unprotect Password:="xxxxxx"
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("CommandButton2").Delete
ActiveSheet.Shapes(2).Select
With Selection
.Font.ColorIndex = 6
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.Solid
.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 204)
.OnAction = "MainMenu"
End With
Range("F4").Select
Range("A1:B1").MergeCells = False
Range("D1:E1").MergeCells = False
Range("A1").ClearContents
Range("D1").ClearContents
Range("A1:BD300").Select
Selection.Copy
With Selection
.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
.Locked = True
.FormulaHidden = True
End With
Range("A4:BD300").Select
With Selection
.Font.FontStyle = "Regular"
.Font.ColorIndex = xlAutomatic
.Interior.ColorIndex = xlNone
End With
Application.CutCopyMode = False
Range("A1:BD3").Font.ColorIndex = xlAutomatic
End Sub

Does anyone see anything above that would result in this error for WinXP Pro
and Excel 2003?

The error is: "The item with the specified name wasn't found."

The sheet has 4 autoshapes (2 command buttons and 2 other shapes).

I would really appreciate any insight.

Oh, and please don't knock the code too badly, I am a beginner at this and
self-taught.

 

[JLGWhiz]

Hi Jason, I had some old code that I wrote before I retired and when I got my
Office 2003 software it would not run until I copied it into a code module in
Excel 2003. I don't know if that will cure your problem but it would be
helpful if you could pinpoint where you are getting the error. In other
words, what is it that the program cannot find? If we know that, it should
be easy to figure out why. You can step through you macro manually and see
at what step the error message is generated.

JLG

 

[Jason]

Thanks for the reply, JLG.

Actually, that is part of the problem. VBA is not pointing me to exactly
where the problem lies. I only get that error, and it does not give me the
option to debug (even when I have the code unprotected).

However, if I look at the spreadsheet, I can sort of tell where it stopped,
although it is somewhat confusing.

The sheet is copied and unprotected, but then where it gets confusing is
that CommandButton2 is deleted, but CommandButton1 is not. So my guess is
that the error relates to CommandButton1. However, why would it delete
CommandButton2 first, when it is after CommantButton1 delete command in my
code?

Thanks again,

Jason

 

[Tom Ogilvy]

I get that error if there is no shape with the name CommandButton1 (or no
shape with the name CommandButton2

if there are only two command buttons then

Dim obj as OleObject
for each obj in Worksheets("Production (2)").OleObjects
if typeof Obj.Object is MSForms.Commandbutton then
obj.Delete
end if
Next

 

[NickHK]

Jason,
Are you running this code from the _Click event of CommandButton1 ?
Are you sure "ActiveSheet.Shapes(2)" still exists ?

NickHK

 

[Peter Huang [MSFT]]

Hi Jason,

In addition, I am not sure the Update can be called from Macro directly.
If yes, I think you may try to follow the steps below to run the code one
by one.
1. Press Alt+F11 to open the VBE editor
2. locate Sub Update
3. Press F9 to set a breakpoint on line " Application.ScreenUpdating =
False"
4. Press F5 to run the macro directly and the breakpoint should be hit
5. Now you can press F8 to go through the code one by one to see what is
the error

Also if you do not have concern, can you provided a simplied reproduce
Excel file in Excel 97 format which working in Excel 97, we can try to run
it in a Windows XP+Excel 2003 environment so that we can troubleshoot the
problem at our side.

If you have any concern, please feel free to let me know.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jason]

Thanks for the input everyone!

Tom's suggestion worked like a charm. Thanks Tom!

Nick, this is run from a _Click event, but from a different sheet, so not
from CommandButton1. And yes, Shapes2 does still exist. I have verified this
after receiving the error.

Peter, thanks so much for stepping me through that, and for your offer.