Protectin/Unprotecting Sheets

[Hugo]

Hi,

I've been using the following code to protect my sheets:

Sub protectsheet()
With ActiveSheet

.Protect Password:="topsecret", UserInterfaceOnly:=True

.EnableOutlining = True

End With

End Sub

I'd like the sheets, once they have been protected, to have the outlining
feature enabled, which holds true when I protect/unprotect the open file, but
once I close out of the file and re-open it, it tells me that I cannot use
the outlining features when it is protected. Any thoughts?

Thanks!

 

[OssieMac]

Hi Hugo,

Untested but try enabling outlining before the protection.

.EnableOutlining = True
.Protect Password:="topsecret", UserInterfaceOnly:=True

If that does not work try a workbook open event and unprotect and re-protect.

 

[Hugo]

Unfortunately, your suggestion yielded the same results as my previous
experience. What exactly do you mean by a workbook open event?

Any other thoughts?

Thanks!

 

[OssieMac]

Hello again Hugo,

Workbook Open Event.

When you open the VBA Editor you will see a list of the VBA modules on the
left hand side. This is called the Project Explorer. These include the sheet
modules and any modules or forms you have inserted.

Here you will see one called ThisWorkbook. Double click it and it will open
with a blank editing screen.

At the top of the editing screen there are 2 dropdown arrows.
One will have the default (General) displayed and the other the default
(Declarations).
Click the dropdown against the left one and select Workbook. It should have
created a sub heading and end sub like the following:

Private Sub Workbook_Open()

End Sub

If it did not create the required default sub/end sub like above then click
the right side dropdown and select Open and it will now be the correct
sub/end sub like the above. (If any other sub/end sub was created then it can
be deleted.)

Insert your code in there and it will run each time the workbook is opened.
(You cannot rename these subs or they will not work)

Since you were unaware of these events then perhaps you are interested in a
little lesson in events. You can try the following site. (I am sure there are
others if you search the internet for Excel events)

http://www.mvps.org/dmcritchie/excel/event.htm

 

[OssieMac]

I should have included the following:

If you can't see the Project Explorer when you open the VBA editor then Ctrl
+ r will turn it on (or menu item View -> Project Explorer)