certificates are being lost consistently

[Walt]

Hi,

I appears that any VBA code that adds or deletes Excel worksheets or
adds or deletes controls, like buttons on worksheets, will not retain
its code signing certificate on a user's machine. What about
temporary modifications to the Worksheet Menu Bar (Adding & deleting
controls here too)? Is there a list of such items/objects/whatever
somewhere? Am I wrong in my observation somehow? Are there best
practice ways to work around these?

In no case are my users looking at my code in the VBE or editing it
(Password protected & I know they don't want to be bothered with that),
yet the certificates are being lost consistently.

I thought - well, maybe the fact that I had event code on worksheets
being copied via VBA code might trigger something because when the user
chooses to copy that worksheet, there's more code created in the copy
(I supposed that might trigger the removal of the certificate). So I
moved the activate, deactivate, and change event code to the workbook
level and cleared all code from the worksheets that can be copied
(Adding logic to identify the worksheet involved and then applying the
appropriate logic). This did not work. Is there a residual effect
of code having been attached to a worksheet?

That's 5 question marks & probably enough for now. Help appreciated.

Best Regards,
Walt Weber
Win XP & 98 with Excel 2000, 2002, 2003
Thawte Cert with Verisign Timestamp

 

[Andy Tischaefer [MSFT]]

I will try to answer all five questions as best I can:

1. Am I wrong in my observation somehow?
Nope, what you are seeing is by design for Excel, for two reasons. The
first is that each sheet is represented as part of the VB Project, so when
you delete them (regardless of whether or not you've looked at the project
in the editor) you automatically make a change to the VB Project, which in
turn invalidates the digital signature. The second is that COM (ActiveX)
controls on the sheet have their properties stored in the VB Project, and
any time you delete (or add) a control to the sheet you are making a change
to the VB Project, albeit indirectly, so your digital signature will be
invalidated.

2. Is there a list of such objects somewhere?
Not that I know of. To the best of my knowledge, though, the only things
that will cause this to happen are deletion of sheets and Add/delete of
controls on a sheet

3. What about modifications to the menu bar?
No this should not be a problem

4. Is there a residual affect...?
No, see question one. Basically you are inadvertantly making changes to the
VB Project through what would be considered non-code-related activities in
the spreadsheet.

5. Are there best practices?
The best way to avoid this is to either not write code that does these
things...ok I know that isn't good enough. Really I think there are
two ways you can do this - one is find a different way to present the
controls instead of "on the sheet", maybe display a user form or something,
depending on how that fits into your scenario. Another would be to hide the
objects instead of deleting them. I'm not 100% sure this will work with
every control but it should cover your sheet scenario ok.

Ultimately the best way to avoid this when building a solution that needs to
do actions like deleting sheets or controls is to use a COM add-in or
managed com-addin that will hold all of your code, rather than having the
code attached to a specific document.

I hope this helps, though I realize none of these are the optimal solution.

- Andy Tischaefer
Test Lead, Microsoft Office

This posting is provided as is and confers no rights

 

[Walt]

Hi Andy,

Thank you for the thoughtful response.

So it looks like I as far as the controls go, I could just put the ones
I'm using on a floating toolbar to get much the same functionality -
that works for me. And, as I think about it, in most of the cases
where I want the user to be able to add and delete worksheets, there
would be a high end of 10 or 20 sheets. Toggling the visible property
could work for these if I preload the workbook with the high end number
of worksheets.

Thank you again, Andy.

Best Regards,
Walt

I will try to answer all five questions as best I can:

1. Am I wrong in my observation somehow?
Nope, what you are seeing is by design for Excel, for two reasons. The
first is that each sheet is represented as part of the VB Project, so when
you delete them (regardless of whether or not you've looked at the project
in the editor) you automatically make a change to the VB Project, which in
turn invalidates the digital signature. The second is that COM (ActiveX)
controls on the sheet have their properties stored in the VB Project, and
any time you delete (or add) a control to the sheet you are making a change
to the VB Project, albeit indirectly, so your digital signature will be
invalidated.

2. Is there a list of such objects somewhere?
Not that I know of. To the best of my knowledge, though, the only things
that will cause this to happen are deletion of sheets and Add/delete of
controls on a sheet

3. What about modifications to the menu bar?
No this should not be a problem

4. Is there a residual affect...?
No, see question one. Basically you are inadvertantly making changes to the
VB Project through what would be considered non-code-related activities in
the spreadsheet.

5. Are there best practices?
The best way to avoid this is to either not write code that does these
things...ok I know that isn't good enough. Really I think there are
two ways you can do this - one is find a different way to present the

 

[Simon]

2. Is there a list of such objects somewhere?
Not that I know of. To the best of my knowledge, though, the only things
that will cause this to happen are deletion of sheets and Add/delete of
controls on a sheet

I get the same problem when I use VBA to ADD a sheet (copy from a hidden
sheet) then add some links between the new sheet and the front sheet of the
workbook. There is no VBA/macro code associated with the hidden sheet and I'm
not doing anything more than creating a few cell formulae. Creating a bunch
of hidden sheets then unhiding them as required is not an option as I will
have to create about 80! That's too much to carry around in the template and
workbooks derived from it on a "just in case" basis.

Is there a better way around this? I do not have any way of creating COM
add-ins

TIA!