Setting sheet.xlt for a workbook programatically

[Guest]

Is there any way to set one particular workbook to use a sheet.xlt template
but still have Excel generally open with a sheet template?

Basically I have a workbook.xlt template that I am using to create a new
workbook through a vb6 program. Once the program is finished running I would
like the user to be able to add a new sheet that has the same formatting as
the workbook.xlt. Is there some property that I can set that will make the
workbook.xlt use the sheet.xlt when creating a new sheet? Thanks

 

[Roger Whitehead]

Try using the NewSheet event:

Delete the sheet the user is creating,
Copy an existing (hidden?) sheet into it's place.

Snippet:
--------------------------------------------------------------------
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim varSheetType As Variant, strShName As String
Application.EnableEvents = False
varSheetType = MsgBox("Do you want to create a new Post Recording
worksheet?", vbYesNoCancel, "Post Log")

Select Case varSheetType
Case vbYes
Application.StatusBar = "Please Wait"
Sheets("Template").Range("A1:I1").Copy Sh.Range("A1:I1")

For c = 1 To 9 Step 1
Sh.Columns(c).ColumnWidth =
Sheets("Template").Columns(c).ColumnWidth
Next c
With Sh.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 85
End With
'Sh.Columns("A:I").AutoFit
With Sh
.Range("A1:I2500").AutoFilter
.Range("B2:IV65536").Locked = False
.Columns("J:IV").Hidden = True
.Range("B2:I1000").ClearContents
[B2].Select
End With
Case vbNo

Case vbCancel
Sh.Delete
End Select
Range("H2:H1000").ClearContents
Application.EnableEvents = True
Application.StatusBar = False
End Sub

 

[Guest]

Well, I am using the workbook in a vb6 application so if the user decides to
insert a new sheet then the program no longer has focus. Basically I want to
duplicate the functionality of having a Sheet.xlt in the XLStart folder for
only one distinct workbook. I don't want to use this sheet.xlt template at
any other time except when this workbook is open. Is this possible?

Try using the NewSheet event:

Delete the sheet the user is creating,
Copy an existing (hidden?) sheet into it's place.

Snippet:
--------------------------------------------------------------------
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim varSheetType As Variant, strShName As String
Application.EnableEvents = False
varSheetType = MsgBox("Do you want to create a new Post Recording
worksheet?", vbYesNoCancel, "Post Log")

Select Case varSheetType
Case vbYes
Application.StatusBar = "Please Wait"
Sheets("Template").Range("A1:I1").Copy Sh.Range("A1:I1")

For c = 1 To 9 Step 1
Sh.Columns(c).ColumnWidth =
Sheets("Template").Columns(c).ColumnWidth
Next c
With Sh.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 85
End With
'Sh.Columns("A:I").AutoFit
With Sh
.Range("A1:I2500").AutoFilter
.Range("B2:IV65536").Locked = False
.Columns("J:IV").Hidden = True
.Range("B2:I1000").ClearContents
[B2].Select
End With
Case vbNo

Case vbCancel
Sh.Delete
End Select
Range("H2:H1000").ClearContents
Application.EnableEvents = True
Application.StatusBar = False
End Sub
--------------------------------------------------------------------

--
HTH
Roger
Shaftesbury (UK)

Is there any way to set one particular workbook to use a sheet.xlt
template
but still have Excel generally open with a sheet template?

Basically I have a workbook.xlt template that I am using to create a new
workbook through a vb6 program. Once the program is finished running I
would
like the user to be able to add a new sheet that has the same formatting
as
the workbook.xlt. Is there some property that I can set that will make
the
workbook.xlt use the sheet.xlt when creating a new sheet? Thanks