Use Group and Outline in Excel with Protection On 2

[ricolame]

Hey guys,

I need help to use group & outline when my protection is turned on. Is there a way to do that?

 

[xlhelp]

You can do it with a macro. Two ways

a. auto-open. macro applies when workbook is opepned. Where you have to define the worksheet you are protecting and the password.

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="xlhelp", userinterfaceonly:=True
.EnableOutlining = True
End With
End Sub

b. running a macro when you are finished working with a particular worksheet

Sub ProtectSheet()

ActiveSheet.EnableOutlining = True
ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

End Sub








Member AAA - Abolish Abused Abbreviations

 

[ricolame]

hi,

thanks it works! But, in order to activate it, i've to run the macro personally. Is there a way to have it auto run the moment the worksheet is opened?

Thanks

 

[xlhelp]

Option a. does that.

Member AAA - Abolish Abused Abbreviations

 

[ricolame]

Hi,

Tks. I took option A code. Placed it in the window.

However it would not work unless I activate the macro personally, and not activated automatically when the excel is opened.

Is it something that i had done wrong ? Tks again!

With regards,

 

[ricolame]

hi xlhelp,

hope you are still on the forum... i couldnt get it working as intended .. the macro just doesnt seem to work when it is opened automatically..

Is there anything that I might had done wrongly?

 

[KenWright]

Where did you put that auto open code? Put it in the 'ThisWorkbook Module', not in the sheet module.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Rofeu]

Hi,

Ken, if the macro is called Autpen, it should be in a regular codemodule. In the Thisworkbook module is will be the Workbook_Open event.

Ricolame, it might have to do with your settings.

1. On opening the workbook, macro's should be enabled.
2. Security should be set to Medium or Low (Tools -> Macro -> Security)
3. VBProjects should be Trusted Sources (same path, tab 'Trusted Sources'. The bottom checkbox should be tagged.)

Hope this helps.

Cheers,

Roel

 

[KenWright]

LOL - my bad, skimming through, saw the _open bit and assumed it was Workbook_Open. Cheers for the catch.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firefytr]

They are actually the same thing, the auto_open is an earlier version and is still supported, although I wouldn't bank on it in the future (as the way MS changes their tune quite a bit). I would recommend using the workbook_open event instead.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[KenWright]

Well not quite the same thing Workbook_Open is an event macro whereas Auto_open isn't. And I seem to remember there being differences in that Auto_open could be prevented from triggering by holding down SHIFT whilst Workbook_open couldn't, or at least in the earlier versions of Excel. Think there were a couple of other nuances as well but for the life of me can't remember what they were.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Rofeu]

Another difference is that if the file is opened through VBA the Workbook_Open event will fire automatically and the Autpen macro needs to be called through the RunAutoMacros command.

Cheers,

Roel

 

[firefytr]

Maybe I should have put "for all intensive purposes.." ;-)

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a Microsoft MVP?
- Leonardo da Vinci

 

[KenWright]

LOL - The firing when opened by VBA nuance was the other one I couldn't remember

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Aubs010]

Is there any way of getting this working without the use of VBA?

I have a workbook that I cannot use VBA as it will be opened by various people with limited abilities or with security set to high which disables the VBA code automatically.

When selecting show detail, the message is:
"You cannot use this command on a protected sheet. To unprotect the sheet...."


Thanks,

Aubs

 

[firefytr]

Set the security lower or give them a password.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci