Hi All.........

I must provide a worksheet to a Customer for their perusal. There are
several Macros involved that I do not want them to see. What is the code
please for me to write a "Sanitize" macro that will delete all macros from
the modules, workbook, etc that the Customer need not see ? I tried
recording it, to delete individual macros by name, but that part does not
show up........

TIA
Chuck, CABGx3

http://www.cpearson.com/excel/vbe.htm

but why not protect the code in the VBE?

If you do, then you won't be able to delete it, but that form of protection
is fairly robust for the average user.

----------
I guess the scenario is not clear. You are giving the book complete to the
user, then the code will delete code after a demo?

--
Regards,
Tom Ogilvy

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There are
several Macros involved that I do not want them to see. What is the code
please for me to write a "Sanitize" macro that will delete all macros from
the modules, workbook, etc that the Customer need not see ? I tried
recording it, to delete individual macros by name, but that part does not
show up........

TIA
Chuck, CABGx3

Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There are
several Macros involved that I do not want them to see. What is the code
please for me to write a "Sanitize" macro that will delete all macros from
the modules, workbook, etc that the Customer need not see ? I tried
recording it, to delete individual macros by name, but that part does not
show up........

TIA
Chuck, CABGx3

Thanks Bob.........

A lot more there than I had sense enough to ask for, but from a cursory
look, it should solve my problem.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote in message
...
Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There are
several Macros involved that I do not want them to see. What is the
code
please for me to write a "Sanitize" macro that will delete all macros
from
the modules, workbook, etc that the Customer need not see ? I tried
recording it, to delete individual macros by name, but that part does
not
show up........

TIA
Chuck, CABGx3

it should solve my problem.

For your consideration (posted by Stephen Bullen)

Generally when automating the IDE, it's not a good idea to modify your own
project. What are you trying to achieve?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Bob.........

A lot more there than I had sense enough to ask for, but from a cursory
look, it should solve my problem.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote in message
...
Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There are
several Macros involved that I do not want them to see. What is the
code
please for me to write a "Sanitize" macro that will delete all macros
from
the modules, workbook, etc that the Customer need not see ? I tried
recording it, to delete individual macros by name, but that part does
not
show up........

TIA
Chuck, CABGx3

Hmmmmm, methinks I've bit off more than I can chew with this
one...........time for Plan "B" I guess..........."sometimes the Dragon
wins" <G

Thanks guys........

Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
it should solve my problem.

For your consideration (posted by Stephen Bullen)

Generally when automating the IDE, it's not a good idea to modify your own
project. What are you trying to achieve?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Bob.........

A lot more there than I had sense enough to ask for, but from a cursory
look, it should solve my problem.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote in message
...
Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There
are
several Macros involved that I do not want them to see. What is the
code
please for me to write a "Sanitize" macro that will delete all
macros
from
the modules, workbook, etc that the Customer need not see ? I
tried
recording it, to delete individual macros by name, but that part
does
not
show up........

TIA
Chuck, CABGx3

Chuck,

Are you saying that you can't get Chip's code to work? If so, give us the
details and we'll help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hmmmmm, methinks I've bit off more than I can chew with this
one...........time for Plan "B" I guess..........."sometimes the Dragon
wins" <G

Thanks guys........

Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
it should solve my problem.

For your consideration (posted by Stephen Bullen)

Generally when automating the IDE, it's not a good idea to modify your
own
project. What are you trying to achieve?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Bob.........

A lot more there than I had sense enough to ask for, but from a
cursory
look, it should solve my problem.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote in message
...
Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal. There
are
several Macros involved that I do not want them to see. What is
the
code
please for me to write a "Sanitize" macro that will delete all
macros
from
the modules, workbook, etc that the Customer need not see ? I
tried
recording it, to delete individual macros by name, but that part
does
not
show up........

TIA
Chuck, CABGx3

Hi Bob.........

Well, that's not it exactly..........I'm very new at VBA and I'm also very
loaded down at work with many tasks, and I have a co-worker who helps me
occasionally, a young fellow that is very bright and catches on very quickly
and has blown right by me in our VBA development......... well, I asked him
to take on this task and he's been working with Chips code most of the day
(it was all over my head), and he sorta kinda had it about together and all
of a sudden seemed to develop some kind of a virus. It wouldn't even let
him run Norton..........and since he doesn't have Internet access, we feel
it might have come in on the email that I sent over to him about Chips
page........at any rate, that ended the day at work, and he was still trying
to get his computer to respond..........

This method does not have to be the solution to my problem, I am only
interested in being able to send the Customer the WorkOrder, stripped of
specific cell data, extra sheets, and as much code as possible.......just
the bare minimum info they need............and do it by macro control. But
there may be other/easier ways of getting there than this one........it was
just the first thing we thought of and tried.

I do appreciate you guys time and thoughts and sure didn't want to make a
pest of myself with this one problem.

Thanks again........

Vaya con Dios,
Chuck, CABGx3




"Bob Phillips" wrote in message
...
Chuck,

Are you saying that you can't get Chip's code to work? If so, give us the
details and we'll help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hmmmmm, methinks I've bit off more than I can chew with this
one...........time for Plan "B" I guess..........."sometimes the Dragon
wins" <G

Thanks guys........

Vaya con Dios,
Chuck, CABGx3




"Tom Ogilvy" wrote in message
...
it should solve my problem.

For your consideration (posted by Stephen Bullen)

Generally when automating the IDE, it's not a good idea to modify your
own
project. What are you trying to achieve?

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
Thanks Bob.........

A lot more there than I had sense enough to ask for, but from a
cursory
look, it should solve my problem.

Vaya con Dios,
Chuck, CABGx3



"Bob Phillips" wrote in message
...
Take a look at Chip's site http://www.cpearson.com/excel/vbe.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Hi All.........

I must provide a worksheet to a Customer for their perusal.
There
are
several Macros involved that I do not want them to see. What is
the
code
please for me to write a "Sanitize" macro that will delete all
macros
from
the modules, workbook, etc that the Customer need not see ? I
tried
recording it, to delete individual macros by name, but that
part
does
not
show up........

TIA
Chuck, CABGx3

Instead of stripping out what you don't want to send why not try copyin
what you do want to send into a new workbook. Which will probally b
easier than deleting sheets etc

--
Message posted from http://www.ExcelForum.com

Thats the line we're starting to think along now.......maybe a master EMAIL
file that contains the emailing macro and it will go and fetch the values we
want to send from the file we specify and then send it, without any of the
macros from the fetched file being sent. I'ts ok if we send only the
Fetching and Emailing macros, they are of no consequence..........

Thanks for your thoughts..........

Vaya con Dios,
Chuck, CABGx3





"mudraker " wrote in message
...
Instead of stripping out what you don't want to send why not try copying
what you do want to send into a new workbook. Which will probally be
easier than deleting sheets etc.


---
Message posted from http://www.ExcelForum.com/

Personally I would have the macro in the workbook that has all you
other macro's.

Build the macro to create a brand new workbook, copy the required dat
into the new workbook then send the new workbook by email.

This way no macros are sent and the receiver does not have to worr
about the validity of any macro's

--
Message posted from http://www.ExcelForum.com

How about having the code you want to keep in an addin, and distribute that?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Thats the line we're starting to think along now.......maybe a master
EMAIL
file that contains the emailing macro and it will go and fetch the values
we
want to send from the file we specify and then send it, without any of the
macros from the fetched file being sent. I'ts ok if we send only the
Fetching and Emailing macros, they are of no consequence..........

Thanks for your thoughts..........

Vaya con Dios,
Chuck, CABGx3





"mudraker " wrote in message
...
Instead of stripping out what you don't want to send why not try copying
what you do want to send into a new workbook. Which will probally be
easier than deleting sheets etc.


---
Message posted from http://www.ExcelForum.com/

Yes, thats the ticket.........essentially we were at that point, but unable
to figure out how to make the "new" workbook send out by email from a macro
in the original workbook.........any help there would be appreciated.......

