Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts

    Prompt for Signature on Every Save

    Platform
    Microsoft Excel 2010, Service Pack 1, running on Microsoft Windows XP, Service Pack 3
    Issue
    Macro Enabled Spreadsheet prompts for signature on every save.
    Detail
    The workbook in question contains a VBA macro that imports data from the most recently created of a set of text files. Another program creates a new text file each business day (The client is closed on Sundays.), which is a catalog of a FTP site that contains three main directories, each of which contains a subdirectory for each of the client's 11 offices. These 11 offices and the home office use these FTP directories to exchange files containing details of the days's transactions and other things, such as price changes.

    The VBA code works by enumerating the files, whose names end with their creation date, expressed as compactly as possible (YYYYMMDD). This naming scheme simplifies identifying the most recent file. However, it requires the predefined data import (QueryTable) to be modified by the macro, to point to the correct file.

    I suspect that it is this dynamic query modification of the QueryTable that provokes the prompt, which is identical to the prompt seen when a digitally signed VBA project is saved. The prompt accepts the same passphrase that secures the Authenticode code signing certificate (issued to me by GlobalSign) with which the VBA project is signed.

    I could, of course, change the import so that it looks for a specific file, and change the VBA to copy or temporarily rename the desired data file. However, before I do so, I want to explore other options, such as overlooked settings in the Trust Center.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi

    It is not clear exactly what you are asking for.
    If you don't want the Save prompt when the workbook is closed,
    perhaps you can just add, in the Workbook_BeforeClose event routine, a line which says
    ActiveWorkbook.Saved = True

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Zeddy,

    Please re-read my post, and tell my why you think I don't want to save the workbook. If I just wanted to abandon the workbook, I wouldn't be wasting a slot with a question, because I have been writing VBA for Word and Excel since 1997, and I know several ways to close a workbook without saving it.

    The motivation for this post is to elicit suggestions that may help me to identify the cause of the digital signature prompt, and to make whatever changes I need to stop it. Since I have lots of other signed VBA projects, I know that something different about it is causing me to be prompted for permission to use my code signing certificate every time I save, since this isn't normal behavior for signed, compiled VBA code.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi TX

    OK.
    My understanding is that whenever code in a signed macro project is modified in any way, if you have the proper digital certificate on your computer, the macro project will automatically be resigned when saved.

    So, I assume you are not changing the VBA, and yet you are being prompted whenever you attempt to save the workbook?

    You have a digitally-signed workbook, that has been changed.
    And now you want to save this modified workbook and keep its original digital signature?

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by zeddy View Post
    Hi TX

    OK.
    My understanding is that whenever code in a signed macro project is modified in any way, if you have the proper digital certificate on your computer, the macro project will automatically be resigned when saved.
    That is my understanding, too. The only factor that changes the scenario slightly is that, when I installed the private key, I instructed Windows to prompt for the passphrase whenever the key is used. Hence, if I change VBA code, I fully expect to be prompted, and I am.


    Quote Originally Posted by zeddy View Post
    So, I assume you are not changing the VBA, and yet you are being prompted whenever you attempt to save the workbook?
    That is correct.


    Quote Originally Posted by zeddy View Post
    You have a digitally-signed workbook, that has been changed.
    And now you want to save this modified workbook and keep its original digital signature?
    No. I have a digitally signed VBA project, which is part of an unsigned macro-enabled workbook. Only the VBA project is supposed to be signed, not the entire document.

    I have many other signed projects embedded in XLSM files, such as, for example, the workbooks in which I record billable hours. I save them, often multiple times each day, on the same and other machines, all of which have the high safety settings for using my private key. None of them has ever prompted me for the key during routine use.

    Hence, I am trying to figure out what is different about this workbook that causes it to prompt for the key every time I save it.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi TX

    A tricky issue to replicate and resolve then.
    Meanwhile, for what it's worth, I save all my macro-enable workbooks as binary .xlsb files rather than .xlsm, for smaller, faster-loading file-size.

    zeddy

  7. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    I recently learned about XLSB, and this one is a good candidate for such treatment. Over a month, it accumulates around 24 sheets with an average of 700 rows and 7 columns each. That's a lot of XML bloat, and I'll probably convert the file to binary. First, however, I must remember to do it when I have the file open.

    If I don't hear from somebody else by this weekend, I'll see about posting the workbook and a data file that can be used to exercise the macro.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

Posting Permissions

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