Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Templates (2003 SP2)

    I am creating a template that will be used read-only by others to create workbooks.

    I can leave the comments toggled to show when they open the template.

    When they save, they will be prompted for a file name because the template is read-only.

    Is there a way to toggle the comments at that point so they don't show on open once a workbook has been created?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Templates (2003 SP2)

    I assume that you mean a real Excel template (an .xlt file). Make sure that it has been saved to disk, then add the following code to the ThisWorkbook module (not to a standard module!):
    <code>
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim i As Integer
    Dim wsh As Worksheet
    If InStr(Me.FullName, "") = 0 Then
    For Each wsh In Me.Worksheets
    For i = wsh.Comments.Count To 1 Step -1
    wsh.Comments(i).Delete
    Next i
    Next wsh
    End If
    End Sub
    </code>
    Then save the template again.
    The code will remove *all* comments from a new workbook based on the template before it is saved for the first time. It'll leave comments added after the first save alone.
    The code will only work if users enable macros.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Templates (2003 SP2)

    Very cool HansV. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •