How to protect excel workbook from macro starting from Developer - Code ribbon?

[Ivan]

Hello,

in my excel workbook project I use a lot of VBA code where I open different
external data sources, hide and unhide some worksheets and do things that
shoul not be explicitly viewed by normal users. So I protected Visual Basic
code with a password what is probably not the best security (?) but it is
probably enough for our not so experienced internal users.

What disturbs me is but that the user can clicks on Macros icon from the
Developer - Code ribbon and then he can see the list of all my programs
defined in my Visual basics Modules. And not only see - the user can even
run any od these programs alone and on such way ,because of uncontrolled
treatment, he can cause unexpected consequences.

Is there any way how to disable user from such uncontrolled actions?

Ivan

 

[The Dude]

Hello Ivan,

You can start by putting "private" in front of your subs. It hides them from
the list of macros (you can still launch them if you type the name), but can
be problematic if you try to call a procedure from another module.

 

[Ivan]

I have suspected the solution of "private" functions and subs but the
problem is exactly in the (un)attainability of such code from the code of
worksheets, forms and other modules. As I understand the only way to reach
my goal now is to displace all code from modules to worksheets and forms. In
this way I lose by all means the universality od modul code!?

Ivan

 

[ginozzzz]

I realise this post is a little old now, but I had trouble with this also. Here are my discoveries:

1. you can add an optional variable to each of the named macros. for example: SUB NAME (Optional dummy as string). Only Macros without variables will show in the macro box.

2. create a small function like this:

Function BookProtected() As Boolean
'Function purpose: To evaluate if a workbook is protected
If ThisWorkbook.ProtectStructure = True Then
BookProtected = True
Else
BookProtected = False
End If
End Function

Then test if the book is protected from your macro.....

If BookProtected = True Then Exit Sub

Hope that helps someone!

 

[L. Miller]

Or simply add an option argument:
Public Sub addSummaryFxs(Optional arg = 3)