Calculation Protection, but able to use drop downs, grouping, and fill in additi

Question

Calculation Protection, but able to use drop downs, grouping, and fill in additional information (in specific cells)

I have created a workbook for each project.

I have protected all worksheets without an issue except for one in the workbook.

There are some cells that require manual data entry (no need for protection).

Within this worksheet are some drop downs that you must make a selection from (no need for protection). This selection will populate certain cells with information imported from some of the other worksheets in the workbook (I want to protect these cells).

There are cells that have calculations that use the manually inserted data and the imported data to produce a number (I want to protect these cells).

I have rows that are grouped together. I only expand the needed groups for the project. I need to have the ability to expand and collapse groups at will (no need for protection).

I have locked cells that I want protection for.

I have unlocked cells that I do not want protected.

If I use normal Protection, then I cannot use the grouping function at all.

I have also used Visual Basic to allow for protection and to allow grouping to function.

The problem I have is that when I use the password to allow grouping to be functional anyone can then go to "Review" and select "Unprotect" and everything in the worksheet can be changed or modified.

I need password protection for the areas I want protected, but still have full functionality of grouping, drop downs, etc.

ANyone?

Explanation / Answer

You can get the grouping to be functional on a protected sheet with a VBA routine for Auto Open

You unprotect the sheet and then reprotect with an additional option. I use this method on some of my worksheets.

As far as I know the enable outlining can only be activated in VBA and not through normal menus or options in Excel .

If you protect your VBA project, then the regular users will not be able to access the sheet password.

This goes in a module and not on an individual sheet.

Sub Auto_Open()

Sheets("Sheet1").Unprotect Password:="password"
With Sheets("Sheet1")

(This part is just the normal protection change to match your contitions)

.Protect Password:="password", DrawingObjects:=False, Contents:=True, Scenarios:= _False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, userinterfaceonly:=True

(This additional line enables the outlining (groups)

.EnableOutlining = True

End With

End Sub

HTH :D

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.