Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro opens input box only on Template (Excel 97 SR2)

    Hi,
    I created two Excel templates (one for each agency) that tracks Shared Leave. They are the same except for Agency Name (in one cell) and the date in another cell. I want to combine them into one Template that when opened, asks which agency it is for. Based on the response, the macro will enter a 1 or 0 in a specified cell. After the workbook is saved (which changes it from a Template to a Workbook), the question will not be asked again. I can write the macro to make the proper changes to the workbook based on the 0 or 1 that was entered.
    Short version: Macro that asks which agency the resulting Workbook is for is run whenTemplate is opened but does NOT run when the Workbook that the Template creates is opened for entry. I hope this is clear and contains all the information needed.

    Thanks,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro opens input box only on Template (Excel 97 SR2)

    You could add your code into an IF like this:

    <pre>Option Explicit
    Private Sub Workbook_Open()
    If Right(ThisWorkbook.Name, 4) <> ".xls" Then
    'add your code here
    End If
    End Sub</pre>


    If the template is opened directly, or if a file is created (file- new) from the template, the file name will not have and "xls" extension and the code will run. Once the newly created file is saved as a workbook (xls extension) it will be ignored and not run anymore.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro opens input box only on Template (Excel 97 SR2)

    Steve,

    Thanks SOOOOO much for the speedy reply (not to mention the solution!).
    I am sorry it took so long to reply. It has been a typical Monday.....
    The solution is a thing of beauty.

    Thanks again,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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