I password protected my macros so no one can edit them but, occasionally, I
want to send the file somewhere and I don't want them to be able to even run
the macros, particularly one that takes off all the password protection!

What is the easiest way to ensure they won't be able to run macros, short of
deleting all of them?

Thanks!
Dean

Is it possible to save the worksheets to a different workbook that does not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but, occasionally, I
want to send the file somewhere and I don't want them to be able to even run
the macros, particularly one that takes off all the password protection!

What is the easiest way to ensure they won't be able to run macros, short of
deleting all of them?

Thanks!
Dean

That sounds too risky. If there is no other option, I suppose I could just
manually delete all the macros.

"MSweetG222" wrote in message
...
Is it possible to save the worksheets to a different workbook that does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but, occasionally,
I
want to send the file somewhere and I don't want them to be able to even
run
the macros, particularly one that takes off all the password protection!

What is the easiest way to ensure they won't be able to run macros, short
of
deleting all of them?

Thanks!
Dean

At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that does not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but, occasionally, I
want to send the file somewhere and I don't want them to be able to even run
the macros, particularly one that takes off all the password protection!

What is the easiest way to ensure they won't be able to run macros, short of
deleting all of them?

Thanks!
Dean

By the way, I assume there is a way to require a password to run each macro
somehow, but it would be better if they could simply be disabled only if and
when I choose to do so, say just before I mail it to someone. I'd rather
not have to supply a password every time that I run them.

Thanks!
Dean

"Dean" wrote in message
...
I password protected my macros so no one can edit them but, occasionally, I
want to send the file somewhere and I don't want them to be able to even
run the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run macros, short
of deleting all of them?

Thanks!
Dean

I also have clickable macro buttons. I suppose I would need to delete them,
right?


"Jim Thomlinson" wrote in message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run macros,
short of
deleting all of them?

Thanks!
Dean

Depends on how critical the data that you are sending out is. If this is very
confidential then you are going to need to rethink the solution as no matter
what I give you it can be defeated. Code and Excel are both inherently easy
to defeat for anyone with a strong enough desire... In that case you will
need to exprt out the data from this spreadsheet into a new workbook that
contains only what you want the end user to see. Otherwise if it is just a
matter of being protected for convenience then there are a couple of routes
taht we could follow including a password on open that unhides the buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to delete them,
right?


"Jim Thomlinson" wrote in message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run macros,
short of
deleting all of them?

Thanks!
Dean

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection, just
enough to deter the non-expert. Is it one of those things by holding down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in message
...
Depends on how critical the data that you are sending out is. If this is
very
confidential then you are going to need to rethink the solution as no
matter
what I give you it can be defeated. Code and Excel are both inherently
easy
to defeat for anyone with a strong enough desire... In that case you will
need to exprt out the data from this spreadsheet into a new workbook that
contains only what you want the end user to see. Otherwise if it is just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run macros,
short of
deleting all of them?

Thanks!
Dean

Since it does not sound mission critical I would write two snipets of code.
One to hide all of the buttons and the other to unhide the buttons. Place a
reference to the hide procedure in the before close event. This will ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called form
the on open event in Thisworkbook. Check Environ("UserName") (windows login
name) against a pre-populated list that is in a very hidden sheet in the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you want the
buttons to be shown. The file is just a flag. Put the file somewhere in a
directory that you create. You can then write some very easy code to verify
if the file exists. If it does then unhide the buttons. You can create a
batch file to create the directory and the empty text file if you want to get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection, just
enough to deter the non-expert. Is it one of those things by holding down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in message
...
Depends on how critical the data that you are sending out is. If this is
very
confidential then you are going to need to rethink the solution as no
matter
what I give you it can be defeated. Code and Excel are both inherently
easy
to defeat for anyone with a strong enough desire... In that case you will
need to exprt out the data from this spreadsheet into a new workbook that
contains only what you want the end user to see. Otherwise if it is just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run macros,
short of
deleting all of them?

Thanks!
Dean

