Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros embedded in .xlt file
Hello:
We have a few macros embedded in a .xlt (Excel template) file for a client of ours. So the client would not have to individually run each of the five macros on her workstation, we created a custom button (smiley face) that runs one of the macros. And, this one macro runs the others as well. The name of this macro is "Subtotal". For some reason, when the client hits this custom button inside the template file, two spreadsheets are generated rather than just one. Why is this? Do macros in Excel template files just naturally generate duplicate spreadsheets like this? Below is the code. If you have any ideas on how to prevent this duplications of spreadsheets, that would be great! Module 1: Sub Format_Header() ' ' Format_Header Macro ' Macro recorded 6/14/2006 to format column header row Range("A1:K1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("A1").Select End Sub Sub Subtotal() ' ' Subtotal Macro ' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _ Replace:=True, PageBreaks:=True, SummaryBelowData:=True Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Format_Header Col_Width Col_Headers End Sub Sub Col_Width() ' ' Col_Width Macro ' Macro recorded 6/14/2006 to set column widths and row height ' Columns("A:A").Select Selection.ColumnWidth = 4.57 Columns("B:B").Select Selection.ColumnWidth = 9.15 Columns("C:C").Select Selection.ColumnWidth = 9.71 Columns("D:D").Select Selection.ColumnWidth = 17.5 Columns("E:E").Select Selection.ColumnWidth = 12 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("G:G").Select Selection.ColumnWidth = 16 Columns("H:H").Select Selection.ColumnWidth = 4.45 Columns("I:I").Select Selection.ColumnWidth = 25 Columns("J:J").Select Selection.ColumnWidth = 8 Columns("K:K").Select Selection.ColumnWidth = 8 Range("A1:K20000").Select Range("A4").Activate Selection.RowHeight = 16 End Sub Sub Col_Headers() ' ' Col_Headers Macro ' Macro recorded 6/14/2006 to set column headers ' Range("A1").Select ActiveCell.FormulaR1C1 = "Plant" Range("G1").Select ActiveCell.FormulaR1C1 = "Cust Item No" Range("B1").Select ActiveCell.FormulaR1C1 = "Ship Date" Range("C1").Select ActiveCell.FormulaR1C1 = "Plant Date" Range("H1").Select ActiveCell.FormulaR1C1 = "Type" Range("K1").Select ActiveCell.FormulaR1C1 = "Rem Qty" Range("A1").Select End Sub Module 2: Sub Macro7() ' ' Macro7 Macro ' Macro recorded 6/14/2006 ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select End Sub Thanks, again! childofthe1980s |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros embedded in .xlt file
I didn't see anything in the code that creates any worksheets.
No Activesheet.copy or an Add command. I would look where you have code like that. -- Regards, Tom Ogilvy "childofthe1980s" wrote: Hello: We have a few macros embedded in a .xlt (Excel template) file for a client of ours. So the client would not have to individually run each of the five macros on her workstation, we created a custom button (smiley face) that runs one of the macros. And, this one macro runs the others as well. The name of this macro is "Subtotal". For some reason, when the client hits this custom button inside the template file, two spreadsheets are generated rather than just one. Why is this? Do macros in Excel template files just naturally generate duplicate spreadsheets like this? Below is the code. If you have any ideas on how to prevent this duplications of spreadsheets, that would be great! Module 1: Sub Format_Header() ' ' Format_Header Macro ' Macro recorded 6/14/2006 to format column header row Range("A1:K1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("A1").Select End Sub Sub Subtotal() ' ' Subtotal Macro ' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _ Replace:=True, PageBreaks:=True, SummaryBelowData:=True Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Format_Header Col_Width Col_Headers End Sub Sub Col_Width() ' ' Col_Width Macro ' Macro recorded 6/14/2006 to set column widths and row height ' Columns("A:A").Select Selection.ColumnWidth = 4.57 Columns("B:B").Select Selection.ColumnWidth = 9.15 Columns("C:C").Select Selection.ColumnWidth = 9.71 Columns("D:D").Select Selection.ColumnWidth = 17.5 Columns("E:E").Select Selection.ColumnWidth = 12 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("G:G").Select Selection.ColumnWidth = 16 Columns("H:H").Select Selection.ColumnWidth = 4.45 Columns("I:I").Select Selection.ColumnWidth = 25 Columns("J:J").Select Selection.ColumnWidth = 8 Columns("K:K").Select Selection.ColumnWidth = 8 Range("A1:K20000").Select Range("A4").Activate Selection.RowHeight = 16 End Sub Sub Col_Headers() ' ' Col_Headers Macro ' Macro recorded 6/14/2006 to set column headers ' Range("A1").Select ActiveCell.FormulaR1C1 = "Plant" Range("G1").Select ActiveCell.FormulaR1C1 = "Cust Item No" Range("B1").Select ActiveCell.FormulaR1C1 = "Ship Date" Range("C1").Select ActiveCell.FormulaR1C1 = "Plant Date" Range("H1").Select ActiveCell.FormulaR1C1 = "Type" Range("K1").Select ActiveCell.FormulaR1C1 = "Rem Qty" Range("A1").Select End Sub Module 2: Sub Macro7() ' ' Macro7 Macro ' Macro recorded 6/14/2006 ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select End Sub Thanks, again! childofthe1980s |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros embedded in .xlt file
Thanks, Tom.
What's funny is that I just recreated this on my laptop and did not get the duplication that my client is seeing. Could it be something environmental? childofthe1980s "Tom Ogilvy" wrote: I didn't see anything in the code that creates any worksheets. No Activesheet.copy or an Add command. I would look where you have code like that. -- Regards, Tom Ogilvy "childofthe1980s" wrote: Hello: We have a few macros embedded in a .xlt (Excel template) file for a client of ours. So the client would not have to individually run each of the five macros on her workstation, we created a custom button (smiley face) that runs one of the macros. And, this one macro runs the others as well. The name of this macro is "Subtotal". For some reason, when the client hits this custom button inside the template file, two spreadsheets are generated rather than just one. Why is this? Do macros in Excel template files just naturally generate duplicate spreadsheets like this? Below is the code. If you have any ideas on how to prevent this duplications of spreadsheets, that would be great! Module 1: Sub Format_Header() ' ' Format_Header Macro ' Macro recorded 6/14/2006 to format column header row Range("A1:K1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("A1").Select End Sub Sub Subtotal() ' ' Subtotal Macro ' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _ Replace:=True, PageBreaks:=True, SummaryBelowData:=True Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Format_Header Col_Width Col_Headers End Sub Sub Col_Width() ' ' Col_Width Macro ' Macro recorded 6/14/2006 to set column widths and row height ' Columns("A:A").Select Selection.ColumnWidth = 4.57 Columns("B:B").Select Selection.ColumnWidth = 9.15 Columns("C:C").Select Selection.ColumnWidth = 9.71 Columns("D:D").Select Selection.ColumnWidth = 17.5 Columns("E:E").Select Selection.ColumnWidth = 12 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("G:G").Select Selection.ColumnWidth = 16 Columns("H:H").Select Selection.ColumnWidth = 4.45 Columns("I:I").Select Selection.ColumnWidth = 25 Columns("J:J").Select Selection.ColumnWidth = 8 Columns("K:K").Select Selection.ColumnWidth = 8 Range("A1:K20000").Select Range("A4").Activate Selection.RowHeight = 16 End Sub Sub Col_Headers() ' ' Col_Headers Macro ' Macro recorded 6/14/2006 to set column headers ' Range("A1").Select ActiveCell.FormulaR1C1 = "Plant" Range("G1").Select ActiveCell.FormulaR1C1 = "Cust Item No" Range("B1").Select ActiveCell.FormulaR1C1 = "Ship Date" Range("C1").Select ActiveCell.FormulaR1C1 = "Plant Date" Range("H1").Select ActiveCell.FormulaR1C1 = "Type" Range("K1").Select ActiveCell.FormulaR1C1 = "Rem Qty" Range("A1").Select End Sub Module 2: Sub Macro7() ' ' Macro7 Macro ' Macro recorded 6/14/2006 ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select End Sub Thanks, again! childofthe1980s |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros embedded in .xlt file
I suppose it could be environmental. Perhaps there is a disability setting
(that was accidently applied in the windows control panel) that causes two mouse clicks to be generated if the user holds the mouse down too long or something similar. -- Regards, Tom Ogilvy "childofthe1980s" wrote: Thanks, Tom. What's funny is that I just recreated this on my laptop and did not get the duplication that my client is seeing. Could it be something environmental? childofthe1980s "Tom Ogilvy" wrote: I didn't see anything in the code that creates any worksheets. No Activesheet.copy or an Add command. I would look where you have code like that. -- Regards, Tom Ogilvy "childofthe1980s" wrote: Hello: We have a few macros embedded in a .xlt (Excel template) file for a client of ours. So the client would not have to individually run each of the five macros on her workstation, we created a custom button (smiley face) that runs one of the macros. And, this one macro runs the others as well. The name of this macro is "Subtotal". For some reason, when the client hits this custom button inside the template file, two spreadsheets are generated rather than just one. Why is this? Do macros in Excel template files just naturally generate duplicate spreadsheets like this? Below is the code. If you have any ideas on how to prevent this duplications of spreadsheets, that would be great! Module 1: Sub Format_Header() ' ' Format_Header Macro ' Macro recorded 6/14/2006 to format column header row Range("A1:K1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("A1").Select End Sub Sub Subtotal() ' ' Subtotal Macro ' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _ Replace:=True, PageBreaks:=True, SummaryBelowData:=True Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Format_Header Col_Width Col_Headers End Sub Sub Col_Width() ' ' Col_Width Macro ' Macro recorded 6/14/2006 to set column widths and row height ' Columns("A:A").Select Selection.ColumnWidth = 4.57 Columns("B:B").Select Selection.ColumnWidth = 9.15 Columns("C:C").Select Selection.ColumnWidth = 9.71 Columns("D:D").Select Selection.ColumnWidth = 17.5 Columns("E:E").Select Selection.ColumnWidth = 12 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("G:G").Select Selection.ColumnWidth = 16 Columns("H:H").Select Selection.ColumnWidth = 4.45 Columns("I:I").Select Selection.ColumnWidth = 25 Columns("J:J").Select Selection.ColumnWidth = 8 Columns("K:K").Select Selection.ColumnWidth = 8 Range("A1:K20000").Select Range("A4").Activate Selection.RowHeight = 16 End Sub Sub Col_Headers() ' ' Col_Headers Macro ' Macro recorded 6/14/2006 to set column headers ' Range("A1").Select ActiveCell.FormulaR1C1 = "Plant" Range("G1").Select ActiveCell.FormulaR1C1 = "Cust Item No" Range("B1").Select ActiveCell.FormulaR1C1 = "Ship Date" Range("C1").Select ActiveCell.FormulaR1C1 = "Plant Date" Range("H1").Select ActiveCell.FormulaR1C1 = "Type" Range("K1").Select ActiveCell.FormulaR1C1 = "Rem Qty" Range("A1").Select End Sub Module 2: Sub Macro7() ' ' Macro7 Macro ' Macro recorded 6/14/2006 ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select End Sub Thanks, again! childofthe1980s |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros embedded in .xlt file
Could it be that the button is assigned to the template file -- not the workbook
based on the template and excel figures it needs to open that .xlt file whenever the button is clicked? But I wasn't sure what kind of button you created or where it was located. Was it a button on a toolbar? Was it a button from the Forms toolbar placed on a worksheet? Was it a button from the Control toolbox toolbar placed on a worksheet? And as a personal preference, I wouldn't name any macro the same as a builtin worksheet function. It may not confuse excel, but it might confuse me. childofthe1980s wrote: Hello: We have a few macros embedded in a .xlt (Excel template) file for a client of ours. So the client would not have to individually run each of the five macros on her workstation, we created a custom button (smiley face) that runs one of the macros. And, this one macro runs the others as well. The name of this macro is "Subtotal". For some reason, when the client hits this custom button inside the template file, two spreadsheets are generated rather than just one. Why is this? Do macros in Excel template files just naturally generate duplicate spreadsheets like this? Below is the code. If you have any ideas on how to prevent this duplications of spreadsheets, that would be great! Module 1: Sub Format_Header() ' ' Format_Header Macro ' Macro recorded 6/14/2006 to format column header row Range("A1:K1").Select Selection.Font.Bold = True With Selection.Interior .ColorIndex = 15 .Pattern = xlSolid End With Range("A1").Select End Sub Sub Subtotal() ' ' Subtotal Macro ' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _ Replace:=True, PageBreaks:=True, SummaryBelowData:=True Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _ Replace:=False, PageBreaks:=False, SummaryBelowData:=True Format_Header Col_Width Col_Headers End Sub Sub Col_Width() ' ' Col_Width Macro ' Macro recorded 6/14/2006 to set column widths and row height ' Columns("A:A").Select Selection.ColumnWidth = 4.57 Columns("B:B").Select Selection.ColumnWidth = 9.15 Columns("C:C").Select Selection.ColumnWidth = 9.71 Columns("D:D").Select Selection.ColumnWidth = 17.5 Columns("E:E").Select Selection.ColumnWidth = 12 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("G:G").Select Selection.ColumnWidth = 16 Columns("H:H").Select Selection.ColumnWidth = 4.45 Columns("I:I").Select Selection.ColumnWidth = 25 Columns("J:J").Select Selection.ColumnWidth = 8 Columns("K:K").Select Selection.ColumnWidth = 8 Range("A1:K20000").Select Range("A4").Activate Selection.RowHeight = 16 End Sub Sub Col_Headers() ' ' Col_Headers Macro ' Macro recorded 6/14/2006 to set column headers ' Range("A1").Select ActiveCell.FormulaR1C1 = "Plant" Range("G1").Select ActiveCell.FormulaR1C1 = "Cust Item No" Range("B1").Select ActiveCell.FormulaR1C1 = "Ship Date" Range("C1").Select ActiveCell.FormulaR1C1 = "Plant Date" Range("H1").Select ActiveCell.FormulaR1C1 = "Type" Range("K1").Select ActiveCell.FormulaR1C1 = "Rem Qty" Range("A1").Select End Sub Module 2: Sub Macro7() ' ' Macro7 Macro ' Macro recorded 6/14/2006 ' Range("A1").Select Selection.CurrentRegion.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Range("A1").Select End Sub Thanks, again! childofthe1980s -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Embedded file name changes | Excel Discussion (Misc queries) | |||
VB Controls with Macros embedded in Excel Document | Excel Discussion (Misc queries) | |||
macros embedded | Excel Discussion (Misc queries) | |||
swf flash file embedded in html file which is edited in excel.. he | Excel Discussion (Misc queries) | |||
Slow running of Excel macros embedded in an ActiveX | Excel Programming |