2003 to 2007 code transfer trouble

[apharrington]

I created a workbook in 2003 and sent it to another to be used in their 2007 version. When she executes the command button in the 2007 version, she gets a runtime error: '-2147024809(80070057)': The item with the specified name wasn't found. Choose 'debug' and the row: ActiveSheet.Shapes("Button 8").Select - is highlighted.
It works fine in 2003 - not in 2007. I resent the workbook and the exact same error came up.

Any ideas on how to have this transfer from 2003 to 2007 properly?

Thanks so much.

[Bob Phillips]

I have just created a simple workbook with a button and activeated that in the Workbook_Open routine, then opened the workbook in Excel 2007, and it worked fine.

There must be something else going on. Can you post the workbook?

[shamsam1]

do paste that worksheet..to test

[apharrington]

Attachment 9945

have at it. Sheet "423a 121" is the one that comes up with the run time error in 2007 only; not 2003.

Anxiously awaiting your reply...

[Paul_Hossler]

Go to the Developer tab, go into Design Mode, right click the button, and the name will show in the Name Box to the left of the formula bar

Comes back with "Button 1"

Changing the offending line to

[vba]
ActiveSheet.Shapes("Button 1").OnAction = "Clear423a121"
[/vba]

works (no need to Select everything first)


Paul

[Carl A]

When you run your clear sheet routine for some reason excel is changing your Button 8 to Button 1 this happens on the first two sheets?

[apharrington]

I am working in 2003. When I change the button to "Button 1" as you have suggested below, it doesn't work. When it's changed to "Button 8" it does...

Are you in version 2003 or 2007? I don't have 2007 to test from so I don't know if this executes properly in that or other versions.

[Carl A]

Excel 2007 changes the names of your copied sheet buttons. 423a 04 Blank which has Button11 becomes Button 1. Sheet 423a 221 Blank which has Button 8 also changes to Button 1.

Using the name dialog box select the controls and change the names of the buttons on the visible sheets to Button 1 and on the hidden sheets that are copied to Button 1 then make the changes in the code from Button 8 to Button 1.

[Paul_Hossler]

Well another way would be to loop through the shapes and look for the text.

That would give you the button you want, and then you could set things, etc.

The function example below is not very general purpose, but could easily be expanded

Change your one line to use the results of a function, and then define the funtion to return a Shape

Paul

[vba]

..........

If Not TheButton Is Nothing Then TheButton.OnAction = "Clear423a121"



Function TheButton() As Shape
Dim shpButton As Shape
For Each shpButton In ActiveSheet.Shapes
If Left(shpButton.AlternativeText, 5) = "CLEAR" Then
Set TheButton = shpButton
Exit Function
End If
Next
Set TheButton = Nothing
End Function
[/vba]