I can't see my client being willing to deal with all this for unhiding a
macro - they have different users and different locations, plus they're not
tech savvy. In the very few occasions that they need to be able to hide
buttons because they are sending the file out to one of their clients, I
think they would be happier to send their client a lesser file, one with the
macro buttons manually deleted, or with them hidden. Can you tell me how to
hide buttons on all worksheets, say worksheets named, Joe, Jane, and Jill,
or a macro to delete all buttons? (At worst, they can delete them manually,
I suppose). Then they could just click this macro button, which could stay
there, and resave the file under a different name to send to their client,
maintaining the non-clicked version for themselves, for future iterations.
By the way, rookie that I am, I create the buttons using the drawing
toolbar - actually, now, I just copy a button from one I did before, and
edit. Is that a bad way to do them? I guess the forms button is quicker.
Anyway, for now, please use a macro that deals with drawing boxes, if it's
not too difficult.

Anyway, if they run such a macro, and if I've already put all the macros in
the private area, that should do it, right? I assume that if they are
private that, if the button is deleted or hidden, there is no way to access
the macro? Is that right? Or is there still a reasonably easy, non-expert,
way?

Dean

"Jim Thomlinson" wrote in message
...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons. Place
a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you want
the
buttons to be shown. The file is just a flag. Put the file somewhere in a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can create a
batch file to create the directory and the empty text file if you want to
get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection, just
enough to deter the non-expert. Is it one of those things by holding
down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons
would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If this
is
very
confidential then you are going to need to rethink the solution as no
matter
what I give you it can be defeated. Code and Excel are both inherently
easy
to defeat for anyone with a strong enough desire... In that case you
will
need to exprt out the data from this spreadsheet into a new workbook
that
contains only what you want the end user to see. Otherwise if it is
just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able
to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean

If all you have is buttons, you might try:

Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next


--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I can't see my client being willing to deal with all this for unhiding a
macro - they have different users and different locations, plus they're not
tech savvy. In the very few occasions that they need to be able to hide
buttons because they are sending the file out to one of their clients, I
think they would be happier to send their client a lesser file, one with
the macro buttons manually deleted, or with them hidden. Can you tell me
how to hide buttons on all worksheets, say worksheets named, Joe, Jane, and
Jill, or a macro to delete all buttons? (At worst, they can delete them
manually, I suppose). Then they could just click this macro button, which
could stay there, and resave the file under a different name to send to
their client, maintaining the non-clicked version for themselves, for
future iterations. By the way, rookie that I am, I create the buttons using
the drawing toolbar - actually, now, I just copy a button from one I did
before, and edit. Is that a bad way to do them? I guess the forms button
is quicker. Anyway, for now, please use a macro that deals with drawing
boxes, if it's not too difficult.

Anyway, if they run such a macro, and if I've already put all the macros
in the private area, that should do it, right? I assume that if they are
private that, if the button is deleted or hidden, there is no way to
access the macro? Is that right? Or is there still a reasonably easy,
non-expert, way?

Dean

"Jim Thomlinson" wrote in
message ...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons. Place
a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called
form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you want
the
buttons to be shown. The file is just a flag. Put the file somewhere in a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can create a
batch file to create the directory and the empty text file if you want to
get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection, just
enough to deter the non-expert. Is it one of those things by holding
down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons
would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If this
is
very
confidential then you are going to need to rethink the solution as no
matter
what I give you it can be defeated. Code and Excel are both inherently
easy
to defeat for anyone with a strong enough desire... In that case you
will
need to exprt out the data from this spreadsheet into a new workbook
that
contains only what you want the end user to see. Otherwise if it is
just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to
delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the
module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be able
to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean

Wow, that was amazing! Only one problem - I've tried it thrice, including
closing out EXCEL and re-opening, and, each time, after I start navigating
just after the macro, EXCEL crashes. It does recover the file but who would
want that to be the normal course of business.

Would it matter that I am initiating the macro via one of the buttons that
the macro then erases? Also, all my subs, including this one, are private.

D


"Tom Ogilvy" wrote in message
...
If all you have is buttons, you might try:

Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next


--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I can't see my client being willing to deal with all this for unhiding a
macro - they have different users and different locations, plus they're
not tech savvy. In the very few occasions that they need to be able to
hide buttons because they are sending the file out to one of their
clients, I think they would be happier to send their client a lesser file,
one with the macro buttons manually deleted, or with them hidden. Can you
tell me how to hide buttons on all worksheets, say worksheets named, Joe,
Jane, and Jill, or a macro to delete all buttons? (At worst, they can
delete them manually, I suppose). Then they could just click this macro
button, which could stay there, and resave the file under a different name
to send to their client, maintaining the non-clicked version for
themselves, for future iterations. By the way, rookie that I am, I create
the buttons using the drawing toolbar - actually, now, I just copy a
button from one I did before, and edit. Is that a bad way to do them? I
guess the forms button is quicker. Anyway, for now, please use a macro
that deals with drawing boxes, if it's not too difficult.

Anyway, if they run such a macro, and if I've already put all the macros
in the private area, that should do it, right? I assume that if they are
private that, if the button is deleted or hidden, there is no way to
access the macro? Is that right? Or is there still a reasonably easy,
non-expert, way?

Dean

"Jim Thomlinson" wrote in
message ...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons.
Place a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called
form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you want
the
buttons to be shown. The file is just a flag. Put the file somewhere in
a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can create a
batch file to create the directory and the empty text file if you want
to get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone
else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection,
just
enough to deter the non-expert. Is it one of those things by holding
down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons
would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If this
is
very
confidential then you are going to need to rethink the solution as no
matter
what I give you it can be defeated. Code and Excel are both
inherently
easy
to defeat for anyone with a strong enough desire... In that case you
will
need to exprt out the data from this spreadsheet into a new workbook
that
contains only what you want the end user to see. Otherwise if it is
just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to
delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the
module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook
that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be
able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean

If you are running from a button that you delete, then it could be a
problem.

Perhaps

Private Sub CommandButton1_Click()
Application.Ontime now, "DeleteButtons"
End Sub

in a general Module

Sub Deletebuttons()
Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next
End Sub


--
Regards,
Tom Ogilvy

"Dean" wrote in message
...
Wow, that was amazing! Only one problem - I've tried it thrice, including
closing out EXCEL and re-opening, and, each time, after I start navigating
just after the macro, EXCEL crashes. It does recover the file but who
would want that to be the normal course of business.

Would it matter that I am initiating the macro via one of the buttons that
the macro then erases? Also, all my subs, including this one, are
private.

D


"Tom Ogilvy" wrote in message
...
If all you have is buttons, you might try:

Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next


--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I can't see my client being willing to deal with all this for unhiding a
macro - they have different users and different locations, plus they're
not tech savvy. In the very few occasions that they need to be able to
hide buttons because they are sending the file out to one of their
clients, I think they would be happier to send their client a lesser
file, one with the macro buttons manually deleted, or with them hidden.
Can you tell me how to hide buttons on all worksheets, say worksheets
named, Joe, Jane, and Jill, or a macro to delete all buttons? (At worst,
they can delete them manually, I suppose). Then they could just click
this macro button, which could stay there, and resave the file under a
different name to send to their client, maintaining the non-clicked
version for themselves, for future iterations. By the way, rookie that I
am, I create the buttons using the drawing toolbar - actually, now, I
just copy a button from one I did before, and edit. Is that a bad way to
do them? I guess the forms button is quicker. Anyway, for now, please
use a macro that deals with drawing boxes, if it's not too difficult.

Anyway, if they run such a macro, and if I've already put all the macros
in the private area, that should do it, right? I assume that if they
are private that, if the button is deleted or hidden, there is no way to
access the macro? Is that right? Or is there still a reasonably easy,
non-expert, way?

Dean

"Jim Thomlinson" wrote in
message ...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons.
Place a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called
form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in
the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you
want the
buttons to be shown. The file is just a flag. Put the file somewhere in
a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can create
a
batch file to create the directory and the empty text file if you want
to get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone
else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection,
just
enough to deter the non-expert. Is it one of those things by holding
down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro buttons
would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If
this is
very
confidential then you are going to need to rethink the solution as
no
matter
what I give you it can be defeated. Code and Excel are both
inherently
easy
to defeat for anyone with a strong enough desire... In that case you
will
need to exprt out the data from this spreadsheet into a new workbook
that
contains only what you want the end user to see. Otherwise if it is
just a
matter of being protected for convenience then there are a couple of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to
delete
them,
right?


"Jim Thomlinson" wrote in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the
module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook
that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be
able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean

It seems like you're suggesting that I can't use a button to delete all
buttons - I can live with that. You're suggesting that the "deletebuttons"
macro will remain accessible to all, and I can live with that. However,
since I only delete buttons once, I don't understand why you have me calling
it from a private subroutine - what does that accomplish?

