Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Source is Changing
I have a workbook called Inventory.xlsm. This has several macros which are
selected with a button. One of them is "PO" which creates a Purchase Order based on items selected in the Inventory workbook. The button is assigned to the macro Inventory.xlsm!PO The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the file and closes c:\mypath\NewPO.xlsx This all works - the file is created successfully. However, at this point all macro buttons in Inventory.xlsm have been reassigned so that I can't run them. For example, the PO button is now assigned to c:\mypath\NewPO.xlsx!PO. Any ideas are appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Source is Changing
Can we start by seeing the section of code that creates and saves the
NewPO.xlsx file? "dhstein" wrote: I have a workbook called Inventory.xlsm. This has several macros which are selected with a button. One of them is "PO" which creates a Purchase Order based on items selected in the Inventory workbook. The button is assigned to the macro Inventory.xlsm!PO The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the file and closes c:\mypath\NewPO.xlsx This all works - the file is created successfully. However, at this point all macro buttons in Inventory.xlsm have been reassigned so that I can't run them. For example, the PO button is now assigned to c:\mypath\NewPO.xlsx!PO. Any ideas are appreciated |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Source is Changing
JLatham
1) Thanks for the response. I'm going to include the entire macro here. But before you get too involved in tracking this down, I just created a Macro to reassign all buttons which I will run after the "Create PO" macro and that should resolve the problem. If you are intellectually curious, then please go ahead and see what you can find - I would love to understand why this is happening. Thanks. Sub CreatePO() Dim Calc As String If Application.Calculation = xlCalculationManual Then Calc = "Manual" Else Calc = "Automatic" End If Application.Calculation = xlCalculationManual Dim SaveFile As String Dim NewSheetName As String Dim RangeToUse As String Dim SortColumn As String Dim SortRange As String Dim Range1 As String Dim Range2 As String Dim Range3 As String Application.Calculation = xlCalculationManual SaveFile = "PO Template with Description" SavePath = "C:\Program Files\" If FolderExists("C:\DATA\DAVID\DEVELOPMENT") Then SavePath = "C:\DATA\DAVID\DEVELOPMENT\Inventory Project\Excel\PO\" Else SavePath = SavePath & "01 Transaction Pro Importer 3.0\" End If RangeToUse = Range("BK6").Value ' Range in Inventory Sheet to Copy ' Set up all ranges to use SortColumn = Range("BL5").Value SortRange = Range("BL6").Value Range1 = Range("BK9").Value Range2 = Range("BK10").Value Range3 = Range("BK11").Value 'MsgBox RangeToUse Sheets("PO").Select ' Clear out old data Range("AA2:AX500").ClearContents Sheets("Inventory").Select Range(RangeToUse).Select ' Copy ordering data from Inventory sheet to PO sheet Selection.Copy 'ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("PO").Select Range("AA2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ' Sort PO data on "Order" column - to eliminate blanks Range(SortRange).Select ActiveWorkbook.Worksheets("PO").Sort.SortFields.Cl ear ActiveWorkbook.Worksheets("PO").Sort.SortFields.Ad d Key:=Range(SortColumn), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("PO").Sort .SetRange Range(SortRange) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ' Sort PO data - using only lines with order values Range(Range1).Select ActiveWorkbook.Worksheets("PO").Sort.SortFields.Cl ear ActiveWorkbook.Worksheets("PO").Sort.SortFields.Ad d Key:=Range(Range3), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("PO").Sort .SetRange Range(Range1) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Application.Calculation = xlCalculationAutomatic Range(Range2).Select Selection.Copy Sheets.Add After:=Sheets(Sheets.Count) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False NewSheetName = ActiveSheet.Name Sheets(NewSheetName).Select Application.CutCopyMode = False Sheets(NewSheetName).Move ActiveWorkbook.SaveAs Filename:= _ SavePath & SaveFile & ".xlsx", FileFormat:= _ xlOpenXMLWorkbook, CreateBackup:=False ActiveWorkbook.Close Range("A24").Select ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Inventory").Select Range("A1").Select If Calc = "Manual" Then Application.Calculation = xlCalculationManual End If End Sub "JLatham" wrote: Can we start by seeing the section of code that creates and saves the NewPO.xlsx file? "dhstein" wrote: I have a workbook called Inventory.xlsm. This has several macros which are selected with a button. One of them is "PO" which creates a Purchase Order based on items selected in the Inventory workbook. The button is assigned to the macro Inventory.xlsm!PO The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the file and closes c:\mypath\NewPO.xlsx This all works - the file is created successfully. However, at this point all macro buttons in Inventory.xlsm have been reassigned so that I can't run them. For example, the PO button is now assigned to c:\mypath\NewPO.xlsx!PO. Any ideas are appreciated |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Source is Changing
First, if you have procedures that are common to a bunch of workbooks, you may
find putting them in a separate workbook/addin a much better way to organize them. You could give the users a toolbar or menu item that allows them to run the macros. Another option would be to replace the buttons from the Forms toolbar with commandbuttons from the control toolbox toolbar. The code behind these commandbuttons live in the worksheet module and would get copied to the new workbook along with the sheet. You'll have to revise your code (slightly) if you want to do that. Or you could just reassign the correct macro to the current workbook after you copy the sheet. With activesheet 'or newwks or what represents the newly copied sheet .buttons("yourbuttonnamehere").onaction _ = "'" & .parent.name & "'!" & "whatevermacronamehere" end with If you have lots, you could even cycle through each button/object and replace the name of the old workbook with the new workbook. ====== I'd use the addin approach. I wouldn't want to have to update a macro with a minor change and not have some idea what needs updating. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm dhstein wrote: I have a workbook called Inventory.xlsm. This has several macros which are selected with a button. One of them is "PO" which creates a Purchase Order based on items selected in the Inventory workbook. The button is assigned to the macro Inventory.xlsm!PO The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the file and closes c:\mypath\NewPO.xlsx This all works - the file is created successfully. However, at this point all macro buttons in Inventory.xlsm have been reassigned so that I can't run them. For example, the PO button is now assigned to c:\mypath\NewPO.xlsx!PO. Any ideas are appreciated -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Source is Changing
ps.
Even though I wrote this message thinking that you were saving a single sheet into a new workbook, it still applies if you're doing a File|SaveAs. But instead of just looping through the objects on one sheet, you may have to loop through each sheet, then through all objects on each sheet. Dave Peterson wrote: First, if you have procedures that are common to a bunch of workbooks, you may find putting them in a separate workbook/addin a much better way to organize them. You could give the users a toolbar or menu item that allows them to run the macros. Another option would be to replace the buttons from the Forms toolbar with commandbuttons from the control toolbox toolbar. The code behind these commandbuttons live in the worksheet module and would get copied to the new workbook along with the sheet. You'll have to revise your code (slightly) if you want to do that. Or you could just reassign the correct macro to the current workbook after you copy the sheet. With activesheet 'or newwks or what represents the newly copied sheet .buttons("yourbuttonnamehere").onaction _ = "'" & .parent.name & "'!" & "whatevermacronamehere" end with If you have lots, you could even cycle through each button/object and replace the name of the old workbook with the new workbook. ====== I'd use the addin approach. I wouldn't want to have to update a macro with a minor change and not have some idea what needs updating. For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm dhstein wrote: I have a workbook called Inventory.xlsm. This has several macros which are selected with a button. One of them is "PO" which creates a Purchase Order based on items selected in the Inventory workbook. The button is assigned to the macro Inventory.xlsm!PO The macro creates a new workbook called c:\mypath\NewPO.xlsx and saves the file and closes c:\mypath\NewPO.xlsx This all works - the file is created successfully. However, at this point all macro buttons in Inventory.xlsm have been reassigned so that I can't run them. For example, the PO button is now assigned to c:\mypath\NewPO.xlsx!PO. Any ideas are appreciated -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM of Changing source value, pls help!!! | Excel Discussion (Misc queries) | |||
Changing Data Source | Excel Worksheet Functions | |||
Changing the source of a link | Excel Discussion (Misc queries) | |||
Changing the source of links. | Excel Discussion (Misc queries) | |||
changing query source | Excel Worksheet Functions |