none
Enforce template code loading, maybe via workbook protection? RRS feed

  • Question

  • Hi Everybody,

    I would like an Excel Template (.xltx) to be inaccessible unless the template code has been loaded.
    Specifically we are using this Template as a Document Library template in SharePoint.
    When a user clicks 'New Document' the template is opened in Excel and the user is given the choice to install the template (code).
    I would like to block all access to this Excel Template unless the user has installed the Template code.
    I thought that a possible way would be to protect the workbook with a password and when the Template code is loaded Unprotect the workbook from the code.
    But I found out that the password is prompted before the template installation starts.
    So, does anyone know of a clever/simple/complex way to accomplish something like this?

    Thanks in advance,
    Michael

    • Moved by Leo_GaoModerator Monday, October 15, 2012 9:25 AM (From:Visual Studio Tools for Office)
    • Moved by Leo_GaoModerator Tuesday, October 16, 2012 1:48 AM (From:Excel for Developers)
    Sunday, October 14, 2012 7:15 PM

Answers

  • Hi michael

    With all the back-and-forth... Still Leo was probably not too far wrong with thinking the Excel forum would be the best place to get suggestions for this scenario. You'd just have to ask them to think in terms of VBA :-)

    I'm really more a Word person, but I do have some basic thoughts on the topic and since nothing useful has happened on this thread, so far, here they are.

    Excel (as Word) has two kinds of "password protection". The one you describe applies to the file - you need a password to open it. The other kind applies to the content of the file: you can lock cells on a worksheet (including the entire sheet) to protect them from changes, but the content can still be viewed. I believe this is considered part of the cell formatting and the commmands in the UI can be found in that area.

    If you also don't want the user to be able to see certain content, the workbook could be saved with the sheets "hidden" or "very hidden" - perhaps with one sheet containing or even the "log-in" form visible. Or have this "log-in" sheet at the front and use events to prevent access the other sheets until you've determined the current user is allowed to view them.

    For highly sensitive Word documents another approach has been to save the actual content outside the document and only write it in when the user has been confirmed.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, October 16, 2012 7:11 AM
    Moderator

All replies

  • Hi Michael,

    Your issue is more like an Excel question. I would move this thread to Excel For Developer Fourm for more dedicated support.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Monday, October 15, 2012 9:36 AM
    Moderator
  • Templates cannot contain VBA code. Where do you propose to put the code ?

    re: "unless the user has installed the Template code"

    I recommend putting it into an XLAM (Excel Addin). Unfortunately, that does not stop the user from using the template unless it is password protected. If you want to bypass the password prompt if the add-in is active, I think you may have to cancel the opening of the password-protected template and open up a clone of the template that has no password. You'll need an application-level workbook open reference. See http://www.cpearson.com/Excel/AppEvent.aspx for more info on how to do that.

    Monday, October 15, 2012 2:44 PM
  • Hi Syswizard,

    I am talking about a template with (C#) VSTO  code in it.
    That's why I originally posted this question in the VSTO forum.

    When you open such a template for the first time it pops up an installation dialog and prompts the user whether to continue with installation, if the users chooses not to install the template code the template opens in Excel without loading the code and that's what I want to prevent.

    Another case when this can happen is when the location of the template is not specified as a trusted location in the Trust Center in Excel.
    In this case the user gets an error message stating that the template code can not be loaded from that location and again the template opens as a usual Excel document without the custom code.

    Ultimately I want the user to be able to open the Template file only if he has also installed the custom C# code which comes with it.

    Is it the right forum for this question? Or maybe I should take it back to the VSTO forum?

    Thanks,
    Michael 

    Monday, October 15, 2012 7:42 PM
  • Hi Syswizard,

    I am talking about a template with (C#) VSTO  code in it.
    That's why I originally posted this question in the VSTO forum.

    Ultimately I want the user to be able to open the Template file only if he has also installed the custom C# code which comes with it.

    Is it the right forum for this question? Or maybe I should take it back to the VSTO forum?


    Definitely VSTO.
    Monday, October 15, 2012 8:55 PM
  • Hi Michael and Syswizard,

    Sorry for my misunderstanding on this issue. Now I move this thread back to VSTO Forum.

    Much appreciate for your understanding.

    Best Regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, October 16, 2012 1:47 AM
    Moderator
  • Hi michael

    With all the back-and-forth... Still Leo was probably not too far wrong with thinking the Excel forum would be the best place to get suggestions for this scenario. You'd just have to ask them to think in terms of VBA :-)

    I'm really more a Word person, but I do have some basic thoughts on the topic and since nothing useful has happened on this thread, so far, here they are.

    Excel (as Word) has two kinds of "password protection". The one you describe applies to the file - you need a password to open it. The other kind applies to the content of the file: you can lock cells on a worksheet (including the entire sheet) to protect them from changes, but the content can still be viewed. I believe this is considered part of the cell formatting and the commmands in the UI can be found in that area.

    If you also don't want the user to be able to see certain content, the workbook could be saved with the sheets "hidden" or "very hidden" - perhaps with one sheet containing or even the "log-in" form visible. Or have this "log-in" sheet at the front and use events to prevent access the other sheets until you've determined the current user is allowed to view them.

    For highly sensitive Word documents another approach has been to save the actual content outside the document and only write it in when the user has been confirmed.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, October 16, 2012 7:11 AM
    Moderator
  • Hi Cindy,

    Thank you so much for your help! :)

    I liked the idea of loading external content, sounds like a possible solution for my use case.

    Best regards,
    Michael

    Tuesday, October 16, 2012 8:18 AM