VBA to Allow Updating of Cells on Protected Sheets

[PShingadia]

Hi All:

I have three reporting sheets with a drop-down on each from which a user can make a selection. This selection then updates calculations on that sheet. However, when these sheets are protected the cells will not update and Excel shows message 'You are trying to update values on a protected sheet....'.

I have the following VBA code in the Workbook part but will not work.

Private Sub WorkbookPrav_Open()
With Worksheets("Total Summary by Lot")
.Protect contents:=True, userinterfaceonly:=True
.EnableOutlining = True
End With

With Worksheets("Summary Pricing Report")
.Protect contents:=True, userinterfaceonly:=True
.EnableOutlining = True
End With

With Worksheets("Summary by Vessel")
.Protect contents:=True, userinterfaceonly:=True
.EnableOutlining = True
End With

End Sub


Please help as have been trying for a whole day to sort this out.

Thanks

 

[Scott T]

You can unprotect the sheet then protect the sheet again.

If you do not have a password then leave that off

Sheets("Total Summary by Lot").Unprotect "password"

Sheets("Total Summary by Lot").Protect "password"

 

[PShingadia]

Thanks Scott - that helped!!!