Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting sheet.xlt for a workbook programatically
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting sheet.xlt for a workbook programatically
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) "trooper665" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting sheet.xlt for a workbook programatically
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? "Roger Whitehead" wrote: 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) "trooper665" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a cell reference in one sheet that updates a workbook? | Excel Discussion (Misc queries) | |||
programatically retrieve selected rows from excel workbook | Excel Programming | |||
Programatically sending a sheet through Outlook | Excel Programming | |||
Programatically saving workbook (or worksheet) without the code | Excel Programming | |||
Copy Worksheet to another workbook, programatically? | Excel Programming |