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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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 Dick,

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

"Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dean J. wrote:
Thanks Dick,

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

"Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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.

Dick

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

"Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Dean J. wrote:
Thanks Dick,

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

"Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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

Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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

Dick 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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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.