Locking the macro view function

[Mattlynn via OfficeKB.com]

Hi all,

I have a survey spreadsheet which has some macros in it.
When i send the spreadsheet out, i want to prevent others from seeing the
macro code.
Is there a way i can lock this down as protecting the workbook etc does not
offer this as an option and the macro can still be viewed.
I tried removing the whole toolbar, but anyone with excel knowledge can just
put that back.
Can anyone help????

Many Thanks
Matt

 

[Luke M]

Open the VBE (Alt+F11). In the Project Explorer window (usually top left. If
it's not visible, go to View, and make it visible) right click any of the
parts of your workbook's coding, and select properties. You should now be
able to go to the protection tab, and setup a password.

The other nice thing about this is while sheet/workbook protection can be
cracked in 2-3 minutes by a macro, VB protection is stronger and requires a
larger amount of effor to defeat.

 

[Mattlynn via OfficeKB.com]

Hi Luke
So, i did that. I right clikced on Module 1 where the nacro is, and set up a
p/w in the protection tab and also selected "lock project for viewing". When
i return to the spreadsheet i can still access the macro through
"tools/macro/macros".

Did i do something wrong?

thanks luke
Regards
Matt

Open the VBE (Alt+F11). In the Project Explorer window (usually top left. If
it's not visible, go to View, and make it visible) right click any of the
parts of your workbook's coding, and select properties. You should now be
able to go to the protection tab, and setup a password.

The other nice thing about this is while sheet/workbook protection can be
cracked in 2-3 minutes by a macro, VB protection is stronger and requires a
larger amount of effor to defeat.

Hi all,

[quoted text clipped - 9 lines]

Many Thanks
Matt

 

[Luke M]

To clarify:
Did you want to prevent people from seeing the code, or prevent them from
calling the macros?

The explaination I first gave was to hide/protect the code. If you don't
want users to be able to call the macros directly, place the word "Private"
in front of you subs, like so:

Private Sub MySub ()
'do something
End Sub

However, it might (depdning on your setup) be possible for a user to start
recording a macro in another workbook, and then activate the macros
(somehow?) in your book. That would tell them the name of the macro called,
but again, if you did what I said earlier, they can't edit/see the actual
coding.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Hi Luke
So, i did that. I right clikced on Module 1 where the nacro is, and set up a
p/w in the protection tab and also selected "lock project for viewing". When
i return to the spreadsheet i can still access the macro through
"tools/macro/macros".

Did i do something wrong?

thanks luke
Regards
Matt

Open the VBE (Alt+F11). In the Project Explorer window (usually top left. If
it's not visible, go to View, and make it visible) right click any of the
parts of your workbook's coding, and select properties. You should now be
able to go to the protection tab, and setup a password.

The other nice thing about this is while sheet/workbook protection can be
cracked in 2-3 minutes by a macro, VB protection is stronger and requires a
larger amount of effor to defeat.

Hi all,

[quoted text clipped - 9 lines]

Many Thanks
Matt

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200909/1

 

[Mattlynn via OfficeKB.com]

All sorted - add the p/w to the VBE as you suggested, then lock the
spreadsheet in the noormal way, the save and close and reopen - hey presto.

Thanks luke - you're a star
Regards
Matt

Hi Luke
So, i did that. I right clikced on Module 1 where the nacro is, and set up a
p/w in the protection tab and also selected "lock project for viewing". When
i return to the spreadsheet i can still access the macro through
"tools/macro/macros".

Did i do something wrong?

thanks luke
Regards
Matt

Open the VBE (Alt+F11). In the Project Explorer window (usually top left. If
it's not visible, go to View, and make it visible) right click any of the

[quoted text clipped - 9 lines]

 

[Mattlynn via OfficeKB.com]

Hi Luke - your original suggestion was perfect. I didnt realise for it to
kick in i had to save and close the workbook and the reopen. When i did this,
it worked. The idea was to prevent them from seeing it, and your suggestion
did just that.

Thanks very much Luke
Regards
Matt

To clarify:
Did you want to prevent people from seeing the code, or prevent them from
calling the macros?

The explaination I first gave was to hide/protect the code. If you don't
want users to be able to call the macros directly, place the word "Private"
in front of you subs, like so:

Private Sub MySub ()
'do something
End Sub

However, it might (depdning on your setup) be possible for a user to start
recording a macro in another workbook, and then activate the macros
(somehow?) in your book. That would tell them the name of the macro called,
but again, if you did what I said earlier, they can't edit/see the actual
coding.

Hi Luke
So, i did that. I right clikced on Module 1 where the nacro is, and set up a

[quoted text clipped - 21 lines]