Thanks, Tom.
Dean

"Tom Ogilvy" wrote in message
...
If you are running from a button that you delete, then it could be a
problem.

Perhaps

Private Sub CommandButton1_Click()
Application.Ontime now, "DeleteButtons"
End Sub

in a general Module

Sub Deletebuttons()
Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next
End Sub


--
Regards,
Tom Ogilvy

"Dean" wrote in message
...
Wow, that was amazing! Only one problem - I've tried it thrice,
including closing out EXCEL and re-opening, and, each time, after I start
navigating just after the macro, EXCEL crashes. It does recover the file
but who would want that to be the normal course of business.

Would it matter that I am initiating the macro via one of the buttons
that the macro then erases? Also, all my subs, including this one, are
private.

D


"Tom Ogilvy" wrote in message
...
If all you have is buttons, you might try:

Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next


--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I can't see my client being willing to deal with all this for unhiding a
macro - they have different users and different locations, plus they're
not tech savvy. In the very few occasions that they need to be able to
hide buttons because they are sending the file out to one of their
clients, I think they would be happier to send their client a lesser
file, one with the macro buttons manually deleted, or with them hidden.
Can you tell me how to hide buttons on all worksheets, say worksheets
named, Joe, Jane, and Jill, or a macro to delete all buttons? (At
worst, they can delete them manually, I suppose). Then they could just
click this macro button, which could stay there, and resave the file
under a different name to send to their client, maintaining the
non-clicked version for themselves, for future iterations. By the way,
rookie that I am, I create the buttons using the drawing toolbar -
actually, now, I just copy a button from one I did before, and edit. Is
that a bad way to do them? I guess the forms button is quicker. Anyway,
for now, please use a macro that deals with drawing boxes, if it's not
too difficult.

Anyway, if they run such a macro, and if I've already put all the
macros in the private area, that should do it, right? I assume that if
they are private that, if the button is deleted or hidden, there is no
way to access the macro? Is that right? Or is there still a
reasonably easy, non-expert, way?

Dean

"Jim Thomlinson" wrote in
message ...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons.
Place a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called
form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in
the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you
want the
buttons to be shown. The file is just a flag. Put the file somewhere
in a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can create
a
batch file to create the directory and the empty text file if you want
to get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone
else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they don't
corrupt the equations. And we're not looking for total protection,
just
enough to deter the non-expert. Is it one of those things by holding
down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro
buttons would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If
this is
very
confidential then you are going to need to rethink the solution as
no
matter
what I give you it can be defeated. Code and Excel are both
inherently
easy
to defeat for anyone with a strong enough desire... In that case
you will
need to exprt out the data from this spreadsheet into a new
workbook that
contains only what you want the end user to see. Otherwise if it is
just a
matter of being protected for convenience then there are a couple
of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to
delete
them,
right?


"Jim Thomlinson" wrote
in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the
module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook
that
does
not
contain your code? That way they never even have access to it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be
able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean

I suggested how you might have a button delete itself.

Beyond that it is all your own creation.

--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
It seems like you're suggesting that I can't use a button to delete all
buttons - I can live with that. You're suggesting that the "deletebuttons"
macro will remain accessible to all, and I can live with that. However,
since I only delete buttons once, I don't understand why you have me
calling it from a private subroutine - what does that accomplish?

Thanks, Tom.
Dean

"Tom Ogilvy" wrote in message
...
If you are running from a button that you delete, then it could be a
problem.

Perhaps

Private Sub CommandButton1_Click()
Application.Ontime now, "DeleteButtons"
End Sub

in a general Module

Sub Deletebuttons()
Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next
End Sub


--
Regards,
Tom Ogilvy

"Dean" wrote in message
...
Wow, that was amazing! Only one problem - I've tried it thrice,
including closing out EXCEL and re-opening, and, each time, after I
start navigating just after the macro, EXCEL crashes. It does recover
the file but who would want that to be the normal course of business.

Would it matter that I am initiating the macro via one of the buttons
that the macro then erases? Also, all my subs, including this one, are
private.

D


"Tom Ogilvy" wrote in message
...
If all you have is buttons, you might try:

Dim sh as Worksheet, shp as Shape
for each sh in worksheets
for each shp in sh.Shapes
shp.Delete
Next
Next


