Hide module from users

Larry Jones

unread,

Jul 27, 1997, 3:00:00 AM7/27/97

to

I am trying to hide my macros from the users, so they cannot be opened
and changed. When I try to set the VISIBLE attribute to xlVeryHidden, I
get an Excel error telling me I cannot se the module property.

Below is the code I am using, to get around the problem. I just set the
macros VISIBLE property to False, the rest are set to xlVeryHidden.

For Each s In Sheets
If s.Name <> "Banner" Then
If s.Name = "Macros" Then
s.Visible = False
Else
s.Visible = xlVeryHidden
End If
End If
Next

I do have some code that allows these sheets to be visible, if the
username is mine, so that is not a problem.

Why can't I do this, even though the help says it can be set?

I'd appreciate any help I could get.

Thanks

GJ

unread,

Jul 27, 1997, 3:00:00 AM7/27/97

to

RE: xlVeryHidden modules

A macro cannot VeryHide the module in which it resides. This is a feature
of Excel, apparently designed for extra protection. If a macro calls a
subroutine in another module, it will also be unable to hide that module.

The only way to very hide all the modules in a workbook is to write a
temporary module with the code:

Sub HideModules()
For each m in Modules
If m.Name <> Activesheet.Name Then m.Visible = xlVeryHidden
Next
End Sub

Make sure this module is the active sheet, then run the macro and delete
it. Remember to protect your workbook, otherwise users can write their own
macro to unhide all of yours.

You could include this module in another workbook, to save you from having
to write it repeatedly. In this case, the macro could look like

sub HideModules()
For each m in Workbooks("workbookname").Modules
M.visible=xlVeryHidden
Next
end sub

Hope this helps.

Greg

Eric Chinn

unread,

Jul 27, 1997, 3:00:00 AM7/27/97

to

Have you tried making your macro sheet an addin. (Addins can't be viewed or
edited.)

Larry Jones <llj...@flash.net> wrote in article
<33DBA1...@flash.net>...

Jeff Motter

unread,

Jul 27, 1997, 3:00:00 AM7/27/97

to

This will hide all modules from users. Put this in your personal
macro book on your local machine only. That way a user can't change
the visible property to "true". Hope this helps. BTW, you can use this
code to un-hide other peoples hidden modules as well.

Sub hide_modules()
Dim vmdl
For Each vmdl In Modules
vmdl.Visible = False
Next vmdl
End Sub

Larry Jones <llj...@flash.net> wrote in article
<33DBA1...@flash.net>...
> I am trying to hide my macros from the users, so they cannot be opened
> and changed. When I try to set the VISIBLE attribute to xlVeryHidden, I
> get an Excel error telling me I cannot se the module property.
>
>

Julian

unread,

Jul 28, 1997, 3:00:00 AM7/28/97

to

I'm very new to this. I need to subtract A1 from A2 and display result
in B1 and then A3 from A4 and display result in B3, etc.... This needs
to be repeated in a column which can exceed 5000 entries. The columns
can also vary. I'm using Excel 5.0. I'm looking for a simple macro which
could be used to perform this function. I'd appreciate any help.

Thanks

Jules

Erik Dillenkofer

unread,

Jul 28, 1997, 3:00:00 AM7/28/97

to

> > I am trying to hide my macros from the users, so they cannot be opened
> > and changed. When I try to set the VISIBLE attribute to xlVeryHidden, I
> > get an Excel error telling me I cannot se the module property.
> >
> >
> > Below is the code I am using, to get around the problem. I just set the
> > macros VISIBLE property to False, the rest are set to xlVeryHidden.
> >
> > For Each s In Sheets
> > If s.Name <> "Banner" Then
> > If s.Name = "Macros" Then
> > s.Visible = False
> > Else
> > s.Visible = xlVeryHidden
> > End If
> > End If
> > Next
> >
> > I do have some code that allows these sheets to be visible, if the
> > username is mine, so that is not a problem.
> >
> > Why can't I do this, even though the help says it can be set?
> >
> > I'd appreciate any help I could get.
> >
> > Thanks
> >

Or you can insert a "temporary" macro module, view the debug window, and
in the upper pane enter Sheets("module name you want to hide goes
here").visible = xlVeryHidden and then hit the enter key. Then you can
delete the "temporary" module sheet.

Russ Wiggin

unread,

Jul 29, 1997, 3:00:00 AM7/29/97

to

Dear Jules,

Short of a macro, how about entering the following formula in B1 and then
filling down (dblclk on the fill handle)?

=IF(MOD(ROW(),2),(A2-A1),"")

Not sure what you mean by "the columns can also vary".

Regards,
--
Russ Wiggin, CPA
Newton, MA

Julian <jul...@saix.net> wrote in article <33DC72...@saix.net>...