Sheets added by VBA invalidates digitally signed VBA project

version: Excel 2003 Pro.

Changing a VBA project invalidates the digital signature if it's signed.
How can VBA code add/remove sheets without invalidating the signature?
There's another thread in this group ("Digital signature being dropped")
dated 1/6/2005 about the same problem, but with no reply.

The only way that works is an Excel add-in. I don't know why that doesn't
invalidate the signature. I don't want to use an add-in because my code is
specific to this 1 particular workbook, not *every* Excel workbook.

Keep in mind, to test this you have to try it on a computer that doesn't
have the signature's private key, since saving the workbook on the computer
with the private key simply re-signs the VBA project when it becomes invalid.

Please help. Thanks.

Dean

I've had that same problem but it wasn't related to adding or removing
sheets. Actually, I never did determine what the real cause was, but it
happend to a lot of similar workbooks at the same time.

By putting your code in add-in, you lose some of the portability of the
workbook, i.e., you have to distribute two workbooks instead of one. But
you gain in that you elimate this problem. So that's the trade off, and I
decided, for me, to put the code in an add-in even if it only related to one
workbook.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Dean J. wrote:
> version: Excel 2003 Pro.
>
> Changing a VBA project invalidates the digital signature if it's
> signed.
> How can VBA code add/remove sheets without invalidating the signature?
> There's another thread in this group ("Digital signature being
> dropped") dated 1/6/2005 about the same problem, but with no reply.
>
> The only way that works is an Excel add-in. I don't know why that
> doesn't invalidate the signature. I don't want to use an add-in
> because my code is specific to this 1 particular workbook, not
> *every* Excel workbook.
>
> Keep in mind, to test this you have to try it on a computer that
> doesn't have the signature's private key, since saving the workbook
> on the computer with the private key simply re-signs the VBA project
> when it becomes invalid.
>
> Please help. Thanks.

Thanks ****,

My workbook has a custom menu I had at the top before the "Help" menu.
Wouldn't an add-in put this menu up there for *every* workbook opened? I
guess I could load/un-load the add-in every time the workbook is
opened/closed, but I've wondered if doing that so often would corrupt the
registry or something... since I don't know what MS does when an add-in is
loaded/un-loaded. I've been researching to see if it's possible to associate
an add-in with a workbook, not with all of Excel, but can't find any info.

Thanks again,
Dean

"**** Kusleika" wrote:

> Dean
>
> I've had that same problem but it wasn't related to adding or removing
> sheets. Actually, I never did determine what the real cause was, but it
> happend to a lot of similar workbooks at the same time.
>
> By putting your code in add-in, you lose some of the portability of the
> workbook, i.e., you have to distribute two workbooks instead of one. But
> you gain in that you elimate this problem. So that's the trade off, and I
> decided, for me, to put the code in an add-in even if it only related to one
> workbook.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Dean J. wrote:
> > version: Excel 2003 Pro.
> >
> > Changing a VBA project invalidates the digital signature if it's
> > signed.
> > How can VBA code add/remove sheets without invalidating the signature?
> > There's another thread in this group ("Digital signature being
> > dropped") dated 1/6/2005 about the same problem, but with no reply.
> >
> > The only way that works is an Excel add-in. I don't know why that
> > doesn't invalidate the signature. I don't want to use an add-in
> > because my code is specific to this 1 particular workbook, not
> > *every* Excel workbook.
> >
> > Keep in mind, to test this you have to try it on a computer that
> > doesn't have the signature's private key, since saving the workbook
> > on the computer with the private key simply re-signs the VBA project
> > when it becomes invalid.
> >
> > Please help. Thanks.
>
>
>

Dean

Use a custom property in the user workbook. Have your add-in check for this
property using an Application_WorkbookOpen event and if it exists, add the
menu item. Then use the Application_BeforeClose (from the add-in) to delete
the menu when the workbook is closed. The add-in will be running all the
time, but it will only show it's menu in the proper context.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Dean J. wrote:
> Thanks ****,
>
> My workbook has a custom menu I had at the top before the "Help" menu.
> Wouldn't an add-in put this menu up there for *every* workbook
> opened? I guess I could load/un-load the add-in every time the
> workbook is opened/closed, but I've wondered if doing that so often
> would corrupt the registry or something... since I don't know what MS
> does when an add-in is loaded/un-loaded. I've been researching to
> see if it's possible to associate an add-in with a workbook, not with
> all of Excel, but can't find any info.
>
> Thanks again,
> Dean
>
> "**** Kusleika" wrote:
>
>> Dean
>>
>> I've had that same problem but it wasn't related to adding or
>> removing sheets. Actually, I never did determine what the real
>> cause was, but it happend to a lot of similar workbooks at the same
>> time.
>>
>> By putting your code in add-in, you lose some of the portability of
>> the workbook, i.e., you have to distribute two workbooks instead of
>> one. But you gain in that you elimate this problem. So that's the
>> trade off, and I decided, for me, to put the code in an add-in even
>> if it only related to one workbook.
>>
>> --
>> **** Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.*****-blog.com
>>
>> Dean J. wrote:
>>> version: Excel 2003 Pro.
>>>
>>> Changing a VBA project invalidates the digital signature if it's
>>> signed.
>>> How can VBA code add/remove sheets without invalidating the
>>> signature? There's another thread in this group ("Digital signature
>>> being dropped") dated 1/6/2005 about the same problem, but with no
>>> reply.
>>>
>>> The only way that works is an Excel add-in. I don't know why that
>>> doesn't invalidate the signature. I don't want to use an add-in
>>> because my code is specific to this 1 particular workbook, not
>>> *every* Excel workbook.
>>>
>>> Keep in mind, to test this you have to try it on a computer that
>>> doesn't have the signature's private key, since saving the workbook
>>> on the computer with the private key simply re-signs the VBA project
>>> when it becomes invalid.
>>>
>>> Please help. Thanks.

****

That would definitely do the trick. I went ahead and put in a MSDN support
ticket, since I feel this is a bug (if it is they usually won't use 1 of your
incidents) Trusted and Signed macro code should be able to add/remove
sheets. I sent the sample workbook that reproduces the issue to the support
guy a few minutes ago. Thanks for your response. I'll post Microsoft's
response here as soon as I hear back.

-Dean

"**** Kusleika" wrote:

> Dean
>
> Use a custom property in the user workbook. Have your add-in check for this
> property using an Application_WorkbookOpen event and if it exists, add the
> menu item. Then use the Application_BeforeClose (from the add-in) to delete
> the menu when the workbook is closed. The add-in will be running all the
> time, but it will only show it's menu in the proper context.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Dean J. wrote:
> > Thanks ****,
> >
> > My workbook has a custom menu I had at the top before the "Help" menu.
> > Wouldn't an add-in put this menu up there for *every* workbook
> > opened? I guess I could load/un-load the add-in every time the
> > workbook is opened/closed, but I've wondered if doing that so often
> > would corrupt the registry or something... since I don't know what MS
> > does when an add-in is loaded/un-loaded. I've been researching to
> > see if it's possible to associate an add-in with a workbook, not with
> > all of Excel, but can't find any info.
> >
> > Thanks again,
> > Dean
> >
> > "**** Kusleika" wrote:
> >
> >> Dean
> >>
> >> I've had that same problem but it wasn't related to adding or
> >> removing sheets. Actually, I never did determine what the real
> >> cause was, but it happend to a lot of similar workbooks at the same
> >> time.
> >>
> >> By putting your code in add-in, you lose some of the portability of
> >> the workbook, i.e., you have to distribute two workbooks instead of
> >> one. But you gain in that you elimate this problem. So that's the
> >> trade off, and I decided, for me, to put the code in an add-in even
> >> if it only related to one workbook.
> >>
> >> --
> >> **** Kusleika
> >> Excel MVP
> >> Daily Dose of Excel
> >> www.*****-blog.com
> >>
> >> Dean J. wrote:
> >>> version: Excel 2003 Pro.
> >>>
> >>> Changing a VBA project invalidates the digital signature if it's
> >>> signed.
> >>> How can VBA code add/remove sheets without invalidating the
> >>> signature? There's another thread in this group ("Digital signature
> >>> being dropped") dated 1/6/2005 about the same problem, but with no
> >>> reply.
> >>>
> >>> The only way that works is an Excel add-in. I don't know why that
> >>> doesn't invalidate the signature. I don't want to use an add-in
> >>> because my code is specific to this 1 particular workbook, not
> >>> *every* Excel workbook.
> >>>
> >>> Keep in mind, to test this you have to try it on a computer that
> >>> doesn't have the signature's private key, since saving the workbook
> >>> on the computer with the private key simply re-signs the VBA project
> >>> when it becomes invalid.
> >>>
> >>> Please help. Thanks.
>
>
>

Hi Dean,

For some additional, recent discussion about this issue, check the
following URL:

http://groups-beta.google.com/group/...ada9ab7856b54c

Best Regards,
Walt

**** Kusleika wrote:
> Dean
>
> I've had that same problem but it wasn't related to adding or
removing
> sheets. Actually, I never did determine what the real cause was, but
it
> happend to a lot of similar workbooks at the same time.
>
> By putting your code in add-in, you lose some of the portability of
the
> workbook, i.e., you have to distribute two workbooks instead of one.
But
> you gain in that you elimate this problem. So that's the trade off,
and I
> decided, for me, to put the code in an add-in even if it only related
to one
> workbook.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Dean J. wrote:
> > version: Excel 2003 Pro.
> >
> > Changing a VBA project invalidates the digital signature if it's
> > signed.
> > How can VBA code add/remove sheets without invalidating the
signature?
> > There's another thread in this group ("Digital signature being
> > dropped") dated 1/6/2005 about the same problem, but with no reply.
> >
> > The only way that works is an Excel add-in. I don't know why that
> > doesn't invalidate the signature. I don't want to use an add-in
> > because my code is specific to this 1 particular workbook, not
> > *every* Excel workbook.
> >
> > Keep in mind, to test this you have to try it on a computer that
> > doesn't have the signature's private key, since saving the workbook
> > on the computer with the private key simply re-signs the VBA
project
> > when it becomes invalid.
> >
> > Please help. Thanks.

Thanks Walt,

I had actually found this thread when researching earlier, but I decided to
call MS anyway. I'm hoping that since I've verified that none of their
documentation mentions the requirement of the code being an add-in, then maybe
they'll realize they need to better document it or fix this with a service
pack...yes I know...good luck. They're supposed to respond to me by Tuesday,
so I'll post their response. I did try moving the code to a VB6 DLL, with
the workbook having a tiny bit of code that calls the DLL, but it was the
same problem. I'm thinking that the reason the add-works is that it's
*totally* separate from the workbook, whereas my DLL is still linked in by a
project reference. You'd think this would be the other way around, and that
an outside program would invalidate the certificate, not code *inside* the
workbook.

Dean

"Walt" wrote:

> Hi Dean,
>
> For some additional, recent discussion about this issue, check the
> following URL:
>
> http://groups-beta.google.com/group/...ada9ab7856b54c
>
> Best Regards,
> Walt
>
> **** Kusleika wrote:
> > Dean
> >
> > I've had that same problem but it wasn't related to adding or
> removing
> > sheets. Actually, I never did determine what the real cause was, but
> it
> > happend to a lot of similar workbooks at the same time.
> >
> > By putting your code in add-in, you lose some of the portability of
> the
> > workbook, i.e., you have to distribute two workbooks instead of one.
> But
> > you gain in that you elimate this problem. So that's the trade off,
> and I
> > decided, for me, to put the code in an add-in even if it only related
> to one
> > workbook.
> >
> > --
> > **** Kusleika
> > Excel MVP
> > Daily Dose of Excel
> > www.*****-blog.com
> >
> > Dean J. wrote:
> > > version: Excel 2003 Pro.
> > >
> > > Changing a VBA project invalidates the digital signature if it's
> > > signed.
> > > How can VBA code add/remove sheets without invalidating the
> signature?
> > > There's another thread in this group ("Digital signature being
> > > dropped") dated 1/6/2005 about the same problem, but with no reply.
> > >
> > > The only way that works is an Excel add-in. I don't know why that
> > > doesn't invalidate the signature. I don't want to use an add-in
> > > because my code is specific to this 1 particular workbook, not
> > > *every* Excel workbook.
> > >
> > > Keep in mind, to test this you have to try it on a computer that
> > > doesn't have the signature's private key, since saving the workbook
> > > on the computer with the private key simply re-signs the VBA
> project
> > > when it becomes invalid.
> > >
> > > Please help. Thanks.
>
>