--
Regards,
Tom Ogilvy


"Dean" wrote in message
...
I can't see my client being willing to deal with all this for unhiding
a macro - they have different users and different locations, plus
they're not tech savvy. In the very few occasions that they need to be
able to hide buttons because they are sending the file out to one of
their clients, I think they would be happier to send their client a
lesser file, one with the macro buttons manually deleted, or with them
hidden. Can you tell me how to hide buttons on all worksheets, say
worksheets named, Joe, Jane, and Jill, or a macro to delete all
buttons? (At worst, they can delete them manually, I suppose). Then
they could just click this macro button, which could stay there, and
resave the file under a different name to send to their client,
maintaining the non-clicked version for themselves, for future
iterations. By the way, rookie that I am, I create the buttons using
the drawing toolbar - actually, now, I just copy a button from one I
did before, and edit. Is that a bad way to do them? I guess the forms
button is quicker. Anyway, for now, please use a macro that deals with
drawing boxes, if it's not too difficult.

Anyway, if they run such a macro, and if I've already put all the
macros in the private area, that should do it, right? I assume that
if they are private that, if the button is deleted or hidden, there is
no way to access the macro? Is that right? Or is there still a
reasonably easy, non-expert, way?

Dean

"Jim Thomlinson" wrote in
message ...
Since it does not sound mission critical I would write two snipets of
code.
One to hide all of the buttons and the other to unhide the buttons.
Place a
reference to the hide procedure in the before close event. This will
ensure
that the buttons are always hidden when the file is closed.

To unhide the buttons we can do one of a couple of things each called
form
the on open event in Thisworkbook. Check Environ("UserName") (windows
login
name) against a pre-populated list that is in a very hidden sheet in
the
workbook. If the name is found then call the unhide routine. Another
possibility is to place a blank Text file on any machines where you
want the
buttons to be shown. The file is just a flag. Put the file somewhere
in a
directory that you create. You can then write some very easy code to
verify
if the file exists. If it does then unhide the buttons. You can
create a
batch file to create the directory and the empty text file if you
want to get
fancy.

To unhide the buttons it depends what kind of buttons you have...
From the control toolbox...
Sheets("Sheet1").CommandButton1.Visible = false
From the forms toolbar...
Sheets("Sheet1").Buttons("Button 1").Visible = False

I am affraid I gotta go home now so if you get stuck perhaps someone
else
can help you or post back tomorrow...
--
HTH...

Jim Thomlinson


"Dean" wrote:

Its not data that needs to be protected, it's mostly, that they
don't
corrupt the equations. And we're not looking for total protection,
just
enough to deter the non-expert. Is it one of those things by
holding down
the shift key, they could defeat?

So, any hints for a password upon opening that will show macro
buttons would
be appreciated. Or, failing that, I could probably just delete the
buttons - there's only about 7 of them.

Thanks, much, Jim!
Dean

"Jim Thomlinson" wrote in
message
...
Depends on how critical the data that you are sending out is. If
this is
very
confidential then you are going to need to rethink the solution as
no
matter
what I give you it can be defeated. Code and Excel are both
inherently
easy
to defeat for anyone with a strong enough desire... In that case
you will
need to exprt out the data from this spreadsheet into a new
workbook that
contains only what you want the end user to see. Otherwise if it
is just a
matter of being protected for convenience then there are a couple
of
routes
taht we could follow including a password on open that unhides the
buttons...
--
HTH...

Jim Thomlinson


"Dean" wrote:

I also have clickable macro buttons. I suppose I would need to
delete
them,
right?


"Jim Thomlinson" wrote
in
message
...
At the top of the module where the code resides add

Option Private Module

This will keep all of the sub procedures and functions in the
module
private
so that they do not show up in the macro list...
--
HTH...

Jim Thomlinson


"MSweetG222" wrote:

Is it possible to save the worksheets to a different workbook
that
does
not
contain your code? That way they never even have access to
it.
--
Thx
MSweetG222



"Dean" wrote:

I password protected my macros so no one can edit them but,
occasionally, I
want to send the file somewhere and I don't want them to be
able to
even run
the macros, particularly one that takes off all the password
protection!

What is the easiest way to ensure they won't be able to run
macros,
short of
deleting all of them?

Thanks!
Dean