Embed Template Within File

Re: Embed Worksheet Template Within File

If a Worksheet is added to a Workbook and sent elsewhere the Template Worksheet will exist in the sent file. However, it will not be readily available to the recepient.

Why not create the no-data template in the Workbook, make it XlVeryHidden and use the [woe]*[/woe] to save the template to the users template folder?

Re: Embed Worksheet Template Within File

Quote

I want to know if there is any way the worksheet template can be stored within an excel file, so that if the file is sent to people on a different computer, the user can easily add more worksheets using the template?

Yes.

I would store the template worksheet within the selfsame workbook as an invisible worksheet.

Your method of user accessibility is up to you. My opinion? On workbook open, check for the existence of this template in the templates folder. If it does not exist, create it automatically. Now this template is available when the user browses the templates folder.

Another option would be to add a menu item directly below "Insert" such as "Insert New Main". This would insert a copy of the invisible worksheet into the workbook without the need for browsing all available templates.

Which way do you want to go?

Re: Embed Template Within File

You will need to do five things to set this up.

• Add the worksheet that will serve as a template to your workbook if it is not already there.
• Copy and paste the code listed below into "The Workbook Class". The default name of this class is "ThisWorkbook"
• Within the code below, see Private Const ButtonCaption As String = "Insert My Template". "Insert My Template" is the caption that will be displayed on your button. Edit to suit.
• Also within the code below, see With Sheets("TemplateMain"). "TemplateMain" is the actual tabname of your soon to be hidden template worksheet. Do one of two things.
  
  
  • Rename the template worksheet to = "TemplateMain" or...
  • ...change "TemplateMain" within the code to reflect the correct name as seen in the tab of the worksheet.

  
  

• Save and close the workbook. Upon reopening, the button will be added and the template worksheet will be very hidden.

If all else fails, see the link to an example workbook using the exact code as listed.

TemplateWorksheet.zip

Option Explicit


Private Const ButtonCaption As String = "Insert My Template"


Private Sub Workbook_Open()
    CreateButton
End Sub


Private Sub Workbook_Activate()
    ButtonVisible True
End Sub


Private Sub Workbook_Deactivate()
    ButtonVisible False
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteButton
End Sub


Private Sub CreateButton()
    Dim cb As CommandBar
    Dim cbb As CommandBarButton
    Dim InsertIndex As Variant
    
    On Error Resume Next
    Set cb = Application.CommandBars("Ply")
    InsertIndex = cb.Controls("&Insert...").Index + 1
    
    If InsertIndex > cb.Controls.Count Then
        Set cbb = cb.Controls.Add(msoControlButton, , , , True)
    Else
        Set cbb = cb.Controls.Add(msoControlButton, , , InsertIndex, True)
    End If
    
    On Error GoTo 0
    If Not cb Is Nothing And Not cbb Is Nothing Then
        cbb.Caption = ButtonCaption
        cbb.Style = msoButtonCaption
        cbb.OnAction = "ThisWorkbook.AddMyTemplate"
    End If
     
End Sub


Friend Sub AddMyTemplate()
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    With Sheets("TemplateMain")
        .Visible = xlSheetVisible
        .Copy Before:=Sheets(1)
        .Visible = xlSheetVeryHidden
    End With
     
End Sub


Private Sub DeleteButton()
    On Error Resume Next
    Application.CommandBars("Ply").Controls(ButtonCaption).Delete
End Sub


Private Sub ButtonVisible(Optional IsVisible As Boolean)
    On Error Resume Next
    Application.CommandBars("Ply").Controls(ButtonCaption).Visible = IsVisible
End Sub

Re: Embed Template Within File

think it might be advisable to turn ScreenUpdating back on in the macro AddMyTemplate

Re: Embed Template Within File

It definitely would not hurt to add:
Application.ScreenUpdating = True at the end.

Nevertheless, in a procedure that is running solo in the stack it is not neccesary. It will go back to true when the procedure is finished...

Re: Embed Template Within File

Well... I tried to edit my previous post and add the line, "Application.ScreenUpdating = True", but there is a 60 minute window to edit posts.

Re: Embed Template Within File

Nope, same code, just a different method of running it.