Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro execution changes filename reference for all macros
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro execution changes filename reference for all macros
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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro reference changes with filename | Excel Discussion (Misc queries) | |||
Using filename hyperlinks in Macros | Excel Programming | |||
Macros - Force execution using workbook_open | Excel Discussion (Misc queries) | |||
Reference in a filename.. | Excel Worksheet Functions | |||
macros execution problem | Excel Programming |