Macro execution changes filename reference for all macros

[Fred Alexander]

Long ago (10 or more years) I created a mailing list application, using Excel
97 originally, with updates along the way using Excel 2000 and 2003. The
application has been running with Excel 2007 for 18 months without a problem.
Everything has worked smoothly until about two weeks ago.

The user clicks on any of a number of control button on a worksheet to call
a series of routines. In the troublesome routine a list is sorted and
filtered. The filtered list is copied to a new sheet. The sheet is renamed.
The sheet is then moved to a new book. The problem occurs when the move
command executes.

The code is:
With ActiveSheet
.Name = "Publication"
.Move
End With

Prior to the execution of that bit of code every button shows the assigned
macro as "Mail List!macroname" (where "Mail List is hte file name); but when
the ".move" command executes the macros assigned to all of the buttons change
to "Book1!macroname" (where Book1 is the name of the new file).
Additionally, if you open the list of macros, all macros now point to Book1
and not to the original file name.

Until now, the application has worked like a charm, with the last major
update in 2004.

What can I do to keep this file name switch from happening?

 

[OssieMac]

Hi Fred,

I don't know what might have caused the change. However, the problem is to
get it working now. I have assumed that these buttons are Forms buttons; not
ActiveX Command Buttons. Therefore, save the OnAction for each of the buttons
prior to moving the worksheet and then reset the OnAction in the new
worksheet. The following code should do it.

I have tested this in xl2007 and it appears to work satisfactorily. However,
I will monitor the thread and feel free to get back to me if any problems.

Sub PreserveOnAction()
Dim shp As Shape
Dim arrAction()
Dim i As Long
Dim j As Long

With ActiveSheet

'Redim a 2 dimensional 1 based array
'with sufficient elements for all shapes
ReDim arrAction(1 To _
.Shapes.Count, 1 To 2)

For Each shp In .Shapes
If shp.OnAction <> "" Then
i = i + 1
arrAction(i, 1) = shp.Name
arrAction(i, 2) = shp.OnAction
End If
Next shp

.Name = "Publication"
.Move
End With

'Publication will now be the active sheet
With Sheets("Publication")
'Some shapes might not have an OnAction
'and some elements at the end of the array
'might be blank.
'Therefore use i as the upper limit when
'retrieving the data from the array.
For j = 1 To i
.Shapes(arrAction(j, 1)) _
.OnAction = arrAction(j, 2)
Next j
End With

End Sub