macros embedded in .xlt file

[Guest]

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").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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

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.