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
s.Visible = xlVeryHidden
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.
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:
For each m in Modules
If m.Name <> Activesheet.Name Then m.Visible = xlVeryHidden
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
For each m in Workbooks("workbookname").Modules
Hope this helps.
For Each vmdl In Modules
vmdl.Visible = False
Larry Jones <llj...@flash.net> wrote in article
> 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.
Short of a macro, how about entering the following formula in B1 and then
filling down (dblclk on the fill handle)?
Not sure what you mean by "the columns can also vary".
Russ Wiggin, CPA