Vaya con Dios,
Chuck, CABGx3







"mudraker " wrote in message
...
Personally I would have the macro in the workbook that has all your
other macro's.

Build the macro to create a brand new workbook, copy the required data
into the new workbook then send the new workbook by email.

This way no macros are sent and the receiver does not have to worry
about the validity of any macro's.


---
Message posted from http://www.ExcelForum.com/

Thanks for your continuing concern Bob, and for this thought, but I think in
this case that would not be what I want to do. The WorkBook is actually a
WorkOrder form that our Salesmen use to create new WorkOrders. The form
contains many formulas which act as "checks and balances" ........Problem
is, the salesmen like to download the file off the network to their local
computers and use those old versions instead of getting a new one each
time........they not only don't have the latest version that way, they also
have been known to modify their copy to serve other purposes, (if you kow
what I mean).........so, in this case I don't really want to "distribute"
anything, I want them to have to go to the network each time and get a new
copy.......I spend much time trying to "protect" the workbook and make it so
that I can identify which "version" they actually use.......

Vaya con Dios,
Chuck, CABGx3





"Bob Phillips" wrote in message
...
How about having the code you want to keep in an addin, and distribute
that?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"CLR" wrote in message
...
Thats the line we're starting to think along now.......maybe a master
EMAIL
file that contains the emailing macro and it will go and fetch the
values
we
want to send from the file we specify and then send it, without any of
the
macros from the fetched file being sent. I'ts ok if we send only the
Fetching and Emailing macros, they are of no consequence..........

Thanks for your thoughts..........

Vaya con Dios,
Chuck, CABGx3





"mudraker " wrote in message
...
Instead of stripping out what you don't want to send why not try
copying
what you do want to send into a new workbook. Which will probally be
easier than deleting sheets etc.


---
Message posted from http://www.ExcelForum.com/

' creates new workbook with sheet1, 6 and 10
worksheets(Array("Sheet1","Sheet6","Sheet10")).cop y
' perform any adjustments - for example, on sheet1
Activeworkbook.Worksheets(1).Range("B9,C11,D15,Z21 ").Clearcontents
ActiveWorkbook.SendMail, , "Report for Today"
Activeworkbook.close SaveChanges:=False

--
Regards,
Tom Ogilvy




"CLR" wrote in message
...
Yes, thats the ticket.........essentially we were at that point, but
unable
to figure out how to make the "new" workbook send out by email from a
macro
in the original workbook.........any help there would be
appreciated.......

Vaya con Dios,
Chuck, CABGx3







"mudraker " wrote in message
...
Personally I would have the macro in the workbook that has all your
other macro's.

Build the macro to create a brand new workbook, copy the required data
into the new workbook then send the new workbook by email.

This way no macros are sent and the receiver does not have to worry
about the validity of any macro's.


---
Message posted from http://www.ExcelForum.com/

Ok Tom,.......... we did finally get it working, and with all our bells and
whistles too........thanks to your post and the others too.........

You guys are the greatest...........
Thank you all sincerely.........

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote in message
...
' creates new workbook with sheet1, 6 and 10
worksheets(Array("Sheet1","Sheet6","Sheet10")).cop y
' perform any adjustments - for example, on sheet1
Activeworkbook.Worksheets(1).Range("B9,C11,D15,Z21 ").Clearcontents
ActiveWorkbook.SendMail, , "Report for Today"
Activeworkbook.close SaveChanges:=False

--
Regards,
Tom Ogilvy




"CLR" wrote in message
...
Yes, thats the ticket.........essentially we were at that point, but
unable
to figure out how to make the "new" workbook send out by email from a
macro
in the original workbook.........any help there would be
appreciated.......

Vaya con Dios,
Chuck, CABGx3







"mudraker " wrote in message
...
Personally I would have the macro in the workbook that has all your
other macro's.

Build the macro to create a brand new workbook, copy the required data
into the new workbook then send the new workbook by email.

This way no macros are sent and the receiver does not have to worry
about the validity of any macro's.


---
Message posted from http://www.ExcelForum.com/