Hiding "modules"

[troysteadman]

Hi All

Yes I know you don't have modules any more...

For Each wbk in Application.Workbooks
For each sht in wbk.Sheets
Select Case sht.Name
Case "MySheet"
sht.Visible=False
End Select
Next
Next

....cycles through every sheet in every Workbook that is open, and
hides any that are called "MySheet". I want toi do the same with VB
Modules but can't seem to find the object - formerly known as a
"Module".

Help!

 

[Bob Phillips]

Where do you get the idea that you don't have modules anymore. I use Excel
2003 and 2007 and I have hundreds of modules.

And what exactly do you mean by hide them? You can lock a VBA project
(Tools>Properties>Protection), but I have never come across a way to hide
individual modules.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[troysteadman]

Where do you get the idea that you don't have modules anymore. I use Excel
2003 and 2007 and I have hundreds of modules.

And what exactly do you mean by hide them? You can lock a VBA project
(Tools>Properties>Protection), but I have never come across a way to hide
individual modules.

Thanks for the reply Bob. Is there any way of doing
Tools>Properties>Protection by macro? I have a workbook which I need
to protect and unprotect with monotonous regularity. I could really do
with some automation.

 

[Bob Phillips]

You can, but it is not a robust method as it involves the use of SendKeys
which isn't too stable.

This is an example, but you may need to tune it

With Application
.SendKeys "%{F11}", True ' VBE
.SendKeys "^r", True ' Set focus to Explorer
.SendKeys "{TAB}", True ' Tab to locked project
.SendKeys "~", True ' Enter
.SendKeys "password"
.SendKeys "~", True ' Enter
End With



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[troysteadman]

You can, but it is not a robust method as it  involves the use of SendKeys
which isn't too stable.

This is an example, but you may need to tune it

    With Application
        .SendKeys "%{F11}", True    ' VBE
        .SendKeys "^r", True        ' Set focus to Explorer
        .SendKeys "{TAB}", True     ' Tab to locked project
        .SendKeys "~", True         ' Enter
        .SendKeys "password"
        .SendKeys "~", True         ' Enter
    End With

Fantastic. I haven't used SendKeys since Lotus 123 DOS where it was
extremely stable. In Windows it always seemed the keys arrived before
(or after) whatever menu was waiting for them. I'll write my will and
have a go at it after a couple of stiff brandies.

Thanks so much for your help.