"Dean J." <[email protected]> wrote in message
news:[email protected]...
> I'm thinking that the reason the add-works is that it's
> *totally* separate from the workbook, whereas my DLL is still linked in by
a
> project reference.

You could test that by referencing the add-in in the project.

I added a reference to the Add-In and created a wrapper procedure that called
the Add-In to delete a sheet. I tried the workbook on the client PC (without
the private key certificate) and it worked fine...meaning it deleted the
sheet and saved the workbook without invalidating the certificate (public
key).

Actually, now that I think about it, the point of a macro virus is to be
attached to the workbook and do it's harm when the workbook opens. So it
would make sense that an add-in (something you usually have to install
yourself) wouldn't fall into the macro virus category, and thus Excel would
allow it to change the VBA project (what happens when adding/deleting a
sheet) without invalidating the certificate.

My personal opinion is that if you have imported the publisher's public
certificate and selected the "Trust all macros from this publisher" checkbox
when opening the workbook, then macros that change the VBA project should be
OK and not require the re-signing of the project. But I guess when the
programmer signs the VBA project, it uses the current object list (number of
sheets, etc.) in the hash, and once that changes it must be re-signed, which
can't be done on the client because they don't have the private key
certificate. Essentially a catch-22.

Dean

"Bob Phillips" wrote:

>
>
> "Dean J." <[email protected]> wrote in message
> news:[email protected]...
> > I'm thinking that the reason the add-works is that it's
> > *totally* separate from the workbook, whereas my DLL is still linked in by
> a
> > project reference.
>
> You could test that by referencing the add-in in the project.
>
>
>

Hi Dean,

Are you opening the file via VBA code in the add-in (This should take
advantage of msoAutomationSecurityLow)? Otherwise, the result you describe
in your 1st paragraph seems inconsistent with all else I've learned.

Can you confirm that the signature is still valid in the modified file with
the sheet deleted (Maybe remove the add-in, then open the modified file and
expect the error when the add-in is not found by the functioning VBA code)?

Best Regards,
Walt

"Dean J." wrote:

> I added a reference to the Add-In and created a wrapper procedure that called
> the Add-In to delete a sheet. I tried the workbook on the client PC (without
> the private key certificate) and it worked fine...meaning it deleted the
> sheet and saved the workbook without invalidating the certificate (public
> key).
>
> Actually, now that I think about it, the point of a macro virus is to be
> attached to the workbook and do it's harm when the workbook opens. So it
> would make sense that an add-in (something you usually have to install
> yourself) wouldn't fall into the macro virus category, and thus Excel would
> allow it to change the VBA project (what happens when adding/deleting a
> sheet) without invalidating the certificate.
>
> My personal opinion is that if you have imported the publisher's public
> certificate and selected the "Trust all macros from this publisher" checkbox
> when opening the workbook, then macros that change the VBA project should be
> OK and not require the re-signing of the project. But I guess when the
> programmer signs the VBA project, it uses the current object list (number of
> sheets, etc.) in the hash, and once that changes it must be re-signed, which
> can't be done on the client because they don't have the private key
> certificate. Essentially a catch-22.
>
> Dean
>
> "Bob Phillips" wrote:
>
> >
> >
> > "Dean J." <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm thinking that the reason the add-works is that it's
> > > *totally* separate from the workbook, whereas my DLL is still linked in by
> > a
> > > project reference.
> >
> > You could test that by referencing the add-in in the project.
> >
> >
> >